Help with displaying information not booked on a certain date.

[simba]

Hi,

I am doing an online booking project and was wondering if anyone has any ideas on how I can display rooms that are not booked on a certain date. The query that I have that displays all rooms that are not booked is:

SELECT rooms.roomid, rooms.rtype
FROM rooms
LEFT JOIN reservation
ON rooms.roomid = reservation.roomid
WHERE rooms.hostelid = 6
AND (((reservation.roomid) Is Null));

But I want to edit this to show all rooms that are not booked on a certain date say 05-JAN-07. I have tried the following query to try make this work:

SELECT rooms.roomid, rooms.rtype
FROM rooms
LEFT JOIN reservation
ON rooms.roomid = reservation.roomid
WHERE rooms.hostelid = 6
AND (((reservation.roomid) Is Null) and ((reservation.fromdate) !='05-JAN-07')));

Any advice on where I am goin wrong would be great!

thanks

[iburyak]

Try to remove this line :

AND (((reservation.roomid) Is Null)

[simba]

No that doesn't work because it just returns reservations that have been made rather than available rooms.

Thanks though!

[iburyak]

Change to >= instead of !=

((reservation.fromdate) >='05-JAN-07')));

[simba]

That doesn't work either sorry. My reservation table contains bookings that have been made but i'm trying to search for rooms that are available and not on the dates that the rooms are booked up on.

[iburyak]

You probably need a third table that has all dates in a calendar and join to that table to find available once.

[almaz]

You probably need a third table that has all dates in a calendar and join to that table to find available once.

Not at all. There is a much simpler way (by the way, it is suggested to use parametrized queries, and also the most correct way of setting datetime value is to use 8-digit string as I show in my sample):

DECLARE @hostelid int 
DECLARE @fromdate datetime 
DECLARE @todate datetime 
 
SET @hostelid = 6
SET @fromdate = '20070105'
SET @todate = '20070110'
 
SELECT rooms.roomid, rooms.rtype
FROM rooms
WHERE rooms.hostelid = @hostelid
    AND rooms.roomid NOT IN 
    (
        SELECT reservation.roomid
        FROM reservation
        WHERE reservation.fromdate < @fromdate and reservation.todate > @todate
    )

[simba]

Thats excellent, worked a treat! thanks! I have one other wee question...I dont suppose you have any idea how to automatically delete an entry from a table once its todate has expired i.e. the date on which people will be checking out of their hotel?

Thanks again!

[almaz]

Do you mean expired reservations? I would leave them just to be able to see the reservation history (and, create reports to see reservation trends), but if you want to remove old records - you can set up an SQL job that will run every midnight and perform database maintenance like deleting or archiving old records.

[simba]

Yeah I mean reservations. Your completely right tho I dont know what I was thinking wanting to delete them! My head was all confused and I was thinking that I had to delete them so that they didnt mess with my query that shows the availability of rooms on certain dates but it will make no difference because once the check out date has passed it will be in-line with the real date i.e. todays date, which people cannot book prior to todays date anyway!

Thanks again for the help!