# 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
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
FROM ListDates
WHERE AllDates < @EndDate)

SELECT AllDates,s.id
FROM ListDates as l
INNER JOIN @tv_Source as s
ON l.AllDates >=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,
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 🙂