我有一个预订表,其中包含给定日期的可变时间范围。它们是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)之间的所有可用房间。
这是我要采取的方法。这会占用您的@minimum_interval,并找到一个大于该值的间隔。正如所写的,它不包括之前和之后。您可以通过在表中插入一个伪日期作为开始日期/时间和结束日期/时间来解决此问题,也可以在cte中将其构建为并集(首选)。
本质上,它所做的一切都是按开始时间排序——在[to]列和[next]列上执行datediff,并使用滞后函数计算[next]。这使您不必有一个专用的表,这也是为什么我会在cte中构建一个虚拟的开始和结束会议;由于数据未存储,因此不必对其进行更新。您可以将开始和结束时间存储在元数据结构中,以允许不同房间有不同的"开放"时间。
此外,您还可以将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;