We have requirement where 2 months and 2 years are provided as input. And the result should provide a comma separated month-year all the months b开发者_如何学Goetween the from month-year and to month-year.
Say, we have
From Month is 'Jan'
From Year is 2011
To Month is 'Apr'
To Year is 2011
desired output
Jan'11,Feb'11,Mar'11,Apr'11
Can this be achieved using recursive CTE? We need to achieve this using recursive CTE. Could somebody help me on this.
Try this code:
--declaration of variables
declare @frommonth char(3) = 'jan',@fromyear char(4) = 2011,
@tomonth char(3) = 'APR', @toyear char(4) = 2011
declare @output varchar(max)
declare @f int, @t int
select --setting from and to month as months after 1900-01-01
@f = datediff(month, 0, cast('1' +@frommonth+@fromyear as datetime)),
@t = datediff(month, 0, cast('1' +@tomonth+@toyear as datetime))
-- recusive loop
;with cte as
(
select @f m
union all
select m + 1 from cte
where m < @t
)
select @output = coalesce(@output +',', '') +stuff(convert(varchar(11),dateadd(mm, m, 0), 109), 4, 6, '''') FROM CTE
select @output
Result:
Jan'11,Feb'11,Mar'11,Apr'11
Test here:
https://data.stackexchange.com/stackoverflow/q/114801/declaration-of-variables
精彩评论