Ramkumar Nagarajan Ramkumar Nagarajan - 7 months ago 12
SQL Question

Add 1 month to the date value

how to Add 1 month to the date value(1st row) and use it as the input to the next row to add the month till it reaches the maximum month id.

declare @tmp table (date date,month_id int);
insert into @tmp values('2014-11-30',1),('2014-11-30',2),('2014-11-30',3),('2014-11-30',4),('2014-11-30',5),('2014-11-30',6),('2015-01-01',1),('2015-01-01',2),('2015-01-01',3),('2015-01-01',4);


Result Set

Expected output:

DATE month_id derived_date1
2014-11-30 1 2014-12-30
2014-11-30 2 2015-01-30
2014-11-30 3 2015-02-28
2014-11-30 4 2015-03-28
2014-11-30 5 2015-04-28
2014-11-30 6 2015-05-28
2015-01-01 1 2015-02-01
2015-01-01 2 2015-03-01
2015-01-01 3 2015-04-01
2015-01-01 4 2015-05-01

Answer

Recursive CTE

declare @tmp table (date date,month_id int);
insert into @tmp values('2014-11-30',1),('2014-11-30',2),('2014-11-30',3),('2014-11-30',4),('2014-11-30',5),('2014-11-30',6),('2015-01-01',1),('2015-01-01',2),('2015-01-01',3),('2015-01-01',4);

;with cte as
(
    select date, month_id, DATEADD(MONTH, 1, date) as derived_date1 from @tmp where month_id = 1
    union all select t.date, t.month_id, DATEADD(MONTH, 1, cte.derived_date1) from cte inner join @tmp t on cte.date = t.date and cte.month_id = t.month_id - 1
)
select * from cte order by date, month_id
Comments