SQL - LEAD()

LEAD 

LEAD ( scalar_expression [ ,offset ] , [ default ] )  

    OVER ( [ partition_by_clause ] order_by_clause ) 

scalar_expression
The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.

offset
The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

default
The value to return when offset is beyond the scope of the partition. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. 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. 

Compare Values Between Years

The query uses the LEAD function to return the difference in sales quotas for a specific employee over subsequent years. Notice that because there is no lead value available for the last row, the default of zero (0) is returned.

USE AdventureWorks2012; 

GO 

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,  

    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota 

FROM Sales.SalesPersonQuotaHistory 

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006'); 

BusinessEntityID SalesYear   CurrentQuota          NextQuota 

---------------- ----------- --------------------- --------------------- 

275              2005        367000.00             556000.00 

275              2005        556000.00             502000.00 

275              2006        502000.00             550000.00 

275              2006        550000.00             1429000.00 

275              2006        1429000.00            1324000.00 

275              2006        1324000.00            0.00 

Compare Values Within Partitions

uses the LEAD function to compare year-to-date sales between employees. The PARTITION BY clause is specified to partition the rows in the result set by sales territory. The LEAD function is applied to each partition separately and computation restarts for each partition. The ORDER BY clause specified in the OVER clause orders the rows in each partition before the function is applied. The ORDER BY clause in the SELECT statement orders the rows in the whole result set. Notice that because there is no lead value available for the last row of each partition, the default of zero (0) is returned.

USE AdventureWorks2012; 

GO 

SELECT TerritoryName, BusinessEntityID, SalesYTD,  

       LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales 

FROM Sales.vSalesPerson 

WHERE TerritoryName IN (N'Northwest', N'Canada')  

ORDER BY TerritoryName; 

TerritoryName            BusinessEntityID SalesYTD              NextRepSales 

-----------------------  ---------------- --------------------- --------------------- 

Canada                   282              2604540.7172          1453719.4653 

Canada                   278              1453719.4653          0.00 

Northwest                284              1576562.1966          1573012.9383 

Northwest                283              1573012.9383          1352577.1325 

Northwest                280              1352577.1325          0.00