选择重叠的时间范围

Question

T-SQL DateTime问题。

我有一组时间范围。在这些时间范围内，可能会有一组重叠的时间范围，我称之为“封锁”时间。封锁的时间不会超过一天。我想要做的是分配时间以排除阻塞时间，基本上是给我没有“阻塞”的时间范围。可以肯定的是，阻塞时间不能超出时间范围。

示例：我工作时间是从上午9点到下午5点，在下午1点有30分钟的午餐时间。我想要2行的结果：9am至1pm和1.30pm至5pm。

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

我猜就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循环来执行此操作，但是如果有人可以建议如何在不进行迭代的情况下执行此操作，那就太好了-谢谢。

Answer 1

我以为我会分享我最终确定的解决方案：

稍微调整一下临时表，因为我已经向@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