Find free room (booking system)

Question

For a booking system I have a table with rooms, arrival and departure.

Example data:

id | room | arrival    | departure
---+------+------------+-----------
1  | 1    | 2011-03-12 | 2011-03-14
2  | 1    | 2011-03-08 | 2011-03-09
3  | 1    | 2011-03-19 | 2011-03-20
4  | 2    | 2011-03-22 | 2011-03-30 
5  | 2    | 2011-03-19 | 2011-03-22

My question is now: if I have a new booking (2011-03-10 to 2011-03-12), how can I check which rooms are free?

Output for this example should be room 1 and 2.

Answer 1

You could also use the BETWEEN comparison operator for this purpose. In this case you would do something like this:

SELECT r.id FROM room r WHERE r.id NOT IN
(
    SELECT rb.room FROM room_booking rb WHERE 
        ('2011-03-10' BETWEEN rb.arrival AND rb.departure) OR 
        ('2011-03-12' BETWEEN rb.arrival AND rb.departure)
)

Answer 2

Here is a query that will show the NOT-FREE rooms for a date span:

select room from bookings where
(arrival<'2011-03-12' and departure>='2011-03-12') -- overlap at the end
OR (arrival<='2011-03-10' and departure>'2011-03-10') -- overlap at the start
OR (arrival>='2011-03-10' and departure<='2011-03-12') -- complete overlap

You can use this with

select roomnumber from rooms where roomnumber not in (... as above ...)

to find the FREE rooms