CODEWITHSUNDEEP
sqlsql-serverdatedatetimerecursive-query
CMJ
CMJ

Reputation: 131

Get an interval of dates from a range of dates

I have two dates 21/10/2019 and 26/6/2031, and from these two I need a list of dates with three months interval starting from the first date like this:

22/10/2019 | 21/01/2020
22/01/2020 | 21/04/2020
22/04/2020 | 21/07/2020
22/07/2020 | 21/10/2020 
...
22/01/2031 | 21/04/2031
22/04/2031 | 26/06/2031

I've tried using ROW_NUMBER() and DENSE_RANK() and LAG() to group a complete list of dates between the two dates, but I can't seem to figure it out. I think I might need to partition this somehow, but I can't get it right.

If you don't understand, please let me know. I'm pretty new at this :)

Upvotes: 0

Views: 546

Answers (2)

SteveC
SteveC

Reputation: 6015

This could also be done using a 'tally' or numbers based approach. The upper limit of the tally_cte is 12^5=248,832 (more than recursion can produce and it could be increased as much as needed).

declare
  @start_dt         datetime='2019-10-21',
  @end_dt           datetime='2031-06-26'

;with
n(n) as (select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(n)),
tally_cte(n) as (
     select row_number() over (order by (select null)) 
     from n n1 cross join n n2 cross join n n3 cross join n n4 cross join n n5)
select t.*, cast(dt.dt as date) start_dt, cast(dateadd(MONTH, 3, dt.dt) as date) end_dt
from tally_cte t
     cross apply  (select dateadd(month, (n-1)*3, @start_dt) dt) dt 
where n<datediff(month, @start_dt, @end_dt)/3;


N   start_dt    end_dt
1   2019-10-21  2020-01-21
2   2020-01-21  2020-04-21
3   2020-04-21  2020-07-21
...
45  2030-10-21  2031-01-21

Upvotes: 0

GMB
GMB

Reputation: 222402

You can use a recursive query:

with cte (dt, end_dt) as (
    select @start_dt, @end_dt
    union all
    select dateadd(month, 3, dt), end_dt from cte where dt < end_dt
)
select dt, 
    case when dateadd(month, 3, dt) < end_dt 
        then dateadd(day, -1, dateadd(month, 3, dt)) 
        else end_dt 
    end as end_dt
from cte
order by dt;

If you need to generate more than 100 quarters, you need to add option (maxrecursion 0) at the very end of the query.

Demo on DB Fiddle

Upvotes: 1

Related Questions