Tuesday 27 August 2013

Getting RefToDate of the Previous Row to become the RefFromDate of the next Row TSQL

Getting RefToDate of the Previous Row to become the RefFromDate of the
next Row TSQL

I have a table with data on the below format:
ProjID ProjName RefDate
-------- ----------- ----------
1 A 08/02/2013
1 A 08/03/2013
1 A 08/15/2013
2 B 08/02/2013
2 B 08/03/2013
2 B 08/15/2013
2 B 08/20/2013
I want a resultset that looks like below:
ProjID ProjName StartDate EndDate
-------- ----------- ---------- ------------
1 A 08/02/2013 08/02/2013
1 A 08/02/2013 08/03/2013
1 A 08/03/2013 08/15/2013
2 B 08/02/2013 08/02/2013
2 B 08/02/2013 08/16/2013
2 B 08/16/2013 08/20/2013
2 B 08/20/2013 08/22/2013
The StartDate is copied from the refdate of the previous row.
How to have a TSQL statement to come up with the resultset mentioned
above? I can do an iteration but it's not the optimal way to do it IMHO.

No comments:

Post a Comment