sql - 在不使用循环或光标的情况下计算预约预约的可用会话

问题描述

这是一个非常简化的模型，但原理仍然存在。在给定时间之间存在可用资源可用的会话。如果您进行预订，它将与开始时间和持续时间一起存储在预订表中。

我可以像这样得到预订之间的差距

;with cte as (
    select ROW_NUMBER() OVER (ORDER BY StartTime)sq, StartTime,DATEADD(MINUTE,Duration,StartTime) as EndTime, Duration
    from @Reservations
)

select StartTime = a.EndTime , EndTime = b.StartTime
    from cte a
    INNER JOIN cte b on a.sq =  b.sq - 1

我正在努力的地方是更新 AvailableSessions 以考虑预订。我想返回一个如下所示的结果集。简单地说，对于 AvailableSession 中的每个原始插槽，必须根据预留在插槽内的开始和结束位置将其拆分为更多的拆分。例如，对于 1600 的预订，第一个会话需要从 1520 到 1600 分成两部分，依此类推。

我可以想出使用游标和循环的方法，但我更喜欢基于集合的方法。我认为这是我目前的 sql 知识的极限。

'Quick Tennis', '2020-11-02 15:20','2020-11-02 16:00' -- the original session is split and ends when the first reservation starts
'Quick Tennis', '2020-11-02 16:10','2020-11-02 16:35'-- the gap between the first reservation ending and the second starting
'Quick Tennis', '2020-11-02 16:45','2020-11-02 1720' -- the gap between the 2nd reservation ending and the third starting
'Quick Tennis', '2020-11-02 17:30','2020-11-02 2000' -- `no reservations made yet so still a contagious block

declare @AvailableSession TABLE (
    ID int,
    Name varchar(50),
    StartTime datetime,
    EndTime datetime
)

insert into @AvailableSession VALUES (1, 'Quick Tennis', '2020-11-02 15:20','2020-11-02 17:30')
insert into @AvailableSession VALUES (2, 'Quick Tennis', '2020-11-02 17:30','2020-11-02 20:00')

declare @Reservations TABLE
(
    StartTime datetime,
    Duration int
)

insert into @Reservations VALUES ('2020-11-02 16:00',10)
insert into @Reservations VALUES ('2020-11-02 16:35',10)
insert into @Reservations VALUES ('2020-11-02 17:20',10)

标签： sqlsql-serverdatabasecommon-table-expression

解决方案

我认为以下应该有效。可以通过组合步骤/ CTE 来简化它，但这可能是遵循我的逻辑的最简单方法：

表创建和示例数据

create table reservations (session_id integer, StartTime datetime, duration integer);
create table sessions(session_id integer, StartTime datetime,EndTime datetime);

insert into sessions(session_id, StartTime ,EndTime)
values (1,'2020-12-03 16:00', '2020-12-03 18:00');

insert into reservations(session_id, StartTime , duration)
    values 
     (1, '2020-12-03 16:00',    5) -- Reservation at start of session
     (1, '2020-12-03 16:10',    15),
     (1, '2020-12-03 16:40',    6),
     (1, '2020-12-03 16:46',    3), -- Starts when previous reservation ends
     (1, '2020-12-03 16:52',    17),
     (1, '2020-12-03 17:26',    10),
     (1, '2020-12-03 17:46',    6)
     (1, '2020-12-03 17:55',    5); -- Ends at session end

生成可用槽的 SQL

--Format reservations so that they have a start and end date and a sequence
with calc_resv (session_id, StartTime, EndTime, seq) as 
(
 select 
 session_id
 , StartTime
 , dateadd(mi, duration, StartTime ) as EndTime
 , cast(rank() over (partition by session_id order by StartTime ) as integer) as seq
 from reservations
 ),
 --Combine session and reservation data into 1 table
 data_set (session_id, StartTime, EndTime, seq) as 
 (
     select session_id , StartTime ,EndTime, 0
     from sessions
     union all
     select cr.session_id, cr.StartTime, cr.EndTime, cr.seq
     from calc_resv cr
 ),
 --Calculate available slots by using the end time of current record with the start time of the next record
 avail_slots (session_id, seq, NextStart, NextEnd) as 
 (
 select 
 session_id, seq
 ,case when seq = 0 then StartTime else
    EndTime end as NextStart
 , isnull(lead(startTime) over (order by session_id, seq), (select s.EndTime from sessions s where ds.session_id = s.session_id)) as NextEnd
 from data_set ds
 )
 select * from avail_slots
where NextStart <> NextEnd -- Case where 1st reservation starts at the start of the session or one reservation immediately follows another
 ;

结果数据集