How to split the time range into multiple rows

Question

I want to split the date/time ranges into multiple rows by hour in SQL Server but have some issues. My current dataset looks like this:

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

And I want something like this as my result table. Note that I want to treat a block less than an hour as one independent row as well. (For example 8:30AM - 9:00AM as one row.)

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

My current code only splits the date/time range that is within the same day. For example, the time range for Employee 587123 stops the spliting at 22:00 - 23:00 and doesn't work for the time range in next day. How do I update my code to capture data after midnight? (The last three rows in the sample result table.)

Here's my current code

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;

The current code looks too complicated so if you know better way to do this, please let me know. I'd appreciate any help on this.

Answer 1

Here is a recursive CTE solution:

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

Basically, the anchor of the CTE performs computes the end of the first range, using datetimefrompart(). Then we iteratively generate the following ranges, until the maximum date time is reached. We can then display the results with the outer query, while adjusting the end date of the last range.

Answer 2

I would approach this using a recursive 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;

Here is a db<>fiddle.

If you might have spans of more than 100 hours, then you need option (maxrecursion 0) for the query.