sql - 查找两个日期之间的时间冲突

Question

我正在处理一个查询，该查询应该找到两个日期之间的午餐时间。例如，午餐时间为 14:00 PM 至 15:30 PM。日期为:“2016-06-09 10:00:00.000”和“2016-06-11 10:00:00.000”这些日期之间有两次午餐时间。另一个例子:

'2016-06-09 15:00:00.000' and '2016-06-11 10:00:00.000' : 2 Times'2016-06-09 17:00:00.000' and '2016-06-11 10:00:00.000' : 1 Time'2016-06-09 13:00:00.000' and '2016-06-11 15:00:00.000' : 3 Times

但我永远做不到:(

Answer 1

这似乎有效:

declare @t table (StartAt datetime not null,EndBefore datetime not null)
insert into @t(StartAt,EndBefore) values
('2016-06-09T15:00:00.000','2016-06-11T10:00:00.000'),
('2016-06-09T17:00:00.000','2016-06-11T10:00:00.000'),
('2016-06-09T13:00:00.000','2016-06-11T15:00:00.000')

;With Dates as (
    select MIN(DATEADD(day,DATEDIFF(day,0,StartAt),0)) as ADate
    from @t
    union all
    select DATEADD(day,1,ADate) from Dates
    where exists (select * from @t where EndBefore > DATEADD(day,1,ADate))
), Lunches as (
    select DATEADD(minute,(14*60),ADate) as StartAt,
           DATEADD(minute,(15*60+30),ADate) as EndBefore
    from Dates
)
select
    *,(select COUNT(*) from Lunches l
       where l.StartAt < t.EndBefore and t.StartAt < l.EndBefore)
from @t t

我们使用两个 CTE 来 (a) 导出所有相关日期，以及 (b) 根据日期计算所有可能的午餐时间，最后找到与原始时段重叠的所有午餐。

结果:

StartAt                 EndBefore               
----------------------- ----------------------- -----------
2016-06-09 15:00:00.000 2016-06-11 10:00:00.000 2
2016-06-09 17:00:00.000 2016-06-11 10:00:00.000 1
2016-06-09 13:00:00.000 2016-06-11 15:00:00.000 3

注意 - 许多人在尝试确定重叠的定义时过于复杂化。我在这里使用一个简单的定义，通常唯一需要更改的是决定是否使用 <或<= ，这取决于您是否认为两个相邻的时间段重叠，但在其他方面不涵盖相同的时间段。

因此，如果您在查询恰好在 15:30 或 开始的时间段时对上述查询生成的答案不满意，您可能需要更改定义14:00 准时结束。