Room reservations, checking available vacancies/schedule

Quite often questions arise on what is the best way to find out if you have vacant rooms in a hotel, or in your office meeting rooms etc.

Here is a straightforward example that you can use and adapt to your own scenario.

What you need to do to check if the rooms are vacant is to see if the times you need overlap. They won't overlap when both the start and end time of a reservation is before the time you need. They also won't overlap when both the start and end time of a reservation is after the time you need. So now you need to find which rooms have all bookings before or after the time you need. In other words, the time you want is vacant.

After we figure that out we will then do a join with the table containing the names of the rooms. This part isn't necessary if you don't have specific room names. If you merely refer to them as room #1 or room #2 then the rooms table isn't needed nor is the last query which will join on the room names.

Your schedule table should not contain the name of the room, it is already stored in the room table, so you can obtain that information with a join.

Now to get the rooms available, lets say your wanted time is between 11:00 and 11:30 today.

The case statement says that if the times of the booking for the room in that particular row is before your start time and after your end time then assign a value of 0 otherwise assign a value of 1 (meaning it would be occupied).

The sum clause ads up all the values for every row for that room, which of course you are hoping is 0 for that room. If it is it will show as available. You can then choose that room (or any one of the rooms that show as available).

Now of course you probably want to tell people what room they are in and not room#2 (otherwise you wouldn't have names right).