CODEWITHSUNDEEP
sqlsql-server-2008
Nafisa Afsana Taskia
Nafisa Afsana Taskia

Reputation: 79

Split dates into quarters based on start and end date

I want to split quarters based on a given start and end date. I have the following table:

table1

ID start_date end_date No. of Quarters
1 01-01-2017 01-01-2018 4
2 01-04-2017 01-10-2018 7

So the result table should be have dates split based on number of quarters and end date. The result table should look like:

table2

ID Quarterly Start Date
1 01-01-2017
1 01-04-2017
1 01-07-2017
1 01-10-2017
2 01-04-2017
2 01-07-2017
2 01-10-2017
2 01-01-2018
2 01-04-2018
2 01-07-2018
2 01-10-2018

I found a solution on stackoverflow which states

declare @startDate datetime
declare @endDate datetime

select
    @startDate= ET.start_date,
    @endDate= ET.end_date    
from
    table1

;With cte
As
( Select @startDate date1
Union All
Select DateAdd(Month,3,date1)   From cte where date1 < @endDate 
) select cast(cast( Year(date1)*10000 + MONTH(date1)*100 + 1 as 
varchar(255)) as date) quarterlyDates From cte

Since I am new to sql, I am having troubles customizing it to my problem. Could anyone please recommend a way? Thanks!

Upvotes: 0

Views: 1040

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If I understand correctly, the recursive CTE would look like:

with cte as (
      select id, start_date, num_quarters
      from t
      union all
      select id, dateadd(month, 3, start_date), num_quarters - 1
      from cte
      where num_quarters > 1
     )
select *
from cte;

Here is a db<>fiddle.

Upvotes: 1

Related Questions