Generating Month on Month Record using CTE

Posted by Kirthiga under Sql Server category on 6/6/2013 | Points: 40 | Views : 1449

Post Code |

Search Codes |

Code Home

Declare @date datetime='2011-07-30' Declare @period int=12
;with cte as(
select 1 nos,@date Date
union all 
select nos+1
,case when Day(@date)=31 then DATEADD(M,DATEDIFF(M,0,date)+2,0)-1 
	  when Day(@date)=30 and DAY(DATEADD(M,1,date)-DAY(DATEADD(M,1,date)))in(29,28) 
		then (DATEADD(M,DATEDIFF(M,0,date)+2,0)-2) 
	  when Day(@date)=29 and DAY(DATEADD(M,1,date)-DAY(DATEADD(M,1,date)))=28 
		then (DATEADD(M,DATEDIFF(M,0,date)+2,0)-3) 
else DATEADD(M,1,date) end
from cte where nos+1<=@period
)select * from cte

Alert Moderator

Bookmark It

Comments or Responses

Posted by: Jasminej on: 6/6/2013 Level:Starter | Status: [Member] | Points: 10

Hey,

What is the purpose of this script and Can you also please give some description about it ?

Posted by: Kirthiga on: 6/6/2013 Level:Starter | Status: [Member] | Points: 10

Hi Jasminej,

This script is used to generate month on month record based on First Due and Period. It is simple to generate month on month using DateAdd function but for exact Due this script is neccesary.

For the above Script First Due is '2011-07-30' and Period is 12.

The Result must be Due Date on 30th of every month. when it comes on February Due Date on 29 th when we use DateAdd function when coming to March Due Date continues with 29 th. The purpose of this script is to maintain Due Date on 30 th.

Latest Code Snipptes