Home » SQL Server Lead Function

SQL Server Lead Function

SQL Server LEAD() function is a Analytics function that provides access to a row at a specified physical offset which follows the current row.





SYNTAX

LEAD ( 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 forward 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 Lead() Function to compare values between months

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

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

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

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

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

 

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

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

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

 

Recommended Posts..

SQL SERVER LAG()




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.