sql - 如何查找重叠时间段内可用的最大容量 SQL Server 2008

Question

我们正在做一个房间预订系统，只要不超过容量，同一个房间可以被不同的人预订。

我们正在尝试计算新预订请求的整个时间段内可用的最大座位数。

这可能最好用图表来解释。假设这个房间可容纳 100 人:

<-------I want to book this - How many seats are available?------->

   <---Booking 1: 10 seats--->       <---Booking 2: 10 seats--->
                       <---Booking 3: 10 seats--->
                                     <---Booking 4: 10 seats--->

因此，在请求期间的任一时间，可用的最大席位中的最小席位将为 70(预订 2、3 和 4 重叠，此时有 30 个席位无效)。

如何使用 SQL 计算？我们希望查询返回请求的时间段当前可用的座位数，在上述情况下为 70。

可用的表是 RoomBooking (room, start, end, capacity_taken) 和 Room (id, capacity)。

我们使用 SQL Server 2008。

Answer 1

对于特定的@room 和从@start_time 到@end_time 的时间段，尝试:

;with cte as 
(select id, capacity, @start_time time_point
 from room where id = @room
 union all
 select id, capacity, dateadd(mi, 1, time_point) time_point
 from cte where dateadd(mi, 1, time_point) < @end_time)
select min(capacity_left) max_available from
(select c.time_point, max(c.capacity) - sum(b.capacity_taken) capacity_left
 from cte c
 join RoomBooking b
   on c.id = b.room and
      c.time_point >= b.start and
      c.time_point < b.end
 group by c.time_point) sq

请注意，在整个整个预订期间可用的最大容量是该期间内任何时间点的最小可用容量 - 因此在示例中这将是70 个席位。