I'm making a Hall Booking System, and I've being struggling with the Booking Module
I have 2 tables Halls & Bookings
Halls table With Sample Data
id hallName hallType numSeats status
---------------------------------------------
1 Hall 1 Normal 500 active
2 Hall 2 VIP 30 active
3 Hall 3 VVIP 5 active
4 Hall 4 Normal 60 active
5 Hall 5 Normal 80 active
6 Hall 4+5 Normal 140 active
Booking Table with Sample Data
id custId hallId beginDate endDate time status
-----------------------------------------------------------------
1 1 1 2022-09-26 2022-09-26 morning confirmed
2 6 4 2022-09-26 2022-09-26 evening cancelled
3 4 3 2022-09-26 2022-09-26 full time pending
4 9 4 2022-09-26 2022-09-30 after noon confirmed
Booking time slots are "morning", "after noon", "evening" & "full time"
I have being trying on the Booking validations as well as a report that shows Halls booking status
Edit
After suggestions in the comments I have edited and removed the second part, maybe will post seperately if I can't figure out
Here is what I want to be the result
The records can be filtered by endDate(e.g 2022-09-26)
if Booking exists which is not cancelled for the provided beginDate, the time slot should be labelled as Booked
if a booking does not exist for the provided beginDate or its canclled, the time slot should be labeled as Available
If a full time Booking slot ecists for the provied beginDate, all the time the 3 slots should be labeled as Booked
Here is the twist that may complicate things
As you can see from the Halls table, Hall 6 is a combination of Halls 4 & 5 which means if any of them is booked, Hall 6 should be marked as unavailable or even Booked will be fine.
Overall, here is a sample result based on the above booking table
hallName hallType morning after noon evening
------------------------------------------------------
hall 1 Normal Booked available available
hall 2 VIP available available available
hall 3 VVIP Booked Booked Booked
hall 4 Normal available Booked available
hall 5 Normal available available available
hall 6 Normal available unavailable available
if we take the Booking Table with Sample data, the result should be as shown above
I'm not that much familiar with Joins, merges, etc which are what I'm thinking the solution is. I tried merge with When matched and when Not matched but could not figure it out to work
I then tried left joining the Halls table to the Booking table seems to be the closest so far and the output is clear.
DECLARE @date NVarchar(50) = '2022-09-26'
SELECT h.id, h.hallName, h.hallType, b.time FROM Halls h LEFT JOIN
Bookings b ON b.hallId=h.id WHERE b.beginDate=@date
this returns only Booked halls with beginDate of that
If I drop the WHERE clause, all the 6 halls are returned but time slots which aren't booked are returned as NULL
btw I'm working on the last module and at firts I was working with a single check in date and requirement changed into Range beginDate & endDate & that is when problems arose.
Firstly you need to fix your design how are you intend to store information that Hall 6 is a combine of Hall 4 + 5
One simple way is to add another column in the Halls table that indicate that. Example
create table Halls
(
id int,
hallName varchar(10),
hallType varchar(10),
numSeats int,
status varchar(10),
combine_id int
);
insert into Halls values
(4, 'Hall 4', 'Normal', 60, 'active', 6),
(5, 'Hall 5', 'Normal', 80, 'active', 6),
(6, 'Hall 4+5', 'Normal', 140, 'active', null);
Once that is in-placed, you need to translate the Bookings to handle the combined Halls. This is perform by the CTE BookingData. It will create row for Hall 6 if Hall 4 or Hall 5 is pending or confirmed. And simiarly the other way round. When Hall 6 is Booked, Hall 4 and Hall 5 will be unavailable.
After that it is just simple pivoting of data
The solution:
DECLARE @date date = '2022-09-26';
with BookingData as
(
select b.hallId, b.time, b.status, beginDate
from Bookings b
union all
select hallId = h.combine_id, b.time,
status = case when b.status in ('pending', 'confirmed')
then 'unavailable'
else 'available'
end,
beginDate
from Bookings b
inner join Halls h on b.hallId = h.id
where h.combine_id is not null
union all
select hallId = h.id, b.time,
status = case when b.status in ('pending', 'confirmed')
then 'unavailable'
else 'available'
end,
beginDate
from Bookings b
inner join Halls h on b.hallId = h.combine_id
where h.combine_id is not null
)
SELECT id,
hallName,
hallType,
[morning] = isnull([morning], 'available'),
[afternoon] = isnull([afternoon], 'available'),
[evening] = isnull([evening], 'available')
FROM
(
SELECT h.id, h.hallName, h.hallType, t.timeSlot,
status = case when b.status in ('pending', 'confirmed')
then 'Booked'
when b.status in ('cancelled')
then 'unavailable'
when b.status in ('unavailable')
then b.status
else NULL
end
FROM Halls h
LEFT JOIN BookingData b ON b.hallId = h.id
and b.beginDate = @date
OUTER APPLY
(
select timeSlot = 'morning' where b.time in ('morning', 'full time')
union all
select timeSlot = 'afternoon' where b.time in ('afternoon', 'full time')
union all
select timeSlot = 'evening' where b.time in ('evening', 'full time')
) t
) D
PIVOT
(
MAX (status)
FOR timeSlot in ( [morning], [afternoon], [evening] )
) P