Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 357

INSERT INTO @MyOrderTotalsByYear(orderyear, qty) 

  SELECT 

    YEAR(O.orderdate) AS orderyear, 

    SUM(OD.qty) AS qty 

  FROM Sales.Orders AS O 

    JOIN Sales.OrderDetails AS OD 

      ON OD.orderid = O.orderid 

  GROUP BY YEAR(orderdate); 

 

SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty 

FROM @MyOrderTotalsByYear AS Cur 

  LEFT OUTER JOIN @MyOrderTotalsByYear AS Prv 

    ON Cur.orderyear = Prv.orderyear + 1;

This code returns the following output.

orderyear   curyearqty  prvyearqty 

----------- ----------- ----------- 

2006        9581        NULL 

2007        25489       9581 

2008        16247       25489

Note that in SQL Server 2012, there is a more efficient way to achieve the same thing, by using the 

LAG function, like this.

DECLARE @MyOrderTotalsByYear TABLE 

( 

  orderyear INT NOT NULL PRIMARY KEY, 

  qty       INT NOT NULL 

); 

 

INSERT INTO @MyOrderTotalsByYear(orderyear, qty) 

  SELECT 

    YEAR(O.orderdate) AS orderyear, 

    SUM(OD.qty) AS qty 

  FROM Sales.Orders AS O 

    JOIN Sales.OrderDetails AS OD 

      ON OD.orderid = O.orderid 

  GROUP BY YEAR(orderdate); 

 

SELECT orderyear, qty AS curyearqty, 

  LAG(qty) OVER(ORDER BY orderyear) AS prvyearqty 

FROM @MyOrderTotalsByYear;