Dynamic calculations of running totals

A few weeks back, a friend from South Africa wrote to
me asking if I knew of a way to quickly produce cumulative totals from monthly
data figures. Here is one way to achieve just that.

As the fiscal year at his firm begins in July and ends at
the end of June, the trick was to first obtain the data in tabular form with
the first fiscal month on top and the last in the bottom row. (See the first grid
below).

The raw data within the grid comes from his data warehouse and the result
set stored within a local temporary table, to be used to calculate the running
totals.

The ‘YearMth’ column is not hardwired but derived from the
year under consideration. I shall blog how this was achieved in an upcoming
posting.

To the astute reader, the ‘YearMth’ column is used as a sort
field and never appear in any of his reports. The calendar month is shown in
its stead.

The result set from the 'summing' of items count and dollar
revenue BY month may be seen in the table below:

 Coming from both a SQL Server and Oracle world, I can see
where the naughty word CURSOR can be a double edged sword. To the doubting
Thomas’ out there, there IS a time and place for cursors.

Considering that my data source for my cursor was stored in a temporary table and
that the number of records extracted were
less than 50000 rows,  I took the plunge
and used a CURSOR to calculated the running total.

Getting the job done

The first task was to define a table variable @DollarTable.
This table defined the 5 fields (YearMth, Month, Item Count , Dollar Revenue and
Cumulative, 4 of which we observed in the grid above.

We then define a few more local variables @YearMth,
@Month,@ItemCount,@DollarRevenue,@RunningTotal (as may be seen in the code below). These variables are used within the cursor.

@RunningTotal will be the variable associated with the
cumulative totals. See the code snippet below.

DECLARE @DollarTable
TABLE (YearMth Varchar(6), Month varchar(12), [Item Count] int,

[Dollar Revenue] decimal(10,2), [Cumulative] decimal(10,2))

DECLARE @YearMth Varchar(6),

        @Month Varchar(12),

        @ItemCount int,

        @DollarRevenue decimal(10,2),

        @RunningTotal decimal(10,2)

SET
@RunningTotal = 0.0

DECLARE rt_cursor CURSOR

FOR

SELECT yearmth,Month,[Item Count], [Dollar
Revenue]  

FROM #rawdata8

ORDER BY YearMth

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @YearMth,@Month,@ItemCount,@DollarRevenue

WHILE @@FETCH_STATUS = 0

 BEGIN

 SET
@RunningTotal = @RunningTotal + @DollarRevenue

 INSERT @DollarTable
VALUES (@YearMth,@Month,@ItemCount,@DollarRevenue,@RunningTotal)

 FETCH NEXT FROM rt_cursor INTO @YearMth,@Month,@ItemCount,@DollarRevenue

 END

CLOSE rt_cursor

DEALLOCATE rt_cursor

SELECT * into #rawdata9 FROM @DollarTable

The reader will note that @RunningTotal had been initalized to 0. We now
open our cursor (as a transaction) to read the first record from our local
temporary data table and WHILE there are records to FETCH, we loop through the
records.

The first task is to set @RunningTotal to the value of @RunningTotal (0 on
the first pass) to @RunningTotal + the DollarRevenue of the first record.

With each subsequent pass, @RunningTotal is incremented by the value of @DollarRevenue,

We now insert the YearMth, Month, Item Count, Dollar Revenue AND the
running total into our table variable @DollarTable, and then fetch the next
row.

Once all the rows are processed, @@FETCH_STATUS is no longer 0  and the looping is halted.

We then insert the contents of the table variable @DollarTable into
another temporary table for further row processing.

The results of the cumulative table may be seen below: 

Summary

In this post, I have shown a manner in which a cumulative total of monthly
income can be created using two temporary tables, a cursor and a table
variable.

In my next posting, I shall show how we can utilize the Pivot function to process
the data so that the months are column based, with the dollar revenues and cumulative revenue values, row based.

As always, should you have any questions or thoughts that may have arisen,
please contact me at steve.simon@sqlpass.org

I would love to hear from you!!