Figure 7 Single Query Returning Yearly Totals
　

DECLARE @CustomerID AS INT
SET @CustomerID = 3

WITH YearlyCustOrders(OrderYear, TotalValue)
AS
(
  SELECT YEAR(OrderDate), SUM(OrderQty*UnitPrice)
  FROM SalesOrderHeader AS H
    JOIN SalesOrderDetail AS D
      ON H.SalesOrderID = D.SalesOrderID
  WHERE CustomerID = @CustomerID
  GROUP BY YEAR(OrderDate)
)
SELECT Cur.*, Prev.TotalValue AS PrevTotalValue,
  (Cur.TotalValue/Prev.TotalValue - 1) * 100 AS Diff
FROM YearlyCustOrders AS Cur
  LEFT OUTER JOIN YearlyCustOrders AS Prev
    ON Cur.OrderYear = Prev.OrderYear + 1;