选择重叠的时间范围

Question

T-SQL日期时间问题。

我有一组时间范围，在这些时间范围内可能存在一组重叠的时间范围，我称之为“锁定”的时间。锁定时间不会跨越超过一天。我想做的是将时间拆分以排除锁定的时间，基本上给我那些没有被“锁定”的时间范围。可以安全地假设锁定时间不会落在时间范围之外。

例如：我从早上9点工作到下午5点，中午1点休息30分钟。我希望得到2行结果：早上9点到中午1点和下午1点30到5点。

如上所述，我有一组时间范围，因此在上述示例中，每天的工作时间和休息次数以及持续时间可能会有所不同。

我想在SQL中输入参数看起来像这样：

declare @timeranges table ( StartDateTime datetime, EndDateTime datetime )
declare @blockedtimes table ( StartDateTime datetime, EndDateTime datetime )

insert into @timeranges 
select '01 Jan 2009 09:00:00', '01 Jan 2009 17:00:00'
union select '02 Feb 2009 10:00:00', '02 Feb 2009 13:00:00'

insert into @blockedtimes 
select '01 Jan 2009 13:00:00', '01 Jan 2009 13:30:00'
union select '02 Feb 2009 10:30:00', '02 Feb 2009 11:00:00'
union select '02 Feb 2009 12:00:00', '02 Feb 2009 12:30:00'

结果集将如下所示。

Start                   End
---------------------   ---------------------
'01 Jan 2009 09:00:00' '01 Jan 2009 13:00:00'
'01 Jan 2009 13:30:00' '01 Jan 2009 17:00:00'
'02 Feb 2009 10:00:00' '02 Feb 2009 10:30:00'
'02 Feb 2009 11:00:00' '02 Feb 2009 12:00:00'
'02 Feb 2009 12:30:00' '02 Feb 2009 13:00:00'

我可以用游标或while循环来实现，但如果有人能建议如何在不迭代的情况下完成这个任务，那就太好了 - 谢谢。

- Chris Moutray

---

这个之前已经出现过。 - Daniel A. White

@Daniel A. White：我找不到它，能分享一个链接吗？ - Lieven Keersmaekers

我找不到答案，这就是为什么我发表了这个问题的原因。 - Chris Moutray

Answer 1

第一版可能存在一些问题，但我会继续努力。
针对给定数据有效，只需要尝试其他情况。

declare @timeranges table ( StartDateTime datetime, EndDateTime datetime )
declare @blockedtimes table ( StartDateTime datetime, EndDateTime datetime )

insert into @timeranges 
select '01 Jan 2009 09:00:00', '01 Jan 2009 17:00:00'
union select '02 Feb 2009 10:00:00', '02 Feb 2009 13:00:00'
--union select '03 Feb 2009 10:00:00', '03 Feb 2009 15:00:00'


insert into @blockedtimes 
select '01 Jan 2009 13:00:00', '01 Jan 2009 13:30:00'
union select '02 Feb 2009 10:30:00', '02 Feb 2009 11:00:00' 
union select '02 Feb 2009 12:00:00', '02 Feb 2009 12:30:00'

--build an ordered, time range table with an indicator
--to determine which ranges are timeranges 'tr'
--and which are blockedtimes 'bt'
--
declare @alltimes table (row int, rangetype varchar(10), StartDateTime datetime, EndDateTime datetime )
insert into @alltimes
select
    row_number() over (order by a.startdatetime), *
from
    (
    select 'tr' as rangetype ,startdatetime, enddatetime from @timeranges
    union
    select 'bt' as rangetype ,startdatetime, enddatetime from @blockedtimes
    )a

--what does the data look like  
--
select * from @alltimes


--
-- build up the results
select
    --start time is either the start time of a timerange, or the end of a blockedtime
    case 
        when at1.rangetype = 'tr' then at1.startdatetime
        when at1.rangetype = 'bt' then at1.enddatetime 
    end as [Start],
    case 
        --a time range followed by another time range : end time from the current time range
        when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'tr'
            then at1.enddatetime

        --a time range followed by nothing (last record) : end time from the currenttime range
        when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) is null
            then at1.enddatetime

        --a time range followed by a blockedtime : end time is start time of blocked time
        when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'bt'
            then (select top 1 at2.startdatetime from @alltimes at2 where at2.row > at1.row and at2.rangetype = 'bt' order by row)

        --a blocked time followed by a blockedtime : end time is start time of next blocked time    
        when at1.rangetype = 'bt'  and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'bt'
            then (select top 1 at2.startdatetime from @alltimes at2 where at2.row > at1.row and at2.rangetype = 'bt' order by row)

        --a blocked time followed by a time range : end time is end time of previous time range     
        when at1.rangetype = 'bt'  and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'tr'
            then (select top 1 at2.enddatetime from @alltimes at2 where at2.row < at1.row and at2.rangetype = 'tr' order by row desc)

        --a blocked time followed by nothing (last record) : end time is end time of previous time range    
        when at1.rangetype = 'bt'  and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) is null
            then (select top 1 at2.enddatetime from @alltimes at2 where at2.row < at1.row and at2.rangetype = 'tr' order by row desc)

    end as [End]

from @alltimes at1

- Kev Riley

Answer 2

我想分享一下我最终采用的解决方案：

在临时表中进行了轻微的调整，我向@timeranges和@blockedtimes都添加了一个StartDate字段。

declare @timeranges table ( StartDate datetime, StartDateTime datetime, EndDateTime datetime ) 
declare @blockedtimes table ( StartDate datetime, StartDateTime datetime, EndDateTime datetime )

无论如何，似乎比其他答案发布的要简单 - 感谢大家的帮助 :)

select 
    *
