LAG (Transact-SQL)
Accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2012. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.
A. Compare values between years
The following example uses the LAG function to return the difference in sales quotas for a specific employee over previous years. Notice that because there is no lag value available for the first row, the default of zero (0) is returned.
USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');
Here is the result set.
BusinessEntityID SalesYear CurrentQuota PreviousQuota ---------------- ----------- --------------------- --------------------- 275 2005 367000.00 0.00 275 2005 556000.00 367000.00 275 2006 502000.00 556000.00 275 2006 550000.00 502000.00 275 2006 1429000.00 550000.00 275 2006 1324000.00 1429000.00
B. Compare values within partitions
The following example uses the LAG function to compare year-to-date sales between employees. The PARTITION BY clause is specified to divide the rows in the result set by sales territory. The LAG function is applied to each partition separately and computation restarts for each partition. The ORDER BY clause in the OVER clause orders the rows in each partition. The ORDER BY clause in the SELECT statement sorts the rows in the whole result set. Notice that because there is no lag value available for the first row of each partition, the default of zero (0) is returned.
USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD,
LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada')
ORDER BY TerritoryName;
Here is the result set.
TerritoryName BusinessEntityID SalesYTD PrevRepSales ----------------------- ---------------- --------------------- --------------------- Canada 282 2604540.7172 0.00 Canada 278 1453719.4653 2604540.7172 Northwest 284 1576562.1966 0.00 Northwest 283 1573012.9383 1576562.1966 Northwest 280 1352577.1325 1573012.9383
C. Specifying arbitrary expressions
The following example demonstrates specifying a variety of arbitrary expressions in the LAG 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,
LAG(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 1 2 4 -2 1 NULL 8 3 1 -6 2 NULL NULL 1 5 NULL
