Using the Row Number function and a WHILE loop to create the rolling average report
Using the Row_Number function and a WHILE loop to create the rolling average report
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 Latest posts by Steve Simon (see all) Reporting in SQL Server – Using calculated Expressions within reports - December 19, 2016 How to use Expressions within SQL Server Reporting Services to create efficient reports - December 9, 2016 How to use SQL Server Data Quality Services to ensure the correct aggregation of data - November 9, 2016
SQLShack
SQL Server training EspañolUsing the Row_Number function and a WHILE loop to create the rolling average report
May 27, 2015 by Steve SimonIntroduction
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. 123 set @Max = (Select Max(row) from #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: 12345678910 while (@Kount <= @max) Begin Insert into #rawdata2 select @Kount as Row , sum(Revenue1) as Rolling , Avg(Revenue1) as RollingAvg from #rawdata1 where row between @Kount - 2 and @Kount set @Kount = @Kount + 1 end 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: 123 where row between @Kount - 2 and @Kount @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
123456789101112131415161718192021222324252627282930313233343536373839404142434445 Use Rolling3GO IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULLBEGIN DROP TABLE #rawdata1ENDIF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULLBEGIN DROP TABLE #rawdata2END go declare @Max as intdeclare @Kount as intSet @Kount = 1SELECT row_number() Over (order by YearMth) as row,[YearMth] ,[Monthee] ,[ItemCount] , Revenue1 ,[Revenue2] into #rawdata1 FROM [Rolling3].[dbo].[Rolling31] order by YearMth set @Max = (Select Max(row) from #rawdata1) Create Table #Rawdata2 ( [Row] int, Rolling int, RollingAvg decimal(15,2) ) while (@kount <= @max) Begin Insert into #rawdata2 select @Kount as Row , sum(Revenue1) as Rolling , Avg(Revenue1) as RollingAvg from #rawdata1 where row between @Kount - 2 and @Kount set @Kount = @Kount + 1 end select rd1.row, rd1.yearMth,Rd1.Monthee,Rd1.Revenue1,Rd1.Revenue2,Rd2.rolling,rd2.RollingAvg from #rawdata2 rd2 inner join #rawdata1 rd1 on rd1.row = rd2.rowAddenda 2
123456789101112131415161718192021222324252627 USE [Rolling3]GO /****** Object: Table [dbo].[Rolling31] Script Date: 05/21/2015 14:51:10 ******/SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGO SET ANSI_PADDING ONGO CREATE TABLE [dbo].[Rolling31]( [YearMth] [varchar](6) NULL, [Monthee] [varchar](20) NULL, [ItemCount] [int] NULL, [Revenue1] [int] NULL, [Revenue2] [int] NULL) ON [PRIMARY] GO SET ANSI_PADDING OFFGO Author Recent Posts Steve SimonSteve 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 Latest posts by Steve Simon (see all) Reporting in SQL Server – Using calculated Expressions within reports - December 19, 2016 How to use Expressions within SQL Server Reporting Services to create efficient reports - December 9, 2016 How to use SQL Server Data Quality Services to ensure the correct aggregation of data - November 9, 2016