May 2, 2010 at 2:03 pm
Hey guys
I have this huge problem - I'm developing a rental room utility. In the reservation table each room have a list of check-in date and check-out date. it looks something like that:
Room_ID, Check_in_Date, Check_Out_Date
1 5-30-2010 6-4-2010
1 6-10-2010 6-12-2010
1 6-13-2010 6-17-2010
2 5-28-2010 6-1-2010
2 6-7-2010 6-9-2010
2 6-10-2010 6-12-2010
Anyways - I need to find a way to extract the room numbers that will be available during a given date range. E.g: for the range June 5th to June 8th only room #1 is available
Appreciate any help!
May 2, 2010 at 2:42 pm
First I would try to find the blocked rooms and second display the remaining:
Side note: please notice how I posted the sample data o make it easier to work on... 😉
DECLARE @tbl TABLE (Room_ID INT, Check_in_Date DATETIME, Check_Out_Date DATETIME)
INSERT INTO @tbl
SELECT 1 ,'5-30-2010','6-4-2010' UNION ALL
SELECT 1 ,'6-10-2010','6-12-2010' UNION ALL
SELECT 1 ,'6-13-2010','6-17-2010' UNION ALL
SELECT 2 ,'5-28-2010','6-1-2010' UNION ALL
SELECT 2 ,'6-7-2010','6-9-2010' UNION ALL
SELECT 2 ,'6-10-2010','6-12-2010'
DECLARE
@from DATETIME,
@to DATETIME
SET @from = '20100605'
SET @to = '20100608'
;WITH cte_blocked AS
(
SELECT room_id
FROM @tbl
WHERE Check_in_Date <= @to AND Check_Out_Date >=@from
)
SELECT t.room_id
FROM @tbl t
LEFT OUTER JOIN cte_blocked b ON t.room_id=b.room_id
WHERE b.room_id IS NULL
GROUP BY t.room_id
Edit: What would you do if I want to check in and check out on the same day? Do you need to cover that option? If so, you might need to expand your IN/OUT-date to include a time section...
May 2, 2010 at 3:08 pm
Hey - Tnx alot!!!
Also tnx for the side tip 🙂
BTW - suppose I wanted just to check if there is an available room - something like count the number of available rooms?
May 2, 2010 at 3:26 pm
Change the SELECT clause to
SELECT COUNT(DISTINCT t.room_id)
FROM @tbl t
LEFT OUTER JOIN cte_blocked b ON t.room_id=b.room_id
WHERE b.room_id IS NULL
May 2, 2010 at 3:31 pm
You ARE the saver of my day 🙂
Tnx a lot!
May 2, 2010 at 3:37 pm
Assume the following scenario:
You have a new room that's available since the hotel decided to refurniture the clothing store.
This room will never be available since it doesn't show up in the booking table you're referencing to.
You should reference th result of the "cte_blocked" against a "total room list" to avoid such a scenario...
May 2, 2010 at 3:42 pm
Yeh - I see what you mean
Quite true, quite true...
Tnx for the good eye.
May 2, 2010 at 3:45 pm
kobyziv (5/2/2010)
Yeh - I see what you meanQuite true, quite true...
Tnx for the good eye.
You're very welcome! 😀
May 2, 2010 at 9:43 pm
One other way to write Lutz's solution:
DECLARE @tbl TABLE (Room_ID INT, Check_in_Date DATETIME, Check_Out_Date DATETIME)
INSERT INTO @tbl
SELECT 1 ,'20100530','20100604' UNION ALL
SELECT 1 ,'20100610','20100612' UNION ALL
SELECT 1 ,'20100613','20100617' UNION ALL
SELECT 2 ,'20100528','20100601' UNION ALL
SELECT 2 ,'20100607','20100609' UNION ALL
SELECT 2 ,'20100610','20100612';
DECLARE @from DATETIME,
@to DATETIME;
SET @from = '20100605';
SET @to = '20100608';
SELECT T.Room_ID
FROM @tbl T
EXCEPT
SELECT T.Room_ID
FROM @tbl T
WHERE T.Check_in_Date <= @to
AND T.Check_Out_Date >= @from;
May 3, 2010 at 1:57 am
Tnx guys. Your help is very much appreciated!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply