Shohel Shohel - 6 months ago 8
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
Comments