SQL Server Lag Function

SQL Server LAG function is a Analytics function that provides access to a row at a given physical offset that comes before the current row.

Basically, it is used in a SELECT statement to compare values in the current row with values in a previous row.

SYNTAX

LAG ( scalar_expression [ ,offset ] , [ default ] ) 
OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression
Value to be returned based on the specified offset. It cannot be an analytic function.

offset
The number of rows back from the current row from which to obtain a value.
By default it is 1. offset should be a positive value of type integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

default
Value to return when offset is beyond the scope of the partition. If a default value is not specified, NULL is returned. default must be type-compatible with scalar_expression.

OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause

divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.

order_by_clause

determines the order of the data before the function is applied. When partition_by_clause is specified, it determines the order of the data in each partition. The order_by_clause is required.

Lets see, how does it works in SQL Server, To demonstrate this we create a sample table – SalesTrack which keeps a monthly sales records of products.

Create table SalesTrack 
( SalesId Int identity(1,1) , 
ProductId Int , 
SalesAmount Numeric(9,2),
SalesDate Date)

Now we insert some sales records into SalesTrack table.

Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5001,56743,'2018/01/15');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5001,67463,'2018/02/18');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5001,40000,'2018/03/19');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5001,89000,'2018/04/30');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5001,78000,'2018/05/22');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5001,23500,'2018/06/19');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5001,58800,'2018/07/19');

Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5002,78743,'2018/01/15');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5002,37793,'2018/03/14');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5002,40570,'2018/04/19');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5002,45870,'2018/05/20');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5001,90890,'2018/06/22');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5002,46570,'2018/07/29');
Insert into SalesTrack(ProductId, SalesAmount,SalesDate)
Values (5002,87800,'2018/08/15');

Using Lag Function to compare values between months

Following query uses the Lag function to return the difference in sales amounts for a specific products over previous months.

SELECT ProductId,Month(SalesDate) AS SalesMonth, Year(SalesDate)AS SalesYear, SalesAmount AS SalesAmount,
Lag( SalesAmount , 1) OVER (ORDER BY Month(SalesDate)) AS PreviousMonthSalesAmount 
FROM SalesTrack WHERE ProductId =5001

As we see, there is no lag value available for the first row so the default value null is returned.

We can provide custom value if no lag value available by passing third argument value into Lag() function as given below.

SELECT ProductId,Month(SalesDate) AS SalesMonth, Year(SalesDate)AS SalesYear, SalesAmount AS SalesAmount,
Lag( SalesAmount , 1,0)OVER (ORDER BY Month(SalesDate)) AS PreviousMonthSalesAmount 
FROM SalesTrack WHERE ProductId =5001

Using Lead() Function to compare values within Partition By clause

The following query uses the Lag function to compare  month wise sales between products.

SELECT ProductId,Month(SalesDate) AS SalesMonth, Year(SalesDate)AS SalesYear, 
SalesAmount AS SalesAmount, 
Lag(SalesAmount, 1) OVER(PARTITION BY ProductId ORDER BY Month(SalesDate))
AS NextMonthSalesAmount 
FROM SalesTrack

 

Also Read..

SQL SERVER LEAD()