Splitting up events that occur over the day boundary

user_14096844 0
Viewed 98

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

sql-server-2016 sql-server sql
3 Answers

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.

user_11440350

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
user_62199790

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
user_145824380
Related