使用日期范围和时间段检查大厅可用性

Question

>我有 2 个表Halls和Bookings，具有以下架构和示例数据

CREATE TABLE Halls 
(
id bigint PRIMARY KEY IDENTITY(1,1), 
hallName varchar(255) NOT NULL,
hallType varchar(255) NOT NULL, 
numSeats int NOT NULL, 
status varchar(255) NOT NULL
combinedIds int,
)
INSERT INTO Halls 
VALUES 
('Hall 1', 'Normal', 500, 'active', NULL),
('Hall 2', 'VIP', 30, 'active', NULL),
('Hall 3', 'VVIP', 5, 'active', NULL),
('Hall 4', 'Normal', 60, 'active', 6),
('Hall 5', 'Normal', 80, 'active', 6),
('Hall 4+5', 'Normal', 140, 'active', NULL);

SELECT * FROM Halls;

CREATE TABLE Bookings 
(
id bigint PRIMARY KEY IDENTITY(1,1), 
custId int NOT NULL,
hallId int NOT NULL, 
beginDate NVarChar(100), 
endDate NVarChar(100) NOT NULL,
time varchar(100) NOT NULL, 
status varchar(100) NOT NULL
)
INSERT INTO Bookings 
VALUES 
(1, 1, '2022-09-28', '2022-09-28', 'morning', 'confirmed'),
(6, 4, '2022-09-28', '2022-09-29', 'evening', 'cancelled'),
(4, 3, '2022-09-28', '2022-09-28', 'full time', 'pending'),
(4, 6, '2022-09-28', '2022-09-28', 'morning', 'pending'),
(9, 4, '2022-09-28', '2022-09-30', 'after noon', 'confirmed');
SELECT * FROM bookings;

预订时间段为："早上"，"中午后"，"晚上"和"全职">

我有上述 2 个表格，如果存在重叠的预订，我想验证传入的预订

简而言之，我想在插入之前检查是否有任何重叠的预订，其中日期范围(开始日期，结束日期)和时间段status!='cancelled'

退出如果我们采用上面的预订表及其示例数据

• 1号馆应仅在"中午之后"和"晚上"时间段提供

• 2号展厅应该在任何时间段可用，因为它不是("早上"，"中午之后"，"晚上"或"全职")

• 3号展厅根本不应该可用，因为它是全职预订的

• 4号馆应在早晚时段开放

  注意：晚上已预订但取消，这意味着可以再次预订

• 5号馆应随时开放("早上"、"中午后"、"晚上"或"全职")

• 6号馆应在早晚时段开放

  注意：6号馆实际上是4号馆和5号馆的组合，这意味着在任何给定的时间段和日期范围内，其中一个或两个都被预订，6号馆应该自动不可用/预订

以下是我到目前为止的一些试验，没有考虑Hall 6情况

DECLARE @hallId int = 1;
DECLARE @beginDate NVarChar(50) = '2022-09-28';
DECLARE @endDate NVarChar(50) = '2022-09-29';
DECLARE @time NVarChar(50) = 'full time';
SELECT * 
FROM Bookings b 
WHERE b.hallId = @hallId  
AND b.status != 'cancelled' 
AND beginDate <= @endDate 
AND endDate >= @beginDate
AND b.time IN (@time, 'full time')

此查询不返回任何内容，这意味着它可以在full time时间段内预订2022-09-28/29Hall 1，但随后检查预订表，"Hall 1"实际上是在 2022-09-28 早上预订

的和 如果我输入time column的时间段

如下

DECLARE @hallId int = 1;
DECLARE @beginDate NVarChar(50) = '2022-09-28';
DECLARE @endDate NVarChar(50) = '2022-09-29'; 
DECLARE @time NVarChar(50) = 'full time';
SELECT * 
FROM Bookings b 
WHERE b.hallId = @hallId  
AND b.status != 'cancelled' 
AND beginDate <= @endDate 
AND endDate >= @beginDate
AND b.time IN ('morning', 'after noon', 'evening', 'full time')

