Get period of Year and Month Based on SQL query

jn93 651 Reputation points
2023-08-01T14:54:41.66+00:00

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:

User's image

SQL Version:

User's image

Sample DDL:

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,312 questions
{count} votes

Accepted answer
  1. Viorel 110.1K Reputation points
    2023-08-01T17:20:51.5233333+00:00

    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)
    
    0 comments No comments

0 additional answers

Sort by: Most helpful