开发者

Getting comma separated month for a given from month and to month using recursive CTE

开发者 https://www.devze.com 2023-04-11 19:20 出处:网络
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-

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

0

精彩评论

暂无评论...
验证码 换一张
取 消