SQL LEAD Function

List all orders. In each row include the next order date.

SELECT CONCAT(FirstName, ' ', LastName) AS Customer, 
       TotalAmount, OrderDate,
       LEAD(OrderDate) OVER(ORDER BY OrderDate) AS 'Next Order Date'
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id

Customer	TotalAmount	OrderDate	Next Order Date
Patricia McKenna	3127.00	2012-09-05 00:00:00.000	2012-09-06 00:00:00.000
Janete Limeira	349.50	2012-09-06 00:00:00.000	2012-09-09 00:00:00.000
Giovanni Rovelli	608.00	2012-09-09 00:00:00.000	2012-09-09 00:00:00.000
Rita Müller	755.00	2012-09-09 00:00:00.000	2012-09-10 00:00:00.000
Pascale Cartrain	2708.80	2012-09-10 00:00:00.000	2012-09-11 00:00:00.000
José Pedro Freyre	1242.00	2012-09-11 00:00:00.000	2012-09-12 00:00:00.000

Syntax of the LEAD function.

LEAD(scalar-expression, offset, default) 
  OVER( PARTITION BY expression, ...
       ORDER BY expression, ... )

SELECT MONTH(OrderDate) AS [Month], SUM(TotalAmount) AS Sales,
       LEAD(SUM(TotalAmount), 1, 0) 
            OVER(ORDER BY MONTH(OrderDate)) AS 'Next Month',
       SUM(TotalAmount) - LEAD(SUM(TotalAmount), 1, 0) 
           OVER(ORDER BY MONTH(OrderDate)) AS Delta
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)

SELECT MONTH(OrderDate) AS [Month], 
       SUM(TotalAmount) AS 'Monthly Sales',
       LEAD(SUM(TotalAmount), 6) 
           OVER(ORDER BY MONTH(OrderDate)) AS '6 Months ahead'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)

• Our Sql Window Functions Reference
• Our Sql LAST_VALUE Function Reference
• Our Sql LAG Function Reference