SQL - LAG()

 Explanation

Accesses data from a previous row in the same result set without the use of a self-join starting with SQL Server 2012 (11.x). 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.

SELECT [DeptID], [Name], [Salary],
LAG ([Salary], 1) OVER (ORDER BY [Salary]) AS [Lower Salary]
FROM [dbo].[Salary]
 
SELECT [DeptID], [Name], [Salary],
LAG ([Salary], 2) OVER (ORDER BY [Salary]) AS [Lower Salary]
FROM [dbo].[Salary]

 

Example 1

USE AdventureWorks2014; 

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 ('2010','2011'); 

 

Example 2

USE AdventureWorks2014; 

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;

 

Example 3

USE AdventureWorks2014; 

GO 

SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota, 

       LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota, 

       SalesAmountQuota - LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff 

FROM dbo.FactSalesQuota 

WHERE EmployeeKey = 272 AND CalendarYear IN (2010, 2011) 

ORDER BY CalendarYear, CalendarQuarter; 

Example 4

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;