SQL LAG Function

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

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

Customer	TotalAmount	OrderDate	Previous Order Date
Ann Devon	3063.00	2013-01-01 00:00:00.000	NULL
Paula Wilson	3868.60	2013-01-01 00:00:00.000	2013-01-01 00:00:00.000
Roland Mendel	2713.50	2013-01-02 00:00:00.000	2013-01-01 00:00:00.000
Roland Mendel	1005.90	2013-01-03 00:00:00.000	2013-01-02 00:00:00.000
Giovanni Rovelli	1675.00	2013-01-03 00:00:00.000	2013-01-03 00:00:00.000

Syntax of the LAG function.

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

SELECT MONTH(OrderDate) AS [Month], SUM(TotalAmount) AS Sales,
       LAG(SUM(TotalAmount), 1, 0) 
           OVER(ORDER BY MONTH(OrderDate)) AS 'Previous Month',
       SUM(TotalAmount) - LAG(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',
       LAG(SUM(TotalAmount), 6) 
           OVER(ORDER BY MONTH(OrderDate)) AS '6 Months ago'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)

• Our Sql Window Functions Reference
• Our Sql FIRST_VALUE Function Reference
• Our Sql LEAD Function Reference