Steve Simon

Using the “Row_Number” function and a WHILE loop to create the rolling average report

May 27, 2015 by

Introduction

A few days ago I received an email from a gentleman in the healthcare industry. He had read an article that I had previously published a few years back on a well known SQL Server website. Based on the original article, he requested a few ideas on how to expand the sample code to do a rolling three-month revenue summary, in addition to a rolling three-month revenue average.

In today’s get together, we shall be looking at doing just this!

Getting Started

As a starting point, let us have a quick look at the sample data that the gentleman provided.

Column A contains a six character field containing the year and month of the revenue. We shall utilize this field as a sorting field. Column B contains the month name and Column C the number of articles sold that month.

Columns D and E contain financial data. We shall be looking exclusively at Column D.

Columns F and G are of most interest to us as they contain the “Rolling 3 Month Total” and “Rolling 3 Month Average” that the gentleman expected to be calculated at runtime.

We now import this data into the Rolling3 database. The imported data may be seen immediately below and the table structure may be seen in Addenda 2.

The astute reader will note that neither the summary nor the average figures from the spreadsheet have been imported into the data table. Once again, these will be calculated at runtime.

Now that we have a high-level understanding of the data, let us get busy producing a query to produce the desired results.

We begin by opening SQL Server Management Studio and open a new query. As we shall be utilizing two temporary tables, we need to develop some helpful code that will delete these two temporary tables prior to each run (see the screenshot below). In reality, the user will probably utilize a stored procedure in which case this code is not necessary and must be commented out prior to creating the stored procedure.

The screenshot above shows the code to delete any existing local temporary tables with the names #rawdata1 and #rawdata2. This will ensure that each time we run the query that the two temporary tables do not exist and that we are able to create them at runtime. Should the table already exist as a result of a previous run of this query (and do not utilize this code) then the query execution will terminate with an error condition. The complete code listing may be seen in Addenda 1.

Additionally, we declare two variables: @Max and @Kount. We set the value of @Kount to 1.

We are now in a position to pull all the relevant data from our table “Rolling31”

The reader will note that in the screenshot above, that we have created a row number for each record that we shall be pulling and that the data has been sorted by increasing “YearMth”. The purpose of the “row” will become apparent in a few minutes.

IT SHOULD ALSO BE NOTED THAT THE SOURCE DATA WAS INSERTED INTO THE TABLE, SORTED BY “YEARMTH” IN ASCENDING ORDER. If this is not the case with your data, then you will probably have to do a pre-sort step on your data.

The results for this portion of this query may be seen in the screenshot below:

We now set the value of “@Max” based upon the maximum row number within the temporary table #rawdata1.

In our case it is 9.

As may be seen in the screenshot above, we have done something a bit unorthodox by creating a temporary table called #rawdata2. Creating the temporary table at this point requires a bit of explanation.

As we shall be calculating the running three month revenue totals and the running three month averages for each “YearMth” we need to process the calculations and insert them into the temporary table #rawdata2 from within the “While Loop”.

@Max (which we calculated above), will be the maximum number of times that we iterate through the while loop. The loop code may be seen below:

The “eagle-eyed” reader will note that each time we iterate through the loop that we insert the current record into the temporary table #rawdata2 (using an “Insert into” clause).

We select the row number (the importance of which we shall see in a few minutes) , the sum of the revenue from the prior two months PLUS the“current month” (for this current iteration) and the average of the revenue for the same two prior months and include “current month” (for this current iteration). This being achieved via the predicate:

@Kount is then incremented by 1. Once @Kount is greater than @max, we break out of the loop and processing continues.

The results of the loop processing may be seen below:

Our last task is to join the first temporary table (#rawdata1) to the second temporary table (#rawdata2), performing an inner join on the “Row” column (see below).

Enlarging a portion of the screen shot above, we note that the results for the rolling three month revenue and three month average are the same as in the original spreadsheet (see below).

This said, we have our final result and we have achieved our end goal.

Conclusions

Necessity is definitely the “mother of invention” and in this case what began as an interesting challenge, proved to be an easier than one would have expected. We have worked with the “Row_Number” function and through the usage of a while loop, we have been able to reach our end goal.

My challenge to you is to try to utilize the same logic and to create a similar ‘routine’ using a cursor as I have utilized in many of my previous articles on SQL Shack.

Should you have any queries or concerns, please do feel free to contact me.

In the interim, happy programming.

Addenda 1: The query

Addenda 2

Steve Simon
Functions, General

About Steve Simon

Steve Simon is a SQL Server MVP and a senior BI Development Engineer with Atrion Networking. He has been involved with database design and analysis for over 29 years. Steve has presented papers at 8 PASS Summits and one at PASS Europe 2009 and 2010. He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally. Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor. View all posts by Steve Simon

168 Views