.png)
LEAD (Transact-SQL)
Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2012. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.
A. 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');
Here is the result set.
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
B. Compare values within partitions
The following example 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;
Here is the result set.
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
C. Specifying arbitrary expressions
The following example demonstrates specifying a variety of arbitrary expressions in the LEAD function syntax.
CREATE TABLE T (a int, b int, c int);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);
SELECT b, c,
LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;
Here is the result set.
b c i ----------- ----------- ----------- 1 -3 8 2 4 2 1 NULL 2 3 1 0 2 NULL NULL 1 5 -2
