Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 351

DECLARE 

  @custid     AS INT, 

  @prvcustid  AS INT, 

  @ordermonth DATETIME, 

  @qty        AS INT, 

  @runqty     AS INT; 

 

DECLARE C CURSOR FAST_FORWARD /* read only, forward only */ FOR 

  SELECT custid, ordermonth, qty 

  FROM Sales.CustOrders 

  ORDER BY custid, ordermonth; 

 

OPEN C; 

 

FETCH NEXT FROM C INTO @custid, @ordermonth, @qty; 

 

SELECT @prvcustid = @custid, @runqty = 0; 

 

WHILE @@FETCH_STATUS = 0  

BEGIN 

  IF @custid <> @prvcustid 

    SELECT @prvcustid = @custid, @runqty = 0; 

 

  SET @runqty = @runqty + @qty; 

 

  INSERT INTO @Result VALUES(@custid, @ordermonth, @qty, @runqty); 

   

  FETCH NEXT FROM C INTO @custid, @ordermonth, @qty; 

END 

 

CLOSE C; 

 

DEALLOCATE C; 

 

SELECT  

  custid, 

  CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth, 

  qty, 

  runqty 

FROM @Result 

ORDER BY custid, ordermonth;

The code declares a cursor based on a query that returns the rows from the CustOrders view 

ordered by customer ID and order month, and iterates through the records one at a time. The code 

keeps track of the current running total quantity in a variable called @runqty that is reset every time a 

new customer is found. For each row, the code calculates the current running total by adding the cur-

rent month’s quantity (@qty) to @runqty, and inserts a row with the customer ID, order month, current 

month’s quantity, and running quantity into a table variable called @Result. When the code is done 

processing all cursor records, it queries the table variable to present the running aggregates. 

www.it-ebooks.info

---

352  

Microsoft SQL Server 2012 T-SQL Fundamentals

Here’s the output returned by this code, shown in abbreviated form.

custid      ordermonth qty         runqty 

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

1           2007-08    38          38 

1           2007-10    41          79 

1           2008-01    17          96 

1           2008-03    18          114 

1           2008-04    60          174 

2           2006-09    6           6 

2           2007-08    18          24 

2           2007-11    10          34 

2           2008-03    29          63 

3           2006-11    24          24 

3           2007-04    30          54 

3           2007-05    80          134 

3           2007-06    83          217 

3           2007-09    102         319 

3           2008-01    40          359 

... 

89          2006-07    80          80 

89          2006-11    105         185 

89          2007-03    142         327 

89          2007-04    59          386 

89          2007-07    59          445 

89          2007-10    164         609 

89          2007-11    94          703 

89          2008-01    140         843 

89          2008-02    50          893 

89          2008-04    90          983 

89          2008-05    80          1063 

90          2007-07    5           5 

90          2007-09    15          20 

90          2007-10    34          54 

90          2008-02    82          136 

90          2008-04    12          148 

91          2006-12    45          45 

91          2007-07    31          76 

91          2007-12    28          104 

91          2008-02    20          124 

91          2008-04    81          205 

 

(636 row(s) affected)

As explained in Chapter 7, SQL Server 2012 supports enhanced window functions that allow you to 

provide elegant and highly efficient solutions to running aggregates, freeing you from needing to use 

cursors. Here’s how you would address the same task with a window function.

SELECT custid, ordermonth, qty, 

  SUM(qty) OVER(PARTITION BY custid 

                ORDER BY ordermonth 

                ROWS UNBOUNDED PRECEDING) AS runqty 

FROM Sales.CustOrders 

ORDER BY custid, ordermonth;

www.it-ebooks.info

---