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

Question

我有一个 SQL 表 Events（ID int、Event int、StartTime datetime、Duration int）。

Event 是事件代码（1=系统运行，2=中断）

Duration 是事件处于活动状态的秒数。

我想要获取事件 1 处于活动状态的秒数，但减去事件 2 的持续时间。

例如，事件 1 为 1:00 至 6:00，事件 2 为 0:00 至 2:00，事件 2 为 5:00 至 6:00。总时间应为2:00至5:00-> 3小时。

我能想到一个办法：对于每个事件1找到所有可以与事件1相交的事件2，对于每个事件2在该集中：修剪其持续时间以仅获取在事件 1 期间处于活动状态的部分。

例如，对于我的事件 1 (1:00 - 6:00)，我将找到事件 2 (0:00 - 2:00)，只获取我感兴趣的部分（1:00-2:00）；找到另一个活动2(5:00-6:00)，找到我感兴趣的部分(这是整个活动5:00-6:00) - 总共是两个小时。活动1总时间为5小时； 5 小时 - 2 小时（活动 2）为 3 小时。

但如果在指定的时间范围内有数千个事件，这将不起作用，所以我更喜欢没有循环（游标）的解决方案提示。

Answer 1

;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

Answer 2

我能想到的最简单的方法是使用多个自连接。我说多个是因为事件 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 开始后开始的情况。