Lead and Lag functions in SQL Server 2012

In this video we will discuss about Lead and Lag functions.

Lead and Lag functions
Introduced in SQL Server 2012
Lead function is used to access subsequent row data along with current row data
Lag function is used to access previous row data along with current row data
ORDER BY clause is required
PARTITION BY clause is optional

Syntax
LEAD(Column_Name, Offset, Default_Value) OVER (ORDER BY Col1, Col2, …)
LAG(Column_Name, Offset, Default_Value) OVER (ORDER BY Col1, Col2, …)

Offset – Number of rows to lead or lag.
Default_Value – The default value to return if the number of rows to lead or lag goes beyond first row or last row in a table or partition. If default value is not specified NULL is returned.

We will use the following Employees table for the examples in this video

SQL Script to create the Employees table
Create Table Employees
(
Id int primary key,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)
Go

Insert Into Employees Values (1, ‘Mark’, ‘Male’, 1000)
Insert Into Employees Values (2, ‘John’, ‘Male’, 2000)
Insert Into Employees Values (3, ‘Pam’, ‘Female’, 3000)
Insert Into Employees Values (4, ‘Sara’, ‘Female’, 4000)
Insert Into Employees Values (5, ‘Todd’, ‘Male’, 5000)
Insert Into Employees Values (6, ‘Mary’, ‘Female’, 6000)
Insert Into Employees Values (7, ‘Ben’, ‘Male’, 7000)
Insert Into Employees Values (8, ‘Jodi’, ‘Female’, 8000)
Insert Into Employees Values (9, ‘Tom’, ‘Male’, 9000)
Insert Into Employees Values (10, ‘Ron’, ‘Male’, 9500)
Go

Lead and Lag functions example WITHOUT partitions : This example Leads 2 rows and Lags 1 row from the current row.

When you are on the first row, LEAD(Salary, 2, -1) allows you to move forward 2 rows and retrieve the salary from the 3rd row.
When you are on the first row, LAG(Salary, 1, -1) allows us to move backward 1 row. Since there no rows beyond row 1, Lag function in this case returns the default value -1.

When you are on the last row, LEAD(Salary, 2, -1) allows you to move forward 2 rows. Since there no rows beyond the last row 1, Lead function in this case returns the default value -1.
When you are on the last row, LAG(Salary, 1, -1) allows us to move backward 1 row and retrieve the salary from the previous row.

SELECT Name, Gender, Salary,
LEAD(Salary, 2, -1) OVER (ORDER BY Salary) AS Lead_2,
LAG(Salary, 1, -1) OVER (ORDER BY Salary) AS Lag_1
FROM Employees

Lead and Lag functions example WITH partitions : Notice that in this example, Lead and Lag functions return default value if the number of rows to lead or lag goes beyond first row or last row in the partition.

SELECT Name, Gender, Salary,
LEAD(Salary, 2, -1) OVER (PARTITION By Gender ORDER BY Salary) AS Lead_2,
LAG(Salary, 1, -1) OVER (PARTITION By Gender ORDER BY Salary) AS Lag_1
FROM Employees

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/10/lead-and-lag-functions-in-sql-server.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/10/lead-and-lag-functions-in-sql-server_5.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists