SQL 提取一天中所有未保留的 30 分钟或 60 分钟时间段

Question

我有一个预订表，其中包含给定日期的可变时间范围。它们是 30 分钟、60 分钟、用户输入的任何给定分钟范围的保留时间段。

我想要做的是创建一个 SQL 语句，该语句可以为我提供给定日期中未保留的所有时间段。用户可以指定分钟范围(30 分钟、60 分钟、90 分钟)间隔。我从哪里开始？

保留表是基本的:

ROOM, RESERVED_DATE, FROM_TIME, TO_TIME
A     09/02/2014     9:00 AM    10:00 AM
B     09/02/2014     11:00 AM   12:00 PM
C     09/02/2014     1:30 PM    2:00 PM

我允许用户说他们想要在特定增量(30 分钟、60 分钟、90 分钟)内的营业时间(上午 8:00 - 下午 4:30)之间的所有可用房间。

Answer 1

这是我要采取的方法。这需要您的 @minimum_interval 并找到一个大于该间隔的间隔。正如所写，它不包括之前和之后。您可以通过在表中插入一个虚拟日期作为开始日期/时间和结束日期/时间来解决这个问题，或者将其构建为 cte 中的联合(首选)。

基本上它所做的就是按开始时间排序，在 [to] 列和 [next] 列上执行 datediff，其中 [next] 是使用滞后函数计算的。这使您不必有一个专用的 table ，这也是为什么我会在 cte 中建立一个虚拟的开始和结束 session ；由于数据未存储，因此不必更新。您可以在元数据结构中存储开始和结束时间，以允许不同房间的不同“开放”时间段。

此外，您可以将 PARTITION 子句添加到滞后函数中，以允许它在不指定房间的情况下工作。

这是一个有趣的练习，感谢您提出问题!

declare @date [datetime] = cast(current_timestamp as [date]);
declare @minimum_interval [int] = 15
    , @room           [sysname] = N'A';
declare @reservation table (
[room]   [sysname]
, [from] [datetime]
, [to]   [datetime]);
insert into @reservation
        ([room],[from],[to])
values      (N'A',N'20140902 09:00',N'20140902 10:00'),
        (N'A',N'20140902 10:00',N'20140902 11:00'),
        (N'A',N'20140902 13:00',N'20140902 14:00');
with [builder]
 as (select [room]
            , [from]
            , [to]
            , lag ([from]
                   , 1
                   , 0)
                over (
                  order by [from] desc) as [next]
     from   @reservation
     where  [room] = @room)
select [room]
   , [from]
   , [to]
   , [next]
from   [builder]
where  datediff(minute
            , [to]
            , [next]) > @minimum_interval;