Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Available rooms query for room reservation system

I am trying to create an online room reservation system for a small hotel. One of the tables of the database (using...

MS Access in the development phase) is supposed to hold the bookings. It has an autonumber field, customer data fields, two date fields for arrival and departure, and a number field for the number of rooms booked.

A search page submits the arrival and departure dates to a result page which is then supposed to tell the customer how many rooms are available within the period if any. This is where it all goes wrong. I just can't get an accurate count of the number of rooms already booked within the period requested. My original query was:

SELECT Sum(RoomsBooked) AS Total
  FROM bookings
 WHERE arrival >= #arrivalDate# 
   AND departure <= #departureDate#

Several versions of operators BETWEEN, OR etc. have availed nothing usable. I get numbers or null, but nothing that can be used for any practical purpose. Is the table for nothing or is the SQL all wrong???

Your query gets the number of rooms already booked, but only if the booking is entirely within the date range specified. We need to be aware of rooms previously booked where the booking period overlaps or is entirely contained within the date range specified. Also, we need to incorporate into the query the total number of rooms in the hotel, so that we can subtract the number of rooms booked and come up with the number available.

Let's examine some sample data to fully understand the problem.

guest    arrive      depart    booked
Smith  2002-06-11  2002-06-18    1
Jones  2002-06-12  2002-06-14    2
Brown  2002-06-13  2002-06-16    1
White  2002-06-15  2002-06-17    2

If the hotel has 9 rooms, here is a day-by-day listing of the number of available rooms.

 date      available  
2002-06-10     9       Hotel is empty
2002-06-11     8       Smith checks in
2002-06-12     6       Jones checks in
2002-06-13     5       Brown checks in
2002-06-14     7       Jones checks out
2002-06-15     5       White checks in
2002-06-16     6       Brown checks out
2002-06-17     8       White checks out
2002-06-18     9       Smith checks out

If we want to see if 6 rooms are available between the 14th and the 17th, we can't just look at those two dates. To confirm availability, we somehow have to find, for every day in the date range, how many rooms are actually booked on each day.

This is possible only if we have some way of generating the actual individual dates within the date range. Then we can do a SUM() of booked rooms, and GROUP BY on the date.

My favourite means of generating a range of numbers is to employ my handy-dandy integer table. (I am probably not the only one to invent this table, but I did invent it on my own, which is why I call it "my" integer table.)

create table integers 
     (i integer);
insert into integers values (1);
insert into integers values (2);
insert into integers values (3);
insert into integers values (4);
insert into integers values (5);

Joining to the integer table allows a specific range to be selected. In the following query, the MS Access DATEADD function is used to create a range of dates between 2002-06-10 and 2002-06-18. Each guest booking is tested to see if it overlaps that date. The number of rooms booked is summed and subtracted from 9, the total number available.

select dateadd('d',i,#2002-06-09#) as thedate
     , 9-sum(rooms)  as available
  from guests, integers
 where i between 1 and 9
   and arrive <= Dateadd('d',i,#2002-06-09#)
   and depart  > Dateadd('d',i,#2002-06-09#)
group by i

This query returns the following results --

 thedate   available
2002-06-11     8
2002-06-12     6
2002-06-13     5
2002-06-14     7
2002-06-15     5
2002-06-16     6
2002-06-17     8

Note that there are no guests booked on the 10th or 18th, so no rows are returned for these dates (because it's an inner join). This means that all rooms are available.

Dig Deeper on Oracle and SQL