对于任何具有重叠beginDate或endDate的大厅，无论时间段如何，都将被视为booked

我想要这样的东西

CREATE PROCEDURE spBookings
@id bigInt, 
@hallId int, 
@custId int,
@beginDate NVarChar(50), 
@endDate NVarChar(50), 
@time NVarChar(50),
@status NVarChar(50), 
@msg NVarChar(200) OUT
AS
IF EXISTS (SELECT * FROM Bookings b 
WHERE b.hallId = @hallId  
AND b.status != 'cancelled' 
AND beginDate <= @endDate 
AND endDate >= @beginDate 
AND b.time IN (@time, 'full time'))
BEGIN
SET @msg = 'info|That date or time slot is booked, select a 
different one'
END
ELSE
BEGIN
INSERT INTO Bookings (custId, hallId, beginDate, endDate, time, status)
VALUES (@custId, @hallId, @beginDate, @endDate, @time, @status)
SET @msg = 'success|Booking success'
END

这是一个包含表、它们的模式、示例数据以及更多注释详细信息的数据库小提琴

我在这里发布了这个问题，这是关于大厅可用性报告，并被建议规范化复合大厅(6号大厅)的Halls表。

我真的很感激任何最好的解决方案来解决这个问题。

Answer 1

该解决方案类似于您之前的检查大厅预订状态问题。

基本概念是找到您输入的任何匹配项与预订信息。不是直接从Bookings表中，而是爆炸的，考虑到combinedIds.对于这种情况，您只对unavailable状态感兴趣，您可以过滤掉cancelled

select b.hallId, b.time, beginDate, endDate
from   Bookings b
where  b.status not in ('cancelled')
union all
select hallId = h.combinedIds, b.time, b.beginDate, b.endDate
from   Bookings b
inner join Halls h on b.hallId = h.id
where  b.status not in ('cancelled')
and    h.combinedIds is not null
union all
select hallId = h.id, b.time, b.beginDate, b.endDate
from   Bookings b
inner join Halls h on b.hallId = h.combinedIds
and    h.combinedIds is not null
where  h.combinedIds is not null

接下来，您需要将full time翻译成 3 行morning，afternoon(顺便说一下，它之间的拼写没有空格)，evening.您可以通过以下查询执行此操作

select time = 'morning' where @time in ('morning', 'full time')
union all
select time = 'afternoon' where @time in ('afternoon', 'full time')
union all
select time = 'evening' where @time in ('evening', 'full time')

上面的查询实际上是您上一个问题的解决方案。

把所有东西放在一起

SELECT *
FROM   
(
select time = 'morning' where @time in ('morning', 'full time')
union all
select time = 'afternoon' where @time in ('afternoon', 'full time')
union all
select time = 'evening' where @time in ('evening', 'full time')
) t
INNER JOIN
(
select b.hallId, b.time, beginDate, endDate
from   Bookings b
where  b.status not in ('cancelled')

union all

select hallId = h.combinedIds, b.time, b.beginDate, b.endDate
from   Bookings b
inner join Halls h on b.hallId = h.id
where  b.status not in ('cancelled')
and    h.combinedIds is not null

union all

select hallId = h.id, b.time, b.beginDate, b.endDate
from   Bookings b
inner join Halls h on b.hallId = h.combinedIds
and    h.combinedIds is not null
where  h.combinedIds is not null
) b ON t.time = b.time
WHERE b.hallId    = @hallId
AND   @beginDate <= b.endDate
AND   @endDate   >= b.beginDate

并将其合并到存储过程中

IF   EXISTS
(
< above query>
)
BEGIN
-- not available
END
ELSE
BEGIN
-- insert into Bookings table
END

数据库<>小提琴演示

旁注 ：

请使用正确的数据类型示例进行开始/结束日期使用DATE而不是 nvarchar