Shohel Shohel - 10 months ago 39
SQL Question

Find previous and next from current record in SQL Server

I have a big problem to find and next and previous records from current data. I have a data set which is below:

enter image description here

Need a result which is look like this below:

enter image description here

Is it possible? Could anyone help me?

Answer

You can use the LEAD and LAG window functions:

SELECT *,
    PreviousShiftProfileID  = LAG(ShiftProfileID) OVER(PARTITION BY EmployeeID ORDER BY CDate),
    NextShiftProfileID      = LEAD(ShiftProfileID) OVER(PARTITION BY EmployeeID ORDER BY CDate)
FROM yourTable