SQL Server Denali - LAG and LEAD

This is a short post on 2 new T-SQL features called LAG and LEAD, they are kind like Bonny & Clyde, they are mentioned in the same phrase...

LAG and LEAD accesses the data from respectively the previous and the next row in a table without using self joins.

Syntax:

LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )


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

USE
AdventureWorks2008R2

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;

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, MONTH(QuotaDate) AS SalesMonth, SalesQuota AS CurrentQuota, LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate), MONTH(QuotaDate)) AS NextQuota

FROM Sales.SalesPersonQuotaHistory

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

Have Fun!