Smile Select All Rooms Available for Given Date Ra

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Smile Select All Rooms Available for Given Date Ra

Author

Topic

lacta
Starting Member

3 Posts

Posted - 2012-09-08 : 17:48:30

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 12:37:26

you need a calendar table or tally table for that


SELECT r.room_no
FROM dbo.CalendarTable('2012-04-03','2012-04-06',0,0)c
CROSS JOIN room r
LEFT JOIN availability a
ON a.[date] = c.[date]
AND a.room_no = r.room_no
GROUP BY r.room_no
HAVING SUM(CASE WHEN a.room_no IS NULL THEN 1 ELSE 0 END) =0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lacta
Starting Member

3 Posts

Posted - 2012-09-09 : 16:39:01

quote:
Originally posted by visakh16

you need a calendar table or tally table for that

SELECT r.room_no
FROM dbo.CalendarTable('2012-04-03','2012-04-06',0,0)c
CROSS JOIN room r
LEFT JOIN availability a
ON a.[date] = c.[date]
AND a.room_no = r.room_no
GROUP BY r.room_no
HAVING SUM(CASE WHEN a.room_no IS NULL THEN 1 ELSE 0 END) =0

thank you for the reply. can yoou explain me what the calendar table will do? i have one calendar_table where i insert all the dates of the current year and if it is weekday or holiday , does this do ?
do i need a room_no column there?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 23:15:17

quote:
Originally posted by lacta

quote:
Originally posted by visakh16

you need a calendar table or tally table for that

SELECT r.room_no
FROM dbo.CalendarTable('2012-04-03','2012-04-06',0,0)c
CROSS JOIN room r
LEFT JOIN availability a
ON a.[date] = c.[date]
AND a.room_no = r.room_no
GROUP BY r.room_no
HAVING SUM(CASE WHEN a.room_no IS NULL THEN 1 ELSE 0 END) =0

thank you for the reply. can yoou explain me what the calendar table will do? i have one calendar_table where i insert all the dates of the current year and if it is weekday or holiday , does this do ?
do i need a room_no column there?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yep that would do

just replace dbo.Calendartable with your tablename


SELECT r.room_no
FROM YourCalendarTable c
CROSS JOIN room r
LEFT JOIN availability a
ON a.[date] = c.[date]
AND a.room_no = r.room_no
WHERE c.date >='2012-04-03' AND c.date<'2012-04-07'
GROUP BY r.room_no
HAVING SUM(CASE WHEN a.room_no IS NULL THEN 1 ELSE 0 END) =0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lacta
Starting Member

3 Posts

Posted - 2012-09-11 : 04:18:08

thank you . it worked !! perfect.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 11:12:59

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources