选择与SQL重叠的日期时间事件

;WITH CTE AS (
    SELECT 
        evnt2.id as ID, 
        sum(evnt1.duration) as Duration 
    from 
        #events evnt1
        INNER JOIN #events evnt2
            ON evnt1.id <> evnt2.id
    WHERE 
        DATEADD(second, evnt1.duration, evnt1.starttime)
        BETWEEN 
            evnt2.starttime AND DATEADD(second, evnt2.duration, evnt2.starttime)
    GROUP BY evnt2.id
) 
SELECT 
    #events.duration - CTE.duration, 
    * 
FROM 
    #events 
    INNER JOIN CTE 
        ON #events.id = CTE.id

我能想到的最简单的方法是使用多个自连接。我之所以说multiple是因为事件2可以在事件1之前或期间开始。

如果事件2总是在事件1之前开始,这里有一些代码可以回答您的问题。

select DateDiff(s,e1.StartTime, DateAdd(s,e2Before.Duration,e2Before.StartTime)) 
from events e1
join events e2Before
    on (e1.StartTime between e2Before.StartTime and DateAdd(s,e2Before.duration,e2Before.StartTime))
    and e1.event = 1
    and e2Before.event = 2

DateAdd 参数交换了一点,以适应事件2在事件1开始后开始的情况。