Add EndDate Column from StartDate Column — T-SQL Sample code


The sample code is from an answer to this question:

http://forums.asp.net/t/1965762.aspx?How+to+write+a+Query+to+get+related+result


create table #table (id int, cid int , startdate datetime)
create table #table (id int, cid int , startdate datetime)
insert #table

select 1, 100, '02/23/2014'
union select 1, 100, '06/25/2013'
union select 1, 100, '06/04/2013'
union select 1, 100, '06/17/2013'
union select 2, 200, '08/9/2013'
union select 2, 200, '08/3/2013'
;with mycte as (
SELECT id,cid,Startdate, row_number() Over(Partition By id,cid order by StartDate) rn
FROM #table)

Select m1.id,m1.cid,m1.StartDate,
Case
When m1.StartDate < getdate() AND m2.StartDate IS NULL Then
 DATEADD(year, DATEDIFF(year,0,m1.StartDate)+1,-1) --Last day of the year
Else Dateadd(Day,-1,m2.StartDate)
ENd as EndDate
FROM mycte m1
Left Join mycte m2 On m1.id=m2.id And m1.cid=m2.cid AND m1.rn=m2.rn-1

Order By m1.id, m1.startdate

/*
id cid StartDate EndDate
1 100 2013-06-04 00:00:00.000 2013-06-16 00:00:00.000
1 100 2013-06-17 00:00:00.000 2013-06-24 00:00:00.000
1 100 2013-06-25 00:00:00.000 2014-02-22 00:00:00.000
1 100 2014-02-23 00:00:00.000 NULL
2 200 2013-08-03 00:00:00.000 2013-08-08 00:00:00.000
2 200 2013-08-09 00:00:00.000 2013-12-31 00:00:00.000
*/

Drop table #table
 
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