如何从约会集返回所有的免费时间段

本文介绍了如何从约会集返回所有的免费时间段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

Using the following screenshot to show an example of the data set,

int，datetime2，datetime2，int

int,datetime2,datetime2, int

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

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分钟的查询的预期输出将是：

For example the expected output of a query where the slotduration param is 30 minutes would be:

free_from = 2016-06-06T22：00：00.000Z free_until = 2016-06-06T22：00：30.000Z

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

（此记录包含搜索范围的起始值）

(This record contains the search range start value)

free_from = -06T22：01：30.000Z free_until = 2016-06-06T22：04：00.000Z

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

free_from = 2016-06-06T22：04：20.000Z free_until = 2016-06-06T22：10：00.000Z

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

free_from = 2016-06-06T22：11：00.000Z free_until = 2016-06-06T22：11：30.000Z

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

free_from = 2016-06-06T22：12：30.000Z free_until = 2016-06-07T21：59：00.000 Z

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

（此记录包含搜索范围结束值）

(This record contains the search range end value)

推荐答案

很难说如果下面的解决方案适合你，给定小数据样本，但希望它会让你开始。

It's hard to say if the solution below will work for you, given the small data sample, but hopefully it will get you started.

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