Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Dynamic calculations of running totals

  • 24 June 2013
  • Author: Steve Simon
  • Number of views: 14129
  • 0 Comments

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 appears 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:

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 the 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, please contact me at steve.simon@sqlpass.org

I would love to hear from you!!

 

 

 

Print

Steve Simonokeofs

Other posts by Steve Simon

Please login or register to post comments.