CODEWITHSUNDEEP
sqlsql-server
James
James

Reputation: 1

Updating start time of next row to the end date of the previous row

I have a table:

locid   timestart      timeend
1       Jan 23 2015    Dec-31-9999
1       Feb 15 2015    Dec-31-9999
2       Mar 18 2015    Apr-28-2015
2       Nov 23 2015    Dec-31-9999
2       Jul 23 2015    Nov-23-2015
2       Apr 28 2015    Dec-31-9999

The time end is incorrect in the above table. It should be like below 

locid   timestart      timeend
1       Jan 23 2015    feb-15-2015
1       Feb 15 2015    Dec-31-9999
2       Mar 18 2015    Apr-28-2015
2       Nov 23 2015    Jul-23-2015
2       Jul 23 2015    Nov-23-2015
2       Apr 28 2015    Dec-31-2015

How write SQL to correct the time end from table 1 as shown in table 2?

Upvotes: 0

Views: 176

Answers (1)

Rob Farley
Rob Farley

Reputation: 15849

You want the timestart of the next row:

WITH newTable AS
(SELECT 8, LEAD(timestart,1,'99991231') OVER (PARTITON BY locid ORDER BY timestart) AS newTimeEnd
FROM theTable
)
UPDATE newTable SET timeend = newTimeEnd;

Upvotes: 1

Related Questions