选择重叠的时间范围 [英] Selecting overlapping time ranges

问题描述

T-SQL DateTime问题。

T-SQL DateTime Question.

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

I have a set of time ranges. During those time ranges there could be a set of overlapping time ranges that I call 'blocked' out time. The blocked time wouldn't span more than one day. What I want to do is split the time to exclude the blocked out time, basically giving me the time ranges that are not 'blocked'. Its safe to assume that blocked times cant fall outside of the times ranges.

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

Example: I work 9am to 5pm with a 30 min lunch break at 1pm. I want the result of 2 rows: 9am to 1pm and 1.30pm to 5pm.

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

As mentioned, I have a set of time ranges so in the above example the working hours may differ on a daily basis and the number of breaks as well as their duration may differ.

我想就SQL而言，输入参数看起来像这样：

I guess in terms of SQL the input parameters would look like this:

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

I could do this with a cursor or while loop but if someone could suggest how to do this without iteration that would be great - thanks.

推荐答案

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

I thought I'd share the solution I finally settled on:

对临时表的轻微调整是，我已将StartDate字段添加到@timeranges和@blockedtimes

Slight adjustment to the temp table in that I've added a StartDate field to both @timeranges and @blockedtimes

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

无论如何，似乎比发布的其他答案更简单-为大家加油打气：）

Anyways seems simpler than some of the other answer posted - cheers for everyones help :)

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

这篇关于选择重叠的时间范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！