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.
| 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/
|
|
|
|
|
|
|
Smile Select All Rooms Available for Given Date Ra