问题描述:

I need to create a temp table or common table expression based on 2 paremters in a SQL Server 2012 environment

@calYear

@currentYear

so if @calYear = 5 and @currentYear='2014'

I would like to generate a temp table of 5 years starting from current year with 4 columns like

YearDesc MonthName MonthNum FirstDayOfMonth

2014 Jan 1 1/1/2014

2014 Feb 2 2/1/2014

2014 Mar 3 3/1/2014

...

...

...

2018 Oct 10 10/1/2018

2018 Nov 11 11/1/2018

2018 Dec 12 12/1/2018

Is it possible to do a Do While loop efficently? How would I account for the month names?

I'm using a really cumbersome Do While loop to iterate all the months of the year then iterate all the years.

网友答案:

One way using a recursive cte:

declare @calYear int = 5, @currentYear char(4) = '2014'

;with cte (dt) as (
    select DATEFROMPARTS(@currentyear,1,1) dt
    union all
    select dateadd(month,1,dt) 
    from cte where dt < dateadd(year,@calyear,DATEFROMPARTS(@currentyear,1,1))
    )

select year(dt) YearDesc, datename(month, dt) MonthName, month(dt) MonthNum, dt FirstDayOfMonth 
from cte
order by dt 

Or using a numbers table: (in this case master..spt_values)

declare @calYear int = 5, @currentYear char(4) = '2014'

;with cte2 (dt) as (
    select dateadd(month,number,DATEFROMPARTS(@currentyear,1,1)) dt
    from master..spt_values where type = 'p'
    and number <= 12*@calYear
    )
select year(dt) YearDesc, datename(month, dt) MonthName, month(dt) MonthNum, dt FirstDayOfMonth 
from cte2
order by dt 
相关阅读:
Top