SQL 2012 : LEAD & LAG (Transact-SQL)

LEAD and LAG are Windowing functions new to SQL Server 2012 returning the next and previous rows in a set respectively.

This eliminates the need for a self join (joining back to the same table)

Here are a couple of examples using AdventureWorks2012

Fistly, alphabetical currency names...

SELECT 
  [Name]
, LEAD(Name,1) OVER (ORDER BY Name ) LeadValue
, LAG(Name,1) OVER (ORDER BY Name ) LagValue
FROM [Sales].[Currency]

More usefully, LEAD and LAG used to compare the totals of shipped products with the next and previous days.

SELECT 
   ShipDate
  ,LAG(ValueShipped,1) OVER (ORDER BY ShipDate) AS DayBefore 
  ,ValueShipped
  ,LEAD(ValueShipped,1) OVER (ORDER BY ShipDate) AS DayAfter
FROM

 (
  SELECT ShipDate, SUM(TotalDue) AS ValueShipped
  FROM [Purchasing].[PurchaseOrderHeader]
  GROUP BY [ShipDate]
 ) ShippingTotals

ORDER BY [ShipDate]

Links :

Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012

SQL Server 2012 Functions - Lead and Lag

LAG (Transact-SQL)

LEAD  (Transact-SQL)