How to create recursive query to get all dates between two dates

Question

I want to get all dates between two dates arrival date and leaving date.

I tried that recursive query but it didn't work.

;with dates as ( 
SELECT GuestID, ArrivalDate as reserveddate 
from dbo.Guest 
union all 
SELECT GuestID, dateadd (day,1,dbo.Guest. ArrivalDate) as reserveddate 
from dbo.Guest 
where dateadd (day,1,dbo.Guest. ArrivalDate) <dbo.Guest.leavingate
) 

SELECT * 
from dates 
option (maxrecursion 0)

I tried that but it doesn't work. with dates as ( SELECT GuestID, ArrivalDate as reserveddate from dbo.Guest union all SELECT GuestID, dateadd (day,1,dbo.Guest. ArrivalDate) as reserveddate from dbo.Guest where dateadd (day,1,dbo.Guest. ArrivalDate) — Kerols Alfons, Jun 15 '16 at 12:28
You need to join `dates` to `dbo.Guest ` in recursive part of query. — gofr1, Jun 15 '16 at 12:35
it returns the same result also.. union works with first adding and recursion doesn't work — Kerols Alfons, Jun 15 '16 at 12:49
So, what do you need? Show sample of data and what you need to get from it then. — gofr1, Jun 15 '16 at 12:50
the result was 17-6-2016 and 18-6-2016 and stopped and it must increment to 20-6-2016 as the value in leaving date — Kerols Alfons, Jun 15 '16 at 12:52
Please, provide samples, post them in question. Like *I got (1, '2016-01-01', '2016-01-10') I need to get ...* am I clear? :) I provided two query's: first one gives you table with dates, second with GuestIDs and dates. What output exactly do you need? — gofr1, Jun 15 '16 at 12:55
Instead of a recursive cte like this I would use a tally or numbers table. — Sean Lange, Jun 15 '16 at 13:03
the first query works correctly when I use declare but I need the second one.. and I get the first and second values only not all value .. I get 17-6-2016; 18-6-2016. while my data in table [arrival date ] = 17-6-2016 and [leaving date]=20-6-2016 so I need to get 17-6-2016; 18-6-2016; 19-6-2016; 20-6-2016; — Kerols Alfons, Jun 15 '16 at 13:08
I just checked my second query with dummy data (like 17-20 june 2016) it works perfectly fine. Please, check EDIT2 — gofr1, Jun 15 '16 at 13:46
@user3199230 if any of given answers helps be free to accept/upvote them! It is the best way to say thank you here! ;) — gofr1, Jun 15 '16 at 15:33

gofr1 · Accepted Answer · 2016-06-15T13:46:00.507

You need recursive CTE:

DECLARE  @arrival_date date = '2016-01-01',
         @leaving_date date = '2016-02-01'

;WITH cte AS (
SELECT @arrival_date as date_
UNION ALL
SELECT CAST(DATEADD(day,1,date_) as date)
FROM cte
WHERE date_ < @leaving_date
)

SELECT *
FROM cte
OPTION (MAXRECURSION 0)

Output:

date_
2016-01-01
2016-01-02
2016-01-03
...
2016-01-30
2016-01-31
2016-02-01

EDIT1

Based on your sample:

;WITH cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
    ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)

SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)

EDIT2

;WITH Guests AS (
SELECT  1 as GuestID,
        '2016-01-01' ArrivalDate,
        '2016-01-05' LeavingDate
UNION ALL
SELECT  2 ,
        '2016-06-17',
        '2016-06-20'
), cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
    ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)

SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)

Output:

GuestID date_
1   2016-01-01
1   2016-01-02
1   2016-01-03
1   2016-01-04
1   2016-01-05
2   2016-06-17
2   2016-06-18
2   2016-06-19
2   2016-06-20

score 1 · Answer 2 · answered Jun 15 '16 at 13:17

My preference for this type of thing is to use a tally table. I keep a view on every system that looks like this.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

This way I have a tally table at my fingertips at all times. You can read more about tally tables and how they replace loops here. http://www.sqlservercentral.com/articles/T-SQL/62867/

To solve your current problem this becomes fairly simple. First we need a table and some sample data.

create table #Guest
(
    GuestID int identity
    , ArrivalDate date
    , DepartureDate date
)

insert #Guest
select '2016-06-01', '2016-06-15' union all
select '2016-07-01', '2016-07-12'

Here is where the power of the tally table really shows. To produce the output for your challenge is as simple as this code.

SELECT GuestID
    , ArrivalDate 
    , DepartureDate
    , DATEADD(Day, N - 1, ArrivalDate) as EachDate
from #Guest g
join cteTally t on t.N <= DATEDIFF(day, ArrivalDate, DepartureDate) + 1
order by GuestID
    , EachDate

The other big advantage of this type of approach over a recursive cte is that you are using what is known as a triangular join and can cause some performance challenges. Here is some more info on triangular joins. http://www.sqlservercentral.com/articles/T-SQL/61539/

score 0 · Answer 3 · answered Jun 15 '16 at 16:30

Try This

    DECLARE @DateFrom DateTime = DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())-5,0)
    DECLARE @DateTo DateTime = CONVERT(DATE,GETDATE())

    ;WITH Numbers (Number) AS (
         SELECT row_number() OVER (ORDER BY object_id)
         FROM sys.all_objects
    )

    SELECT  dateadd(DAY, number-1, @DateFrom)           
    FROM    Numbers
    WHERE   number <= datediff(DAY, @DateFrom-1, @DateTo-1)

you can change the change the date interval by changing DAY to WEEK, MONTH, YEAR etc. in the select dateadd and the where datediff

How to create recursive query to get all dates between two dates

3 Answers3

Linked