Help in writing Query for hall booking date availability search

Question

I need help in writing query to find the time slot availability for a hall booking system.. details are given below

I have a Hall table which stores the hall details and HallBooking table which stores the start and from time of the bookings done..

Hall - HallId - Name

HallBooking - HallBookingId - HallId - BookingPersonName - StartDateTime - EndDateTime

A user can search for hall availability by specifying a start datetime and end datetime.. the query should pick and show whether the hall is available for that time..

If the time slot is NOT available the query should pick available slots with same duration for that day and show to the user.

Thanks in advance Anz

Answer 1

Here's a test script that might get you started.

DECLARE @Hall TABLE
    (
      HallID INT PRIMARY KEY ,
      HallName VARCHAR(1)
    )
DECLARE @HallBooking TABLE
    (
      HallBookingID INT PRIMARY KEY ,
      HallID INT ,
      BookingPersonName VARCHAR(10) ,
      StartDateTime DATETIME ,
      EndDateTime DATETIME
    )


INSERT  @Hall
        SELECT  1 ,
                'A'
        UNION ALL
        SELECT  2 ,
                'B'
        UNION ALL
        SELECT  3 ,
                'C'


--TEST 1 Check for available Hall
DECLARE @HallID INT ,
    @StartDateTime DATETIME ,
    @EndDateTime DATETIME
DECLARE @Status VARCHAR(6)

SELECT  @HallID = 1 ,
        @StartDateTime = '20091021 8:00:00' ,
        @EndDateTime = '20091021 12:00:00'


--INSERT TestReservationCode here

SELECT  @Status = 'Open'
FROM    @Hall h
WHERE   h.HallID = @HallID
        AND NOT EXISTS ( SELECT *
                         FROM   @HallBooking hb
                         WHERE  h.HallID = hb.HallID
                                AND ( @StartDateTime >= hb.StartDateTime
                                      AND @StartDateTime < hb.EndDateTime
                                      OR @EndDateTime > hb.StartDateTime
                                      AND @EndDateTime <= hb.EndDateTime
                                    ) )


IF @Status IS NULL 
    BEGIN

        SELECT  @Status = 'Closed'

        SELECT  *
        FROM    @Hall h
        WHERE   h.HallID <> @HallID
                AND NOT EXISTS ( SELECT *
                                 FROM   @HallBooking hb
                                 WHERE  h.HallID = hb.HallID
                                        AND ( @StartDateTime >= hb.StartDateTime
                                              AND @StartDateTime < hb.EndDateTime
                                              OR @EndDateTime > hb.StartDateTime
                                              AND @EndDateTime <= hb.EndDateTime
                                            ) )


    END


SELECT  @Status

To test the reservation status, insert the following code beneath the INSERT comment above and re-run.

--TestReservationCode
INSERT  INTO @HallBooking
        ( HallBookingID ,
          HallID ,
          BookingPersonName ,
          StartDateTime ,
          EndDateTime
        )
VALUES  ( 1 , -- HallBookingID - int
          1 , -- HallID - int
          'Stuart' , -- BookingPersonName - varchar(10)
          '2009-10-21 10:00' , -- StartDateTime - datetime
          '2009-10-21 12:00'  -- EndDateTime - datetime
        )

INSERT  INTO @HallBooking
        ( HallBookingID ,
          HallID ,
          BookingPersonName ,
          StartDateTime ,
          EndDateTime
        )
VALUES  ( 2 , -- HallBookingID - int
          2 , -- HallID - int
          'Stuart' , -- BookingPersonName - varchar(10)
          '2009-10-21 10:00' , -- StartDateTime - datetime
          '2009-10-21 12:00'  -- EndDateTime - datetime
        )