I have 2 tables Halls & Bookings with the following schema & sample data
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;
Booking time slots are : "morning", "after noon", "evening" & "full time"
I have the above 2 tables and I want to validate incoming Bookings if an overlapping Bookings exist
in short, I want to check if any overlapping Bookings with status!='cancelled' exits for the date range(beginDate, endDate) & time slot before inserting
if we take in the above booking table with its sample data
Hall 1 should be available in 'after noon' & 'evening' time slots only
Hall 2 should be available in any time slot since it isn't ('morning', 'after noon', 'evening' OR 'full time')
Hall 3 should not be available at all since it's booked full time
Hall 4 should be available in morning & evening time slots
NOTE: Evening is booked but cancelled, which means it's available to book again
Hall 5 should be available in any time slot('morning', 'after noon', 'evening' OR 'full time')
Hall 6 should be available in morning & evening time slots
NOTE: Hall 6 is actually a combination of Halls 4 & 5 which means in any given time slots & date ranges for which one or both of them is Booked, Hall 6 should automatically be unavailable/booked
Here are some of my trials so far, without taking Hall 6 scenario into consideration
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')
This query returns nothing which means it's available to book Hall 1 on 2022-09-28/29 in full time time slot, but then check the Bookings table, 'Hall 1' is actually booked on 2022-09-28 morning
AND If I put in the time slots for the time column as below
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')
for any Hall that has an overlapping beginDate or endDate will be considered as booked regardless of time slot
I want something like this
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
Here is a db-fiddle with the tables, their schema, sample data plus some more commented details
I posted this question here which was about Halls Availability report before and got advised to Normalize the Halls table for composite halls (hall 6).
I really appreciate whatever the best possible solution to get around this.
The solution is similar to your earlier Check Hall Booking status question.
The basic concept is to find any matches of your input against the booking information. Not directly from the Bookings table but the exploded, taking into consideration of the combinedIds. And for this case, you are only interested in the unavailable status, you can filter out the 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
Next you need to translate full time into 3 rows of morning, afternoon (by the way it is spelled without a space in between), evening. You can do that with following query
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')
The above query are actually solution from your previous question.
Putting all together
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
And incorporate that into your stored procedure
IF EXISTS
(
< above query>
)
BEGIN
-- not available
END
ELSE
BEGIN
-- insert into Bookings table
END
Side Note :
Please use proper data type example for begin/end Date use DATE instead of nvarchar