Select All Rooms Available for Given Date Range

Posted by Lacta under Sql Server on 9/8/2012 | Points: 10 | Views : 1238 | Status : [Member] | Replies : 1
I am trying to built a hotel reservation system and I have the following tables:

hotel
id, etc
customer
id, etc

room
room_no, hotelid , type, facilities
101 | 1 etc 101 | 1 etc 101 | 1 etc

booking
id, room_no, customerid, datefrom , dateto
1| 101 | 2 | 03-03-2012 | 06-03-2012 1| 101 | 2 | 07-03-2012 | 12-03-2012

availability
room_no, date
101 | 03-03-2012
101 | 04-03-2012
101 | 05-03-2012
101 | 06-03-2012
101 | 03-04-2012
101 | 04-04-2012
101 | 05-04-2012
101 | 06-04-2012

at the availability table the owner of the hotel will store the dates that the room is available. as you see for example the room 101 is available for the dates 3-6/3/12 and 3-6/4/12. The booking table is the table tha stores the actual booking , when a room is booked it's dates will be deleted from the availability table. Now when a customer wants to search for a room available from 3/4 to 6/4/12 what query can i do to the availability table, to search all dates and see if there is a room for that date range?




Responses

Posted by: Krv on: 11/28/2012 [Member] Starter | Points: 25

Up
0
Down
a. Create a CTE to get the another column which provides the difference between current row date with next row date and have it as availabilityday
b. In the actually query, use where date>=start date and date<=end date and availabilityday=1

Thanks,
R.Vasanth
MCTS(SQL Server & BI)
http://bigdatatech.blogspot.com

Lacta, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response