Lead and Lag functions in SQL Server 2012

 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