user3666224 user3666224 - 6 months ago 11
SQL Question

Create a start and stop date based on location from multiple rows in SQL

MRN LocationGUID transferdate
123 209300610 2016-05-16 00:51:00.000
123 209100610 2016-05-17 13:58:32.000
123 148900610 2016-05-20 23:55:28.000
456 853698742 2016-05-01 08:15:00.000
456 951357782 2016-05-03 14:32:28.000


My table looks like the one above, what I need to do is make it look like the below

MRN locationGUID Transferdate Transferstop
123 209300610 2016-05-16 00:51:00.000 2016-05-17 13:58:32.000
123 209100610 2016-05-17 13:58:32.000 2016-05-20 23:55:28.000
123 148900610 2016-05-20 23:55:28.000 GETDATE()
456 853698742 2016-05-01 08:15:00.000 2016-05-03 14:32:28.000

Answer

If you are on SQL Server 2012+, then you can use LEAD window function:

SELECT LocationGUID, 
       CAST(Transferdate AS DATE) AS Transferdate, 
       CAST(COALESCE(LEAD(transferdate) OVER 
                     (ORDER BY transferdate), GETDATE()) AS DATE) AS Transferstop
FROM mytable

Demo here

Note: If you want to keep time then you can simply omit the CAST to DATE from the query.