LAG & LEAD

Syntax:

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

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

Examples:

SELECT ct.CustomerTransactionID,
       ct.TransactionDate,
       ct.TransactionAmount,
       LAG(TransactionAmount, 1, 0) OVER(ORDER BY ct.TransactionDate) AS [LAG],
       LEAD(TransactionAmount, 1, 0) OVER(ORDER BY ct.TransactionDate) AS [LEAD],
       SUM(ct.TransactionAmount) OVER(PARTITION BY DATEPART(MONTH, TransactionDate)
       ORDER BY ct.TransactionDate) [RunningTotalByMonth]
FROM sales.CustomerTransactions ct
WHERE ct.TransactionDate BETWEEN '2013-01-01' AND '2013-12-31'
      AND customerid = 808
      AND ct.TransactionTypeID = 1
ORDER BY ct.TransactionDate;