使用SQL选择重叠的datetime事件 [英] select overlapping datetime events with SQL

问题描述

我有一个SQL表Events (ID int, Event int, StartTime datetime, Duration int).

Event是事件代码(1 =系统正在运行，2 =中断)

Event is event code (1=system running, 2=break)

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

Duration is the amount of seconds that the event was active.

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

I'd like to get the amount of seconds that event 1 was active, but subtract the duration of event 2.

例如事件1是从1:00到6:00，事件2是从0:00到2:00，事件2是从5:00到6:00.总时间应该是从2:00到5:00-> 3小时.

E.g. event 1 was from 1:00 to 6:00, event 2 from 0:00 to 2:00 and event 2 from 5:00 to 6:00. The total time should be from 2:00 to 5:00 -> 3 hours.

有一种我可以想到的方法:对于每个事件1 ，找到可以与事件1相交的所有事件2 ，对于每个事件2 :设置其持续时间，以仅获取事件1中处于活动状态的部分.

There is a way I can think of: for each event 1 find all events 2 that can intersect with event 1, and for each event 2 in that set: trim its duration to get only the part that was active during its event 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小时.

e.g. for my event 1 (1:00 - 6:00) I'll find event 2 (0:00 - 2:00), get only the part that interests me (1:00-2:00); find another event 2(5:00-6:00), get the part that interests me (it's whole event 5:00-6:00) - that summed up are two hours. The total time of event 1 was 5 hours; 5 hrs - 2 hrs (event 2) is 3 hours.

但是如果在指定的时间范围内有成千上万的事件，这将无法正常工作，因此我希望提供一种无循环(游标)的解决方案提示.

But this won't work if there are thousands of events in the specified time frame, so I'd prefer a hint of solution without loops (cursors).

推荐答案

;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

这篇关于使用SQL选择重叠的datetime事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！