sql - 如何从约会集中返回所有空闲时间段

Question

使用以下屏幕截图显示数据集的示例，

整数，日期时间2，日期时间2，整数

当它们没有作为约会存储在数据库中时，我如何返回所有在开始和停止范围内可用的空闲时间段，这些空闲时间段也等于或长于时段持续时间参数

declare @startRange datetime2
declare @endRange datetime2
declare @slotDurationInMinutes int

set @startRange = '2016-06-06T22:00:00.000Z'
set @endRange = '2016-06-07T21:59:00.000Z'
set @slotDurationInMinutes = 30


-- from this appointment dataset how do i query for all the free periods which are as long or longer than the slotduration parameter
-- these values are not stored in the table?
select TSO_Table_ID, time_start, time_end, duration from Org_TSO_Table

例如，slotduration 参数为 30 分钟的查询的预期输出为:

free_from=2016-06-06T22:00:00.000Zfree_until=2016-06-06T22:00:30.000Z

(这条记录包含搜索范围起始值)

free_from=2016-06-06T22:01:30.000Zfree_until=2016-06-06T22:04:00.000Z

free_from=2016-06-06T22:04:20.000Zfree_until=2016-06-06T22:10:00.000Z

free_from=2016-06-06T22:11:00.000Zfree_until=2016-06-06T22:11:30.000Z

free_from=2016-06-06T22:12:30.000Zfree_until=2016-06-07T21:59:00.000Z

(这条记录包含搜索范围结束值)

Answer 1

鉴于数据样本较少，很难说下面的解决方案是否适合您，但希望它能帮助您入门。

IF OBJECT_ID('tempdb..#sked') is not null
DROP TABLE #sked

IF OBJECT_ID('tempdb..#tmpResults') is not null
DROP TABLE #tmpResults

create table #sked(
    ID int,
    time_start datetime2,
    time_end datetime2,
    duration int)

insert into #sked (ID,time_start,time_end,duration) values
(4,'2016-06-06 00:30:00','2016-06-06 01:30:00',3600000),
(2,'2016-06-06 04:00:00','2016-06-06 04:20:00',1200000),
(1,'2016-06-06 10:00:00','2016-06-06 11:00:00',3600000),
(6,'2016-06-06 11:30:00','2016-06-06 12:30:00',3600000)

declare @startRange datetime2
declare @endRange datetime2
declare @slotDurationInMinutes int

set @startRange = '2016-06-05T00:00:00.000'
set @endRange = '2016-06-07T21:59:00.000'
set @slotDurationInMinutes = 30

select
    time_end as free_from,
    isnull(lead(time_start) over (order by time_end),@endRange) as free_until
into #tmpResults
from
    #sked
where
    time_end >= @startRange 
    and time_end <= @endRange
    --and duration <= (@slotDurationInMinutes * 60000) --conversion to milliseconds

union all

select
    case when @startRange < min(time_start) then @startRange end as free_from,
    case when @startRange < min(time_start) then min(time_start) end as free_until
from
    #sked
where
    time_end >= @startRange 
    and time_end <= @endRange
    --and duration <= (@slotDurationInMinutes * 60000) --conversion to milliseconds
order by
    free_from

select 
    *,
    DATEDIFF(minute,free_from,free_until) 
from 
    #tmpResults
where
    free_from is not null
    and DATEDIFF(minute,free_from,free_until) >= @slotDurationInMinutes