如何将时间范围拆分为多行

Question

我想在SQL Server中将日期/时间范围按小时划分为多行，但存在一些问题。我当前的数据集如下：

EmployeeCode         StartDateTime           EndDateTime
843578             2017-05-14 8:30 AM     2017-05-14 11:36 PM
587123             2017-05-14 22:00 PM    2017-05-15 01:28  AM

我想要这样的东西作为我的结果表。请注意，我也希望将不到一个小时的块视为一个独立的行。(例如，上午8:30-上午9:00作为一行。(

EmployeeCode         StartDateTime           EndDateTime
843578             2017-05-14 8:30 AM     2017-05-14 9:00 PM
843578             2017-05-14 9:00 AM     2017-05-14 10:00 AM 
843578             2017-05-14 10:00 AM    2017-05-14 11:00 AM 
843578             2017-05-14 11:00 AM    2017-05-14 11:36 AM 
587123             2017-05-14 22:00 PM     2017-05-14 23:00 PM
587123             2017-05-14 23:00 PM     2017-05-15 00:00 AM
587123             2017-05-15 00:00 AM     2017-05-15 01:00 AM
587123             2017-05-15 01:00 AM     2017-05-15 01:28 AM

我当前的代码只分割同一天内的日期/时间范围。例如，Employee 587123的时间范围在22:00-23:00停止拆分，并且不适用于第二天的时间范围。如何在午夜后更新代码以捕获数据？(示例结果表中的最后三行。(

这是我当前的代码

SELECT YT.EmployeeCode,
CASE WHEN YT.StartDateTime > DT.StartDateTime THEN YT.StartDateTime ELSE DT.StartDateTime END AS StartDateTime,
CASE WHEN YT.EndDateTime < DT.EndDateTime THEN YT.EndDateTime ELSE DT.EndDateTime END AS StartDateTime
FROM (VALUES(843578,CONVERT(datetime2(0),'2017-05-14T08:30:00'),CONVERT(datetime2(0),'2017-05-14T15:36:00')),
(587123,CONVERT(datetime2(0),'2017-05-14T09:00:00'),CONVERT(datetime2(0),'2017-05-14T18:28:00')))YT(EmployeeCode,StartDateTime,EndDateTime)
CROSS APPLY (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23))T(I)
CROSS APPLY (VALUES(DATEADD(HOUR,T.I,CONVERT(time(0),'00:00:00')),DATEADD(HOUR,T.I+1,CONVERT(time(0),'00:00:00'))))V(StartTime,EndTime)
CROSS APPLY (VALUES(DATETIMEFROMPARTS(YEAR(YT.StartDateTime),MONTH(YT.StartDateTime),DAY(YT.StartDateTime),DATEPART(HOUR,V.StartTime),DATEPART(MINUTE,V.StartTime),0,0),
DATETIMEFROMPARTS(YEAR(YT.StartDateTime),MONTH(YT.StartDateTime),DAY(YT.StartDateTime),DATEPART(HOUR,V.EndTime),DATEPART(MINUTE,V.EndTime),0,0)))DT(StartDateTime,EndDateTime)
WHERE YT.StartDateTime <= DT.EndDateTime
AND YT.EndDateTime >= DT.StartDateTime;

当前的代码看起来太复杂了，所以如果你知道更好的方法，请告诉我。如果你能帮我，我将不胜感激。

Answer 1

这里有一个递归CTE解决方案：

with cte as (
select 
employeecode, 
startdatetime, 
dateadd(hour, 1, datetimefromparts(year(startdatetime), month(startdatetime), day(startdatetime), datepart(hour, startdatetime), 0, 0, 0)) enddatetime
enddatetime maxdatetime
from mytable
union all
select employeecode, enddatetime, dateadd(hour, 1, enddatetime), maxdatetime
from cte
where enddatetime < maxdatetime
)
select employeecode, startdatetime, 
case when enddatetime < maxdatetime then enddatetime else maxdatetime end as enddatetime
from cte

基本上，CTE的锚点使用datetimefrompart()来计算第一范围的末尾。然后，我们迭代生成以下范围，直到达到最大日期时间。然后，我们可以使用外部查询显示结果，同时调整最后一个范围的结束日期。

Answer 2

我会使用递归CTE:来处理这个问题

with cte as (
select t.EmployeeCode, t.StartDateTime as startdt, 
dateadd(hour, datepart(hour, t.startdatetime) + 1, convert(datetime, convert(date, t.StartDateTime))) as enddt,
t.endDateTime, 1 as lev
from t
union all
select cte.employeecode, enddt,
(case when dateadd(hour, 1, enddt) < enddatetime then dateadd(hour, 1, enddt) else enddatetime end),
enddatetime, lev + 1
from cte
where enddt < enddatetime
)
select *
from cte
order by employeecode, startdt;

这里有一个db<gt；不停摆弄

如果跨度可能超过100小时，那么查询需要option (maxrecursion 0)。