Add Sequence a Dataset with Datetime Columns


  

 --Create table #MonthProcess  (ID numeric, MonthStartDate  datetime, LastMonthDate  datetime, Month_Code numeric, Process_Code char(2), Remarks char(2))

Create table #MonthProcess  (ID numeric, MonthStartDate  datetime
, LastMonthDate  datetime, Month_Code int, Process_Code char(2), Remarks char(4))

Declare @MonthStartDate datetime = '01/01/2016'
Declare @MonthEnd datetime = '01/01/2017'

Declare @StartID numeric = 80


;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n)-1 FROM Num2)

 Insert into #MonthProcess (ID,MonthStartDate,LastMonthDate, Month_Code,Process_Code,Remarks)

Select  n+@StartID ID, dateadd(month,n,@MonthStartDate) MonthStartDate
, EOMONTH(dateadd(month,n,@MonthStartDate)) LastMonthDate
--,Dateadd(day,-1,dateadd(month,n+1,@MonthStartDate))  LastMonthDate
,12 - n%12 as Month_Code
, 'PR' as Process_Code, 'STRD' as Remarks  
  
FROM  Nums
WHERE dateadd(month,n-1,@MonthStartDate)<@MonthEnd


--check data
Select * from #MonthProcess

--clean up
drop table #MonthProcess
 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8287987e-116b-4e3a-a184-4bec8a6b16e4/help-in-increment-query?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s