SQL date Question

[aadesign]

I really hope someone can help me with this one.

It is a Hotel Booking System:

Tbl Room (RoomID, Floor, Notes)
Tbl BookingsRoom (RoomID, BookingID)
Tbl Bookings (BookingsID, StartDate, EndDate, Comments)

I have to create an SQL select statement for checking if a room is bookable during a given time period.

I tried…

SELECT *
FROM Room
WHERE RoomID = ‘111’
AND NOT EXISTS
(SELECT… now I am stuck how do I do with dates in different columns I can’t do BETWEEN or can I…

[seslie]

Hello aadesi

Now, you want to select a room that isnt booked between particular dates?

SELECT * 
FROM TBLBOOKINGSROOM
WHERE ROOM_ID = '111'
AND STARTDATE IN ('07/05/2012') //this would depend on a variable
AND ENDDATE IN ('07/05/2012')//this would depend on a variable

Between can also be used but try this out. Then based on the result from the query, you can decide whether to insert a record or print out an error message.

[aadesign]

Thank you!!
I was not shure if BETWEEN can be used because start date and enddate are not in the same column, they are two different attributes.

[seslie]

oh, ok, i see what you mean. I dont think between can be used in the way you are asking. For e.g

WHERE checkdate BETWEEN startdate AND enddate.

There are several other ways to write the query i gave above tho... and there's a certain way between could be used also. Try googling to find out more.

Regards

[adam_k]

I see a problem with seslie's script.
If for example you had a reservation for room 111 for period starting 1st of May and ending 10 of May, then the seslie's script would return it as available.
For this particular script to work you need to insert a record in TBLBOOKINGSROOM for each and every day that you are booking (which kind of bits the point of having startdate and enddate and is bad practise due to space and resources being wasted).

Instead you should use <= and >= to evaluate both startdate and enddate as so (assuming I'm checking room 111 for period 5th to 7th of May):

SELECT * FROM TBLBOOKINGSROOM 
WHERE ROOM_ID = '111' 
AND ( STARTDATE <=  '5/5/2012' AND ENDDATE >=  '5/5/2012' --Greek date format, change it to your match yours
OR STARTDATE <= '7/5/2012' AND ENDDATE >= '7/5/2012' 
OR STARTDATE BETWEEN '5/5/2012' AND '7/5/2012' 
OR ENDDATE BETWEEN '5/5/2012' AND '7/5/2012') 

This would get
A) Bookings that start before your checking period and end after your checking period
B) Booking that start in your checking perio and don't end until after it
C) Bookings that start or end or both in your checking period.

PS: I haven't tested it, so there might be errors.