{"id":1713,"date":"2013-10-17T00:00:00","date_gmt":"2013-10-17T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/calculating-values-within-a-rolling-window-in-transact-sql\/"},"modified":"2021-09-29T16:21:41","modified_gmt":"2021-09-29T16:21:41","slug":"calculating-values-within-a-rolling-window-in-transact-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/calculating-values-within-a-rolling-window-in-transact-sql\/","title":{"rendered":"Calculating Values within a Rolling Window in Transact SQL"},"content":{"rendered":"
\n

Calculating Values within a Rolling Window in SQL<\/h1>\n

Any time that you need to combine values across several rows in SQL, the problem can be challenging, particularly when it comes to performance.  We will focus upon the rolling twelve-month totals problem, but our methods can be applied to any time window (e.g., 3 months) or to averages and other aggregations across those time windows as well. <\/p>\n

A rolling total for a month is the total for that month plus the previous months within the time window, or NULL<\/code> if you don’t have the values for all the previous months within the time window .<\/p>\n

In previous versions of SQL Server, you had to jump through a few hoops to come up with a method that performs well, but SQL 2012 offers some new features that make it simpler.  In either case, there are several valid solutions.  Which is fastest and most efficient?  We’ll try to answer this question  in this article.<\/p>\n

We will be working in SQL 2012.  If you would like to follow along, you can use the Sample Queries.sql<\/i><\/b> resource you’ll find attached.<\/p>\n

Data Setup and Statement of the Business Problem<\/h2>\n

Often times you’ll find yourself with many transactions within a month, but in our case we’ll assume you’ve already grouped your transactions for each month.  We’ll assign our PRIMARY KEY<\/code> to a DATE<\/code> data type, and include some values over which we want to accumulate rolling twelve month totals.<\/p>\n

CREATE TABLE #RollingTotalsExample\n(\n    [Date]     DATE PRIMARY KEY\n    ,[Value]   INT\n);\n \nINSERT INTO #RollingTotalsExample\nSELECT '2011-01-01',626\nUNION ALL SELECT '2011-02-01',231 UNION ALL SELECT '2011-03-01',572\nUNION ALL SELECT '2011-04-01',775 UNION ALL SELECT '2011-05-01',660\nUNION ALL SELECT '2011-06-01',662 UNION ALL SELECT '2011-07-01',541\nUNION ALL SELECT '2011-08-01',849 UNION ALL SELECT '2011-09-01',632\nUNION ALL SELECT '2011-10-01',906 UNION ALL SELECT '2011-11-01',961\nUNION ALL SELECT '2011-12-01',361 UNION ALL SELECT '2012-01-01',461\nUNION ALL SELECT '2012-02-01',928 UNION ALL SELECT '2012-03-01',855\nUNION ALL SELECT '2012-04-01',605 UNION ALL SELECT '2012-05-01',83\nUNION ALL SELECT '2012-06-01',44 UNION ALL SELECT '2012-07-01',382\nUNION ALL SELECT '2012-08-01',862 UNION ALL SELECT '2012-09-01',549\nUNION ALL SELECT '2012-10-01',632 UNION ALL SELECT '2012-11-01',2\nUNION ALL SELECT '2012-12-01',26;\n \nSELECT * FROM #RollingTotalsExample;\n<\/pre>\n
Since a valid, rolling twelve month total can’t occur until you have at least  twelve months of data in your set, we seek to generate a NULL value for our rolling total column for the first 11 rows  in the returned results.  It is only in the 12th month of 2011  that we will have twelve months of data in which to calculate the rolling total.  With our sample data, we can calculate that total as 7776 (or we can run the query below if manual calculations  aren’t your thing).<\/p>\n
SELECT SUM(Value)\nFROM #RollingTotalsExample\nWHERE [Date] <= '2011-12-01';\n<\/pre>\n
Calculating a rolling twelve month total is akin to calculating a running total  from all prior rows, with just a few calculation tricks we’ll show a little later on.<\/p>\n
Solutions That Work in SQL Server 2005 Onwards<\/h2>\n
Solution #1: Using A Tally Table<\/h3>\n
Hopefully you’ll know what a Tally table is, but to make a long story short it  is simply a table that has one integer column that is a sequential number from 1 to n, n being the number of rows you  need.  Since we know that each monthly row in our test table must be summed  into exactly 12 other rows (ignoring end points), perhaps we can use a simple zero-based Tally table to do this.  Let’s give it a try.<\/p>\n
WITH Tally (n) AS\n(\n    SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7\n    UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11\n)\nSELECT [Date], Value, n, GroupingDate=DATEADD(month, n, [Date])\nFROM #RollingTotalsExample a\nCROSS APPLY Tally b\nWHERE [Date] = '2011-01-01';\n<\/pre>\n
This query returns 12 rows where the GroupingDate<\/code> column represents the period  we want to sum the 2011-01-01 row into.  It is then a relatively simple  matter to create the sum over the grouping like this.<\/p>\n
WITH Tally (n) AS\n(\n    SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7\n    UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11\n)\nSELECT GroupingDate=DATEADD(month, n, [Date])\n    ,Value=MAX(CASE n WHEN 0 THEN a.Value END)\n    ,Rolling12Months=SUM(Value)\nFROM #RollingTotalsExample a\nCROSS APPLY Tally b\nGROUP BY DATEADD(month, n, [Date])\nORDER BY DATEADD(month, n, [Date]);\n<\/pre>\n
When we examine the results of this query, we find that there are a couple of  issues:<\/p>\n