LAG & LEAD

--This functions will return columns values from its previous rows in the same result set without the use of a self-join.

DECLARE @Users TABLE( 
                        UserID INT IDENTITY,
                        UserName VARCHAR(20)
                    )

INSERT INTO @Users (UserName)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'


-- OLD METHOD:

SELECT U1.UserID, U1.UserName, U2.UserName AS PreviousUserName
FROM @Users U1
LEFT OUTER JOIN @Users U2 ON U2.UserID = U1.UserID - 1

-- USING LAG()

SELECT UserID,UserName,
LAG(UserName) OVER (ORDER BY UserName) AS PreviousRecordUserName
FROM @Users
ORDER BY UserName


-- LEAD(): This functions will return columns values from subsequent rows in the same result set without the use of a self-join.

DECLARE @Users TABLE( 
UserID INT IDENTITY,
UserName VARCHAR(20)
)

INSERT INTO @Users (UserName)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D'

-- OLD METHOD:

SELECT U1.UserID, U1.UserName, U2.UserName AS PreviousUserName
FROM @Users U1
LEFT OUTER JOIN @Users U2 ON U2.UserID = U1.UserID + 1

-- USING LEAD()

SELECT
UserId,UserName,
LEAD(UserName) OVER (ORDER BY UserName) AS NextUser
FROM @Users
ORDER BY UserName