sql - 拆分发生在一天边界内的事件

Question

我有一个包含开始时间和结束时间的事件表，其中一些事件的开始时间在午夜之前，结束时间在午夜之后。我想生成在午夜分界点拆分这些事件的输出，以便将它们计入各自的日期。

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

上表可以通过查询产生:

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'
;

我想要的输出将在午夜拆分跨越多天的事件:

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

如有任何帮助，我们将不胜感激。理想情况下，我希望在没有函数或创建新表的情况下生成它。

请注意，我使用的是 SQL Server 2016

Answer 1

使用数字表

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