from
(
    -- first SELECT get start boundry
    select t.StartDateTime s, b.StartDateTime e
    from @timeranges t, @blockedtimes b
    where 
        -- same day and blocks overlaps timerange
        t.StartDate = b.StartDate and (t.StartDateTime <= b.EndDateTime and b.StartDateTime <= t.EndDateTime)
    and
        -- the following is the important bit for this SELECT   
        not exists (select 1 from @blockedtimes b2 where b2.StartDate = b.StartDate and b2.StartDateTime < b.StartDateTime)
union
    -- second SELECT get spikes ie middle
    select b1.EndDateTime s, b2.StartDateTime e
    from @timeranges t, @blockedtimes b1, @blockedtimes b2
    where 
        -- same day and blocks overlaps timerange
        t.StartDate = b1.StartDate and (t.StartDateTime <= b1.EndDateTime and b1.StartDateTime <= t.EndDateTime) 
    and 
        -- same day and blocks overlaps timerange
        t.StartDate = b2.StartDate and (t.StartDateTime <= b2.EndDateTime and b2.StartDateTime <= t.EndDateTime) 
    and 
        -- the following is the important bit for this SELECT
        b1.EndDateTime < b2.StartDateTime
union
    -- third SELECT get end boundry
    select b.EndDateTime s, t.EndDateTime e
    from @timeranges t, @blockedtimes b
    where 
        -- same day and blocks overlaps timerange
        t.StartDate = b.StartDate and (t.StartDateTime <= b.EndDateTime and b.StartDateTime <= t.EndDateTime)
    and 
        -- the following is the important bit for this SELECT
        not exists (select 1 from @blockedtimes b2 where b2.StartDate = b.StartDate and b2.StartDateTime > b.StartDateTime)
) t1

- Chris Moutray

Answer 3

以下方案适用于输入数据满足两个条件：A）每个阻塞时间间隔都在一个时间范围间隔内（您说可以假定这一点）。B）阻塞时间间隔不重叠-也就是说，没有时间因为同时落在多个阻塞的时间间隔而被“双重阻塞”。

    with TB(src,S,E) as (
      select 'T', StartDateTime, EndDateTime from @timeranges as T
      union all
      select 'B', StartDateTime, EndDateTime from @blockedtimes as B
    ), TBP(evt,switch,DT,rk) AS (
      select
        src+DT,
        CHARINDEX(src+DT,'TEBSTSBE')/5 AS OffOn,
        EventDT,
        row_number() over (
          order by EventDT, CHARINDEX(src+DT,'TEBSTSBE')/5 desc
        ) as rk
      from TB UNPIVOT (
        EventDT FOR DT in ([S],[E])
      ) as U
    )
      select
        min(DT) as StartDateTime,
        max(DT) as EndDateTime
      from TBP
      group by (rk-1)/2
      having min(DT) < max(DT)
      order by (rk-1)/2;

这是如何工作的？

首先，将所有日期时间值标记为0或1，以指示特定时间可用性结束（0，对于@timeranges中的EndDateTime值和@blockedtimes中的StartDateTime值）或开始（1，对于另外两种可能性）。然后按时间、标记顺序排列时间和标记，并使用row_number函数编号一个名为rk的列。标记可以使用CASE表达式更易读，但CHARINDEX要少打几个字...

由于假设，标记序列将在0和1之间交替：0,1,0,1,0,1...，每个连续的（0,1）对表示可用性间隔的开始和结束。这些间隔可以使用（rk-1）/2进行编号。

行在每个可用性间隔上分组。组中的最小日期时间是开始时间，最大日期时间是结束时间，如果它们不同，则该组表示属于结果集的非空间隔。请注意，对于您的数据，不存在空间隔，但如果两个阻止时间相邻或阻止时间与时间范围同时结束，则会出现空间隔。

最后，结果被旋转以显示所需的格式。

这不是最容易阅读的代码，但值得琢磨。像这样使用row_number和分组的解决方案有时对于解决棘手的问题很有用。

- Steve Kass

Answer 4

SELECT  COALESCE(bt.StartDateTime, tr.StartDateTime),
        bt.EndDateTime
FROM    @timeranges tr
CROSS APPLY
        (
        SELECT  bp.StartDateTime, bt.StartDateTime AS EndDateTime
        FROM    (
                SELECT  StartDateTime
                FROM    @blockedtimes bt
                WHERE   bt.EndDateTime >= tr.StartDateTime
                        AND bt.StartDateTime <= tr.EndDateTime
                UNION ALL
                SELECT  tr.EndDateTime
                ) bt
        OUTER APPLY
                (
                SELECT  TOP 1 EndDateTime AS StartDateTime
                FROM    @blockedtimes bti
                WHERE   bti.EndDateTime >= tr.StartDateTime
                        AND bti.StartDateTime <= tr.EndDateTime
                        AND bti.StartDateTime < bt.StartDateTime
                ORDER BY
                        bti.StartDateTime DESC
                ) AS bp
        ) bt

此解决方案基于以下假设：

• 时间范围永远不会与其他时间范围重叠
• 阻止的时间永远不会与其他阻止的时间重叠

- Quassnoi

---

这看起来是一个优雅的解决方案，所以我试图理解它的工作原理，但我注意到它没有产生正确的结果 - 第三个和第四个结果是不正确的 :( - Kev Riley

好的，在我开始看之前，你是怎么想出这个解决方案的？你能告诉我一下你是如何得出这个解决方案的吗？我的头都快晕了！ - Kev Riley

@Kev：你需要想出一个算法和一组基于集合的公式，然后将它们融合在一起。我无法解释如何做到，我只是看得出来 :) 你可以阅读我的文章了解更多信息：http://explainextended.com/2009/07/12/double-thinking-in-sql/ - Quassnoi