Get period of Year and Month Based on SQL query

Question

Hi, Lets say I have year start and year end with the period of month. (Given in sample DDL)

How can I write SQL query from the temp table and get month start and year like below shown in the expected output?

Expected Output:

SQL Version:

Sample DDL:

INSERT INTO #TempParameters (year_start, year_end, period)
VALUES (2021, 2023, 5); -- Change period to 5

Answer 1

Check this solution:

;
with Q1 as 
(
    select year_start as y, year_end, period
    from  #TempParameters
    union all
    select y + 1, year_end, period
    from Q1
    where y + 1 <= year_end
),
Q2 as
(
    select y, 1 as m1, case when period > 12 then 12 else period end m2, period
    from  Q1
    union all
    select y, m2 + 1, case when m2 + period > 12 then 12 else m2 + period end m2, period
    from Q2
    where m2 < 12
)
select m1 as month_start, m2 as month_end, y as [year]
from Q2
order by [year], month_start
option (maxrecursion 0)