Date Ranges

• BSavoie

  SSCertifiable

  Points: 6309

  More actions

  December 7, 2010 at 8:03 pm

  #227871

  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 ?????

  .

• ngreene

  SSC-Addicted

  Points: 461

  More actions

  December 7, 2010 at 9:23 pm

  #1259891

  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

• WayneS

  SSC Guru

  Points: 95460

  More actions

  December 7, 2010 at 10:06 pm

  #1259897

  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

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  December 7, 2010 at 10:09 pm

  #1259899

  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

  )

  )

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• WayneS

  SSC Guru

  Points: 95460

  More actions

  December 7, 2010 at 10:26 pm

  #1259905

  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

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• BSavoie

  SSCertifiable

  Points: 6309

  More actions

  December 8, 2010 at 8:33 pm

  #1260408

  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.

  .

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  December 11, 2010 at 9:29 pm

  #1261370

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• BSavoie

  SSCertifiable

  Points: 6309

  More actions

  December 11, 2010 at 9:54 pm

  #1261373

  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!

  .

• WayneS

  SSC Guru

  Points: 95460

  More actions

  December 11, 2010 at 10:21 pm

  #1261379

  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

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  December 12, 2010 at 10:27 am

  #1261432

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  December 12, 2010 at 10:28 am

  #1261433

  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

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)