Adding Dates for Each Day of Stay

All we need is an easy explanation of the problem, so here it is.

Let’s say we have a small inn. People make reservations online. I receive a reservation number, the date they will arrive and the length of the stay. I would like not just the number of days of their stay, but the actual calendar dates as well. For example, if my source table looks like this:

1000|5|6/5/2021
1001|3|6/7/2021

Then I would expect the output to be:

1000|6/5/2021
1000|6/6/2021
1000|6/7/2021
1000|6/8/2021
1000|6/9/2021
1001|6/7/2021
1001|6/8/2021
1001|6/9/2021

I’ve been using the following code to generate dates, but I am having trouble joining it back to the original data..

DECLARE @StartDate DATE, @EndDate DATE
SELECT @StartDate = '2021-11-01', @EndDate = '2021-12-01'; 
WITH ListDates(AllDates) AS
(   SELECT @StartDate AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < @EndDate)
SELECT AllDates
FROM ListDates
GO

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You are very close to it .
(Usually, there is a calendar table and used it , instead of the recursive query. You can search on web for an example)

I don’t know if you received a text/string (for the source of your data) or it’s table . In this solution , I used a table (@tv_Source)

Here is one way :

DECLARE @StartDate DATE, @EndDate DATE
DECLARE @tv_Source TABLE
    (
        id int, noDays int,startDate date
    );
INSERT INTO @tv_Source(id,noDays,startDate)
VALUES(1000,5,'6/5/2021'),
     (1001,3,'6/7/2021');

SELECT @StartDate = '2021-06-01', @EndDate = '2021-07-01'; 
WITH ListDates(AllDates) AS
(   SELECT @StartDate AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < @EndDate)

SELECT AllDates,s.id
FROM ListDates as l
 INNER JOIN @tv_Source as s
    ON l.AllDates >=s.startDate 
        AND l.AllDates< DATEADD(dAY,s.noDays,s.startDate)
ORDER BY s.id ASC,l.AllDates ASC
OPTION (MAXRECURSION 0)

output:

AllDates   id
2021-06-05 1000
2021-06-06 1000
2021-06-07 1000
2021-06-08 1000
2021-06-09 1000
2021-06-07 1001
2021-06-08 1001
2021-06-09 1001

Method 2

You can do the following (all the code below is available on the fiddle here):

CREATE TABLE booking
(
  bk_no     INTEGER NOT NULL PRIMARY KEY,
  bk_nights INTEGER NOT NULL CHECK (bk_nights > 0 AND bk_nights < 100), -- 100 arbitrary
  bk_sdt    DATE NOT NULL
);

Populate the table:

INSERT INTO booking
VALUES
(1000, 5, '2021-05-06'),
(1001, 3, '2021-05-06');

And create a new table to hold our booking nights:

CREATE TABLE booking_nights
(
  nt_no    INTEGER NOT NULL,
  bk_no    INTEGER NOT NULL,
  bk_night DATE NOT NULL,
  
  PRIMARY KEY (bk_no, bk_night)
);

Then run the following query – it’s a RECURSIVE CTE:

WITH cte (n, bkno, bknt, bkdt) AS
(
  SELECT
    1,
    bk_no, 
    bk_nights,
    bk_sdt
  FROM booking
  UNION ALL
  SELECT
    n + 1,
    bkno,
    bknt,
    DATEADD(DAY, 1, bkdt)
  FROM cte
  WHERE n < bknt
        
)
INSERT INTO booking_nights
SELECT c.n, c.bkno, c.bkdt 
FROM cte c
ORDER BY c.bkno, c.bkdt;

and then:

SELECT * FROM booking_nights
ORDER BY bk_no, bk_night;

Result:

 nt_no  bk_no   bk_night
     1   1000   2021-05-06
     2   1000   2021-05-07
     3   1000   2021-05-08
     4   1000   2021-05-09
     5   1000   2021-05-10
     1   1001   2021-05-06
     2   1001   2021-05-07
     3   1001   2021-05-08
8 rows

A note on the CHECK constraint part of the table definition, i.e.:

bk_nights INTEGER NOT NULL CHECK (bk_nights > 0 AND bk_nights < 100), -- 100 arbitrary

  • with the use of this constraint in conjuction with the n variable in the main RCTE, the maximum number of iterations will be 100. Not having a limit on this risks going into an infinite loop in the event of some data error!

  • setting OPTION (MAXRECURSION 0); is dangerous, because that brake is deactivated (dbfiddle will eventually give a Run failed error).

    An alternative might be to set MAXRECURSION to something like 100 and not have the constraint in the table definition, but to "drive" without brakes is never advisable. The maximum this can be set to is 32,767 (the highest possible value of a signed 32bit INTEGER). This is discussed in another answer of mine here.

A performance analysis is available here.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply