September 8, 2012 at 3:49 pm
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?
September 8, 2012 at 4:09 pm
You may want to look at:
http://www.sqlservercentral.com/Forums/Topic267061-176-2.aspx#bm1135582
It is the so called Island and Gaps solution.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply