Splitting up events that occur over the day boundary

Question

I have a table of events with a start time and an end time, with some events that have a start time before midnight and an end time after midnight. I'd like to produce output that splits up these events at the midnight barrier so they can be counted toward their respective date.

| EVENT_ID | START_TIME              | END_TIME                |
|----------|-------------------------|-------------------------|
| 1001     | 2021-02-21 14:00:00.000 | 2021-02-21 18:00:00.000 |
| 1002     | 2021-02-21 17:00:00.000 | 2021-02-22 03:00:00.000 |
| 1003     | 2021-02-21 18:00:00.000 | 2021-02-21 22:00:00.000 |
| 1004     | 2021-02-21 22:00:00.000 | 2021-02-22 07:00:00.000 |

The above table could be produced by the query:

SELECT EVENT_ID,
       START_TIME,
       END_TIME
FROM EVENTS
WHERE START_TIME BETWEEN '2021-02-21 00:00:00.000' AND '2021-02-21 23:59:59.999'
;

My desired output will split up the events that span multiple days at midnight:

| EVENT_ID | START_TIME              | END_TIME                |
|----------|-------------------------|-------------------------|
| 1001     | 2021-02-21 14:00:00.000 | 2021-02-21 18:00:00.000 |
| 1002     | 2021-02-21 17:00:00.000 | 2021-02-21 23:59:59.999 |
| 1002     | 2021-02-22 00:00:00.000 | 2021-02-22 03:00:00.000 |
| 1003     | 2021-02-21 18:00:00.000 | 2021-02-21 22:00:00.000 |
| 1004     | 2021-02-21 22:00:00.000 | 2021-02-21 23:59:59.999 |
| 1004     | 2021-02-22 00:00:00.000 | 2021-02-22 07:00:00.000 |

Any help would be greatly appreciated. Ideally I'd like to produce this without functions or the creation of new tables.

Note that I'm using SQL Server 2016

One method would be to `JOIN` to a calendar table; do you have one? — Thom A, Mar 03 '21 at 11:48
What's the longest period (in days) that there could be between the start and end time? Are we looking at 2 or 3 days, or 100's? — Thom A, Mar 03 '21 at 11:52
@hdf8 . . . I would change the `end_time` to be exactly midnight so you don't miss a second or millisecond or whatever. — Gordon Linoff, Mar 03 '21 at 13:37

score 3 · Answer 1 · answered Mar 03 '21 at 11:50

3

You can use a recursive CTE for this:

with cte as (
      select event_id, start_time,
             (case when datediff(day, start_time, end_time) = 0 then end_time
                   else dateadd(day, 1, convert(date, start_time))
              end) as end_time,
             end_time as real_end_time
      from t
      union all
      select event_id, end_time,
             (case when dateadd(day, 1, convert(date, end_time)) > real_end_time
                   then real_end_time
                   else dateadd(day, 1, convert(date, end_time))
              end),
             real_end_time
       from cte
       where end_time < real_end_time
     )
select *
from cte;

Here is a db<>fiddle.

answered Mar 03 '21 at 11:50

Gordon Linoff

1,242,037
58
646
786

Not anonymous. rCTE is very inefficient, I've told you that before. Use a tally function/table instead – Charlieface Mar 03 '21 at 13:40
Let's just say that I've seen you answer with this type of code in much larger instances, and even inside `APPLY`s on massive tables (do you have it as one of your standard snippets?) It's bad code and it falls down on any significant number of rows, don't promote it as a standard solution. And I started writing an answer using a tally, but then I saw @Serg 's answer, so I left a comment instead. – Charlieface Mar 03 '21 at 14:17

score 3 · Accepted Answer · answered Mar 03 '21 at 12:17

Using table of numbers

with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
),nmbs as(
   select row_number() over(order by t1.n) - 1 n
   from t0 t1 cross join t0 t2 cross join t0 t3
)
select event_id, 
             case when n = 0 
                   then start_time
                   else dateadd(day, n, convert(date, start_time))
             end start_time,
             case when datediff(day, start_time, end_time) = n
                   then end_time
                   else dateadd(second, -1, dateadd(day, n + 1, convert(datetime, convert(date, start_time))))
             end as end_time
from Events
cross apply (
  select top (datediff(day, start_time, end_time) + 1) n 
  from nmbs) ns

score 0 · Answer 3 · answered Mar 03 '21 at 13:33

The following method solves for the case of midnight between START_TIME and END_TIME. The "desired output" above indicates only a single midnight occurs between START_TIME and END_TIME.

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t ( Event_ID INT, START_TIME DATETIME2, END_TIME DATETIME2)
INSERT INTO #t (Event_ID, START_TIME, END_TIME)
VALUES
  ( 1001, '2021-02-21 14:00:00.000', '2021-02-21 18:00:00.000' )
, ( 1002, '2021-02-21 17:00:00.000', '2021-02-22 03:00:00.000' )
, ( 1003, '2021-02-21 18:00:00.000', '2021-02-21 22:00:00.000' )
, ( 1004, '2021-02-21 22:00:00.000', '2021-02-22 07:00:00.000' )

-- get original data plus midnight after START_TIME
IF OBJECT_ID('tempdb..#stage') IS NOT NULL DROP TABLE #stage
SELECT * 
, CONVERT(DATETIME2, CONVERT(DATE, DATEADD(DAY, 1, t.START_TIME))) d
INTO #stage
FROM #t t

-- get all rows
SELECT Event_ID, START_TIME
, CASE WHEN d > END_TIME THEN END_TIME ELSE d END END_TIME
FROM #stage

UNION ALL

-- get rows where midnight occurs between START_TIME and END_TIME
SELECT Event_ID
, CASE WHEN d > END_TIME THEN START_TIME ELSE d END  START_TIME
, END_TIME
FROM #stage 
WHERE d < END_TIME

ORDER BY Event_ID

You might find https://stackoverflow.com/a/65818648/14868997 interesting specifically case 3. In other words, don't scan the table a second time, just `cross apply` an extra row — Charlieface, Mar 03 '21 at 13:39

Splitting up events that occur over the day boundary

3 Answers3