sql - Problem in Start And End Dates using CTE -
i have below input
id activity date 1 gardening 2011-01-01 00:00:00.000 1 gardening 2011-02-01 00:00:00.000 2 cooking 2011-03-01 00:00:00.000 2 cooking 2011-04-01 00:00:00.000 2 cooking 2011-05-01 00:00:00.000 1 gardening 2011-06-01 00:00:00.000 1 gardening 2011-07-01 00:00:00.000 the ddl under
declare @t table(id int,activity varchar(50),[date] datetime) insert @t select 1,'gardening','01/01/2011' union select 1,'gardening','02/01/2011' union select 2,'cooking','03/01/2011' union select 2,'cooking','04/01/2011' union select 2,'cooking','05/01/2011' union select 1,'gardening','06/01/2011' union select 1,'gardening','07/01/2011' select * @t expected output
id activity initial_date end_date 1 gardening 01/01/2011 02/01/2011 1 gardening 02/01/2011 06/01/2011 1 gardening 06/01/2011 07/01/2011 2 cooking 03/01/2011 04/01/2011 2 cooking 04/01/2011 05/01/2011 so far have done
;with cte as(select rn= row_number() over(order id,[date]),* @t) ,cte2 as( select rn ,id,activity,initialdate =[date],enddate = [date] cte rn =1 union select c1.rn ,c1.id,c1.activity,c1.date,c1.date cte2 c2 join cte c1 on c1.rn = c2.rn+1 ) select id,activity,initialdate,enddate cte2 but output not correct
id activity initialdate enddate 1 gardening 2011-01-01 00:00:00.000 2011-01-01 00:00:00.000 1 gardening 2011-02-01 00:00:00.000 2011-02-01 00:00:00.000 1 gardening 2011-06-01 00:00:00.000 2011-06-01 00:00:00.000 1 gardening 2011-07-01 00:00:00.000 2011-07-01 00:00:00.000 2 cooking 2011-03-01 00:00:00.000 2011-03-01 00:00:00.000 2 cooking 2011-04-01 00:00:00.000 2011-04-01 00:00:00.000 2 cooking 2011-05-01 00:00:00.000 2011-05-01 00:00:00.000 help needed
;with cte ( select *, row_number() over(partition id order [date]) rn @t ) select c1.id, c1.activity, c1.[date] initial_date, c2.[date] end_date cte c1 inner join cte c2 on c1.id = c2.id , c1.rn + 1 = c2.rn order c1.id, c1.[date]
Comments
Post a Comment