Check Hall Availability using Date range and Time slot-CodePudding

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.

CodePudding user response：

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

db<>fiddle demo

Side Note :

Please use proper data type example for begin/end Date use DATE instead of nvarchar