December 7, 2010 at 8:03 pm
There's a very simple way to do this, but I forgot what it was. A good example of the problem is a hotel. I need to find out what rooms are available for a given arrival/departure date, based on existing bookings. Here's the example code:
DECLARE @RoomBookings TABLE(
RoomType varchar(6),
ArrDate DateTime,
DepDate DateTime
)
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/01/2011', '2/3/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/11/2011', '2/14/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/16/2011', '2/20/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/26/2011', '2/28/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/3/2011', '2/5/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/16/2011', '2/24/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/25/2011', '2/26/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/27/2011', '2/28/2011')
--* Find the rooms available for a reservation with arrival/departure of 2/7/2011 - 2/9/2011
SELECT * FROM @RoomBookings WHERE ?????
.
December 7, 2010 at 9:23 pm
DECLARE @RoomBookings TABLE(
RoomType varchar(6),
ArrDate DateTime,
DepDate DateTime
)
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/01/2011', '2/3/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/11/2011', '2/14/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/16/2011', '2/20/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/26/2011', '2/28/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/3/2011', '2/5/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/16/2011', '2/24/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/25/2011', '2/26/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/16/2011', '2/28/2011')
DECLARE @ArrDate datetime,@DepDate datetime
SET @ArrDate = '2/7/2011'
SET @DepDate = '2/9/2011'
SELECT * FROM @RoomBookings
WHERE @ArrDate NOT BETWEEN ArrDate and DepDate
and @DepDate NOT BETWEEN ArrDate and DepDate
December 7, 2010 at 10:06 pm
ngreene (12/7/2010)
DECLARE @RoomBookings TABLE(RoomType varchar(6),
ArrDate DateTime,
DepDate DateTime
)
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/01/2011', '2/3/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/11/2011', '2/14/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/16/2011', '2/20/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/26/2011', '2/28/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/3/2011', '2/5/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/16/2011', '2/24/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/25/2011', '2/26/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/16/2011', '2/28/2011')
DECLARE @ArrDate datetime,@DepDate datetime
SET @ArrDate = '2/7/2011'
SET @DepDate = '2/9/2011'
SELECT * FROM @RoomBookings
WHERE @ArrDate NOT BETWEEN ArrDate and DepDate
and @DepDate NOT BETWEEN ArrDate and DepDate
If you add these entries:
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('103', '2/9/2011', '2/11/2011');
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('103', '2/4/2011', '2/6/2011');
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('104', '2/5/2011', '2/08/2011');
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('104', '2/19/2011', '2/25/2011');
this code then returns rooms that are booked during this time period.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 7, 2010 at 10:09 pm
I think you would be having another table that has a list of all rooms, I have assumed the same to be @Rooms. If you don't have a table like that you can create a table variable and fill it will all types of rooms and then use the query below.
DECLARE@from_date SMALLDATETIME
DECLARE@to_date SMALLDATETIME
SET @from_date = '2/7/2011'
SET @to_date = '2/9/2011'
DECLARE@Rooms TABLE
(
RoomType varchar(6)
)
DECLARE @RoomBookings TABLE
(
RoomType varchar(6),
ArrDate DateTime,
DepDate DateTime
)
INSERT INTO @Rooms( RoomType ) VALUES( '101' )
INSERT INTO @Rooms( RoomType ) VALUES( '102' )
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/01/2011', '2/3/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/11/2011', '2/14/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/16/2011', '2/20/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/26/2011', '2/28/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/3/2011', '2/5/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/16/2011', '2/24/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/25/2011', '2/26/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/27/2011', '2/28/2011')
--* Find the rooms available for a reservation with arrival/departure of 2/7/2011 - 2/9/2011
SELECT*
FROM@Rooms R
WHERENOT EXISTS(
SELECT*
FROM@RoomBookings RB
WHERER.RoomType = RB.RoomType
AND(
RB.ArrDate BETWEEN @from_date AND @to_date OR
RB.DepDate BETWEEN @from_date AND @to_date OR
@from_date BETWEEN RB.ArrDate AND RB.DepDate OR
@to_date BETWEEN RB.ArrDate AND RB.DepDate
)
)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 7, 2010 at 10:26 pm
How's this?
DECLARE @RoomBookings TABLE(
RoomType varchar(6),
ArrDate DateTime,
DepDate DateTime
)
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/01/2011', '2/3/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/11/2011', '2/14/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/16/2011', '2/20/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('101', '2/26/2011', '2/28/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/3/2011', '2/5/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/16/2011', '2/24/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/25/2011', '2/26/2011')
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('102', '2/27/2011', '2/28/2011')
-- add some rooms that are occupied on the specified dates.
-- these should not show up in the results.
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('103', '2/9/2011', '2/11/2011');
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('103', '2/4/2011', '2/6/2011');
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('104', '2/5/2011', '2/08/2011');
INSERT INTO @RoomBookings(RoomType, ArrDate, DepDate) VALUES('104', '2/19/2011', '2/25/2011');
DECLARE @StartDate DATETIME,
@EndDate DATETIME,
@ArrDate DATETIME,
@DepDate DATETIME;
SET @StartDate = '20110207';
SET @EndDate = '20110209';
-- Get the start/end of the month for the dates specified
SET @ArrDate = DateAdd(month, DateDiff(month, 0, @StartDate), 0);
SET @DepDate = DateAdd(day, -1, DateAdd(month, DateDiff(month, -1, @StartDate), 0));
WITH TALLY AS
(
-- You should have your own permanent tally table.
-- If so, omit this CTE.
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.all_columns
), Dates AS
(
-- Get the set of dates between
-- the @ArrDate and @DepDate.
SELECT MyDate = DateAdd(day, N-1, @ArrDate)
FROM TALLY
WHERE N <= DateDiff(day, @ArrDate, @DepDate)+1
), DatesBooked AS
(
-- For each RoomType, get the dates that it's booked
SELECT RoomType, MyDate
FROM @RoomBookings t1
CROSS APPLY (SELECT MyDate
FROM Dates
WHERE MyDate >= t1.ArrDate
AND MyDate < DateAdd(day, 1, t1.DepDate)) t2
), Rooms AS
(
-- Get a set of all of the rooms available
SELECT DISTINCT RoomType
FROM @RoomBookings
)
-- Get the RoomTypes that are not reserved
-- for any of the specified dates
SELECT DISTINCT Rooms.RoomType
FROM Rooms
LEFT JOIN (SELECT RoomType
FROM DatesBooked
WHERE @StartDate = MyDate
OR @EndDate = MyDate) t2
ON t2.RoomType = Rooms.RoomType
WHERE t2.RoomType IS NULL;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 8, 2010 at 8:33 pm
You are absolutely right. I did not ask this question correctly. I need to go back to the drawing board about how to ask this, and the tables / data involved. My apologies to all. I've wasted enough of everyone's time. I'll repost correctly, and I'll find a way to dig through the hundreds of thousands of lines of code to find the answer I'm trying to remember and post that as well. I keep coming across this every year or so, and can never remember the query, or find the example I need.
BTW, I think this is a classic "gaps & islands" problem. I'd never heard of that one before, but I think that's exactly what this is.
.
December 11, 2010 at 9:29 pm
Try this...
--===== Define the desired dates for a reservation
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '2011-02-07',
@EndDate = '2011-02-09';
--===== Find a room type with no reservation
-- for the dates given.
SELECT RoomType
FROM @RoomBookings
EXCEPT
SELECT RoomType
FROM @RoomBookings r1
WHERE @StartDate <= DepDate --Might want to change this to just <
AND @EndDate >= ArrDate
--Jeff Moden
December 11, 2010 at 9:54 pm
I don't have to try it Jeff, that is exactly what I was looking for. I remember it now that I see it. Thank You!! Now I just have to figure out a way to be able to recall this code when I need it again 12 - 18 months from now. 😀
Thanks again. Certainly not the first time you've come through for me!
.
December 11, 2010 at 10:21 pm
Jeff Moden (12/11/2010)
Try this...
I knew there was a simpler way, but I just couldn't figure it out. Thanks Jeff!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 12, 2010 at 10:27 am
BSavoie (12/11/2010)
I don't have to try it Jeff, that is exactly what I was looking for. I remember it now that I see it. Thank You!! Now I just have to figure out a way to be able to recall this code when I need it again 12 - 18 months from now. 😀Thanks again. Certainly not the first time you've come through for me!
You're welcome! If it's any comfort, I never remember it myself. I always have to draw it out on paper. It doesn't take long (5 lines) but I always have to draw it. 😛
--Jeff Moden
December 12, 2010 at 10:28 am
WayneS (12/11/2010)
Jeff Moden (12/11/2010)
Try this...I knew there was a simpler way, but I just couldn't figure it out. Thanks Jeff!
Thanks for the feedback, Wayne. Hmmmm... one "spackle" article coming up. 😛
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply