# Dynamic calculations of running totals

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

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

 YearMth Month Item Count Dollar Revenue 201207 July 4 122 201208 August 3 12 201209 September 3 143 201210 October 6 66 201211 November 6 88 201212 December 5 77 201301 January 7 321 201302 February 7 465 201303 March 6 876 201304 April 15 906 201305 May 8 756 201306 June 4 32

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:

 YearMth Month Item Count Dollar Revenue Cumulative 201207 July 4 122 122 201208 August 3 12 134 201209 September 3 143 277 201210 October 6 66 343 201211 November 6 88 431 201212 December 5 77 508 201301 January 7 321 829 201302 February 7 465 1294 201303 March 6 876 2170 201304 April 15 906 3076 201305 May 8 756 3832 201306 June 4 32 3864

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,

I would love to hear from you!!

## Simon Says

##### Posted by abdul.badru on 15 May 2014

Hi, you can also do this using cross join. For the code below you need to introduce a rank column on yout #rawdata8 (1,2...12)

select a.[YearMth],a.RankOrder ,a.[Month],a.[Dollar Revenue], 'Cumulative' = sum(b.[Dollar Revenue])

into #tmp

from  #rawdata8 a

cross join

#rawdata8 b

where left(rtrim(ltrim(a.[YearMth])),4)<= left(rtrim(ltrim(b.[YearMth])),4)

and a.RankOrder >= b.RankOrder

group by a.[YearMth],a.ordem ,a.[Month],a.[Dollar Revenue]

order by a.RankOrder

select * from #tmp

Regards

##### Posted by abdul.badru on 15 May 2014

Hi, you can also do this using cross join. For the code below you need to introduce a rank column on yout #rawdata8 (1,2...12)

select a.[YearMth],a.RankOrder ,a.[Month],a.[Dollar Revenue], 'Cumulative' = sum(b.[Dollar Revenue])

into #tmp

from  #rawdata8 a

cross join

#rawdata8 b

where left(rtrim(ltrim(a.[YearMth])),4)<= left(rtrim(ltrim(b.[YearMth])),4)

and a.RankOrder >= b.RankOrder

group by a.[YearMth],a.ordem ,a.[Month],a.[Dollar Revenue]

order by a.RankOrder

select * from #tmp