user3666224 user3666224 - 5 months ago 6
SQL Question

New field with the date of the subsequent row

ID Name Request Date
296459 Waiting 2016-06-04 22:53:46.000
296459 ED-22 2016-06-04 23:01:30.650
296459 CCS-CC28-A 2016-06-05 01:16:00.000
296459 4N-N450-A 2016-06-14 06:33:41.530


I would like to create a new row called End Date which would contain the Request Date from the next row.

ID Name Request Date End Date
296459 Waiting 2016-06-04 22:53:46.000 2016-06-04 23:01:30.650
296459 ED-22 2016-06-04 23:01:30.650 2016-06-05 01:16:00.000
296459 CCS-CC28-A 2016-06-05 01:16:00.000 2016-06-14 06:33:41.530
296459 4N-N450-A 2016-06-14 06:33:41.530 GETDATE()

vkp vkp
Answer

If you are using SQL Server 2012+, use lead to get the values on the next row (based on a specified ordering)

select t.*, 
coalesce(lead(request_date) over(partition by id order by request_date), getdate()) as end_date
from tablename t