Finding availability by time

Question

I have three tables.

• Table (t), a resource in my system. It has a maximum capacity.
• Booking (b), a reservation in the system, it hase FromDatimeTime, ToDateTime and nrOfPeople
• BookedTable (bt), connects the booking with table and how many seats that are used by this booking, a booking can have more than one table.

The problem is that if i have three bookings:

1. 4 people comming at 12:00 and leaving at 14:00.
2. 4 people comming at 12:00 and leaving at 13:00.
3. 4 people comming at 13:00 and leaving at 14:00.

All bookings using the same table with 8 seats. I want to se if the table is "overbooked".

Setup code

CREATE TABLE Tables (
     TableNr INT, 
     Seats INT
     )
GO 
CREATE TABLE Booking 
    ( 
    BookingNr INT, 
    Time_From DATETIME, 
    Time_TO DATETIME, 
    Guests INT
    )
GO 
CREATE TABLE Table_Booking   
(
 TableBookingId INT ,
 BookingNr INT ,
 TableNr INT ,
 GuestOnTable int 
)
GO 
INSERT INTO [Tables] (  [TableNr], [Seats]) VALUES ( 1, 8 ) 
INSERT INTO [Tables] (  [TableNr], [Seats]) VALUES ( 2, 4 ) 
INSERT INTO [Tables] (  [TableNr], [Seats]) VALUES ( 3, 4 ) 

INSERT INTO [Booking] ([BookingNr],[Time_From],[Time_TO],[Guests]) VALUES ( /* BookingNr - INT */ 1,/* Time_From - DATETIME */ '2009-7-7 11:00',/* Time_TO - DATETIME */ '2009-7-7 13:00',/* Guests - INT */ 4 ) 
INSERT INTO [Booking] ([BookingNr],[Time_From],[Time_TO],[Guests]) VALUES ( /* BookingNr - INT */ 2,/* Time_From - DATETIME */ '2009-7-7 11:00',/* Time_TO - DATETIME */ '2009-7-7 12:00',/* Guests - INT */ 4 ) 
INSERT INTO [Booking] ([BookingNr],[Time_From],[Time_TO],[Guests]) VALUES ( /* BookingNr - INT */ 3,/* Time_From - DATETIME */ '2009-7-7 12:00',/* Time_TO - DATETIME */ '2009-7-7 13:00',/* Guests - INT */ 4 ) 


INSERT INTO [Table_Booking] ([TableBookingId],[BookingNr],[TableNr], GuestOnTable) VALUES (/* TableBookingId - INT */ 1,    /* BookingNr - INT */ 1,/* TableNr - INT */ 1, 4 ) 
INSERT INTO [Table_Booking] ([TableBookingId],[BookingNr],[TableNr], GuestOnTable) VALUES (/* TableBookingId - INT */ 2,    /* BookingNr - INT */ 2,/* TableNr - INT */ 1, 4 ) 
INSERT INTO [Table_Booking] ([TableBookingId],[BookingNr],[TableNr], GuestOnTable) VALUES (/* TableBookingId - INT */ 3,    /* BookingNr - INT */ 3,/* TableNr - INT */ 1, 4 ) 

GO

Simple test query

select Booking.BookingNr, [Booking].[Time_From], [Booking].[Time_TO], [Booking].[Guests], [Tables].TableNr  ,  
    CASE WHEN [Tables].[Seats] - 
    (  select sum(tbInner.[GuestOnTable]) from [Table_Booking] as tbInner    
     join [Booking] AS bInner on bInner.BookingNr = tbInner.BookingNr
     where (NOT ( Booking.Time_From>= bInner.[Time_To] OR bInner.[Time_From] >= Booking.Time_To ) )
      ) < 0 THEN 'OverBooked' ELSE 'Ok' END AS TableStatus
     from [Booking] 
     join [Table_Booking] on [Booking].[BookingNr] = [Table_Booking].[BookingNr]
     join [Tables] on [Tables].[TableNr] = [Table_Booking].[TableNr]

Gives me: Bookingnr 1 = overbooked Booking 2 and 3 = Ok.

If i remove booking 3. I get the expected result. the problem is when 3 or more bookings share time on the same table.

I don't want to resort to something like doing a loop over all the possible times during the booking and checking if it's overbooked. The query is used frequently, maybe once a minute by all the users, and there can be a couple of hundreds of rows for a day. I can use either SQL or delphi dataset (but i'd rather do it in SQL)

Edit 1

It is a part of a larger stored proc that does all kind of other stuff, so it could be a function.

I would prefer that i would'nt have to deal with min intervals. Most customers using the product doesn't have this problem since they don't allow splittning a table. And i don't want the query to be significantly slower for them.

Answer 1

the trick is to expand out your "Booking" table in a sub query/derrived/CTE, so the is one row per "interval" (hour/half hour/minute/etc), then you can SUM - GROUP BY - HAVING SUM()> limit. You would use a Numbers table to expand out the bookings table.

edit your question with code to build the tables, like:

DECLARE @t table (col1......   )
INSERT into @t values (......

DECLARE @Booking  table(....
INSERT into @Bookings) values (.....

so I can try out a query or two, and I'd try to write it for you...

EDIT

To use my queries below, you need to create this table:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

here are some queries:

--get one row per time "interval" (hour) per booking
SELECT
    b.BookingNr, b.Time_From, b.Time_TO, b.Guests
        ,DATEADD(hh,Number-1,b.Time_From) AS ActualTime
    FROM Booking            b
        INNER JOIN Numbers  n ON DATEADD(hh,Number-1,b.Time_From)<=Time_TO
    ORDER BY 1,5

--get one row per time "interval" (hour), combining each interval
SELECT
    bt.TableNr, SUM(b.Guests) AS TotalGuests
        ,DATEADD(hh,Number-1,b.Time_From) AS ActualTime
    FROM Booking                   b
        INNER JOIN Numbers         n  ON DATEADD(hh,Number-1,b.Time_From)<=Time_TO
        INNER JOIN Table_Booking   bt ON b.BookingNr=bt.BookingNr
    GROUP BY bt.TableNr, DATEADD(hh,Number-1,b.Time_From)
    ORDER BY 3

--get one row per time "interval" (hour), where the seat limit was exceeded
SELECT
    bt.TableNr, SUM(b.Guests) AS TotalGuests
        ,DATEADD(hh,Number-1,b.Time_From) AS ActualTime
        ,t.Seats-SUM(b.Guests) AS SeatsAvailable
    FROM Booking                   b
        INNER JOIN Numbers         n  ON DATEADD(hh,Number-1,b.Time_From)<=Time_TO
        INNER JOIN Table_Booking   bt ON b.BookingNr=bt.BookingNr
        INNER JOIN Tables          t  ON bt.TableNr=t.TableNr
    GROUP BY bt.TableNr,t.Seats, DATEADD(hh,Number-1,b.Time_From)
    HAVING SUM(b.Guests)>t.Seats
    ORDER BY 3

I'm not sure how you want to handle when the seat limit is exceeded. If these queries are not enough, let me know what is needed... Also, what version of SQL Server are you on?