Say you want to find the most recent 30-day period during which a person purchased some amount of products from your company. How you market to a customer might change if they have been active over a time period recently, or even in the past. But this also means that for each day going back in history, you need to sum historic data over and over, and the previous 29 days of activity. This is generally known as a rolling total. Doing this sort of calculation has been an interesting problem for many years.
When window functions came around, they became quite useful for such tasks, but they have one kind of complicated problem: gaps in source data patterns. The code I found in all my searches and AI worked something like this (this code was generated by Google’s AI):
SELECT date_column,
value_column,
SUM(value_column) OVER (
ORDER BY date_column
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_total
FROM your_table;
This is awesome if your data has every single time period existing, because then you can get the current and previous 2 windows and sum them up for the running total. Like if your customer has a sale (or a 0 when no sale) for every day, you can use this construct nicely. But if your data is very sparse (as most data for sales typically is), you can’t use this technique unless you manufacture rows for each time period (and it can turn out to be a lot of generated 0 values. As I started to think about how I would do this, it got me thinking that basically, what we are looking at is the need to do overlapping queries. So in my example scenario, I needed a set of range queries:
Range Start Date Range End Date ---------------------- ----------------- Date - 29 Date (Date - 1) - 29 Date - 1 (Date - 2) - 29 Date - 2 (Date - 3) - 29 Date - 3
So you might have the following ranges you are looking for, and the end date is the date you are checking for activity over 30 days.
12-01-2025 12-31-2025 11-30-2025 12-30-2025 11-29-2025 12-29-2025 11-28-2025 12-28-2025
So 30 days of activity is everything after 12-01-2025 and includes everything on 12-31-2025. (This works nicely for data rounded to the date, but you would want to change your ranges to deal with start and end dates for data that has a time element)
An example
Using AndventureWorks2025DW for my example data (which you can get at the link), I am going to use this technique to find a monthly running 12-month sales order history. You can easily see how you could take the output of this and find the most recent time periods when they had some threshold of sales orders. Like 200 in purchases over 12 months, pretty recently could mean you market to them differently than if it had been years.
To start with, I am going to grab some data to look at. I found this customer key value that had quite a few sales, and in 2013, they had sales each month:
SELECT DimCustomer.CustomerKey,
OrderDimDate.CalendarYear, OrderDimDate.MonthNumberOfYear,
SUM(FactInternetSales.SalesAmount) AS MonthlySales
FROM dbo.FactInternetSales
JOIN dbo.DimDate AS OrderDimDate
ON OrderDimDate.DateKey = FactInternetSales.OrderDateKey
JOIN dbo.DimCustomer
ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey
WHERE DimCustomer.CustomerKey = 11300
GROUP BY DimCustomer.CustomerKey,
OrderDimDate.CalendarYear,
OrderDimDate.MonthNumberOfYear
ORDER BY OrderDimDate.CalendarYear desc,
OrderDimDate.MonthNumberOfYear DESC;
This returns:
CustomerKey CalendarYear MonthNumberOfYear MonthlySales =---------- ------------ ----------------- --------------------- 11300 2013 12 250.85 11300 2013 11 59.48 11300 2013 10 255.25 11300 2013 9 80.24 11300 2013 8 201.10 11300 2013 7 233.90 11300 2013 6 8.99 11300 2013 5 127.34 11300 2013 4 34.99 11300 2013 3 174.51 11300 2013 2 32.28 11300 2013 1 41.59
In my example, I am going to pretend the requirements are rolling 3-month periods starting with today, and then looking back 3 months. So really what I am looking for is represented in these rows for the first 4 months (the queries treat the end date as inclusive (the start date IS excluded), and I am creating this code on Feb 16, so any sales today would count.):
GroupValue StartDate EndDate ----------- ---------- ---------- 0 2025-11-16 2026-02-16 1 2025-10-16 2026-01-16 2 2025-09-16 2025-12-16 3 2025-08-16 2025-11-16
If it was in the previous month, it is considered the 0th month (or current), but in the actual query (that I won’t include in this blog for brevity reasons) I will be fetching the minimum number of months back. My solution (that works nicely for pretty much any time frame I have tried (of course, it does get more costly for smaller time frames in other data sets), is to manufacture rows to include those ranges using a CTE that uses GENERATE_SERIES. You can use any of the techniques in this blog if you don’t have that function available.
When I first tried this, I just created a table for the fixed ranges I needed and recreated them daily as the need arose in my data warehouse. But in my latest tests, I just decided to build the range on the fly, which means I can do whatever range I want to pretty easily. This next query in the article is the query that does this for my one test case. Notice I go back 500 TIME periods, but you can go back as far as you need to. I will break down the technique after the first output.
*/
--start where the data stats in the sample database
--(2014-12-31 in the current version)
DECLARE @StartingValue DATE =
(SELECT MAX([FullDateAlternateKey]) FROM dbo.DimDate);
--display the starting time
SELECT @StartingValue AS StartingValue;
WITH TimeFrame AS (
SELECT --generate time frames to look back to. For me, it is month or years
-- or even weeks, hours; to look back to
Value AS GroupValue, --This is what you are going to call the group
--here I will call it the number of months prior
--to the starting data
--3 months earlier than the EndDate
DATEADD(month,-3,DATEADD(MONTH,-value,@StartingValue)) AS StartDate,
--series starting with today, incremented by a month
DATEADD(MONTH,-value ,@StartingValue) AS EndDate
FROM GENERATE_SERIES(0,500)
)
SELECT GroupValue,
MAX(TimeFrame.StartDate) AS StartDate,
MAX(TimeFrame.EndDate) AS EndDate,
--Filtered to one, remove to see this
--and remove WHERE to see all customers
--FactInternetSales.CustomerKey,
SUM(FactInternetSales.SalesAmount) AS RollingSum
FROM dbo.FactInternetSales
JOIN dbo.DimDate AS OrderDimDate
ON OrderDimDate.DateKey = FactInternetSales.OrderDateKey
JOIN TimeFrame
--note that we don't include the startdate, but we do the end
--since that is the day we are iterating on.
ON OrderDimDate.FullDateAlternateKey > TimeFrame.StartDate
AND OrderDimDate.FullDateAlternateKey <= TimeFrame.EndDate
WHERE FactInternetSales.CustomerKey = 11300
GROUP BY FactInternetSales.CustomerKey, TimeFrame.GroupValue;
This returns:
StartingValue ------------- 2014-12-31 GroupValue StartDate EndDate RollingSum ----------- ---------- ---------- --------------------- 10 2013-11-28 2014-02-28 250.85 11 2013-10-31 2014-01-31 310.33 12 2013-09-30 2013-12-31 565.58 13 2013-08-30 2013-11-30 394.97 14 2013-07-31 2013-10-31 536.59 15 2013-06-30 2013-09-30 515.24 16 2013-05-31 2013-08-31 443.99 17 2013-04-30 2013-07-31 370.23 18 2013-03-30 2013-06-30 171.32 19 2013-02-28 2013-05-31 336.84 20 2013-01-30 2013-04-30 283.37 21 2012-12-31 2013-03-31 248.38 22 2012-11-28 2013-02-28 73.87 23 2012-10-31 2013-01-31 41.59
You can see that the start date in this case is always the last day of the month, and 3 months earlier is the last day of 4 months prior.
To validate the process, take the data we gathered earlier that summed up each month:
CustomerKey CalendarYear MonthNumberOfYear MonthlySales ----------- ------------ ----------------- --------------------- 11300 2013 12 250.85 11300 2013 11 59.48 11300 2013 10 255.25 11300 2013 9 80.24 11300 2013 8 201.10 11300 2013 7 233.90 11300 2013 6 8.99 11300 2013 5 127.34 11300 2013 4 34.99 11300 2013 3 174.51 11300 2013 2 32.28 11300 2013 1 41.59
You can easily do the math for each of the rolling MonthlySales values:
Group 10 250.85 250.85 Group 11 310.33 250.85 + 59.48 Group 12 565.58 250.85 + 59.48 + 255.25 Group 13 394.97 59.48 + 255.25 + 80.24
And yes, this is why I aligned the months with the rolling sum to make it easier to show the outcome. In the later examples, I will show more interesting rolling sum time periods, but once you see the pattern, it is simple to do most any pattern you need.
Running this for all the data in the AdventureWorks FactInternetSales table is as easy as just uncommenting out a few rows of code:
--start where the data stats in the sample
--database (2014-12-31 in the current version)
DECLARE @StartingValue DATE =
(SELECT MAX([FullDateAlternateKey]) FROM dbo.DimDate);
--display the starting time
SELECT @StartingValue AS StartingValue;
WITH TimeFrame AS (
SELECT --generate time frames to look back to. For me, it is month or years
-- or even weeks, hours; to look back to
Value AS GroupValue, --This is what you are going to call the group
--here I will call it the number of months prior
--to the starting data
--3 months earlier than the EndDate
DATEADD(month,-3,DATEADD(MONTH,-value,@StartingValue)) AS StartDate,
--series starting with today, incremented by a month
DATEADD(MONTH,-value ,@StartingValue) AS EndDate
FROM GENERATE_SERIES(0,500) --back to the 70's
)
SELECT GroupValue,
MAX(TimeFrame.StartDate) AS StartDate,
MAX(TimeFrame.EndDate) AS EndDate,
FactInternetSales.CustomerKey,
SUM(FactInternetSales.SalesAmount) AS RollingSum
FROM dbo.FactInternetSales
JOIN dbo.DimDate AS OrderDimDate
ON OrderDimDate.DateKey = FactInternetSales.OrderDateKey
JOIN TimeFrame
--note that we don't include the startdate, but we do the end
--since that is the day we are iterating on.
ON OrderDimDate.FullDateAlternateKey > TimeFrame.StartDate
AND OrderDimDate.FullDateAlternateKey <= TimeFrame.EndDate
GROUP BY FactInternetSales.CustomerKey, TimeFrame.GroupValue;
This returns 78833 rows for all the sales and companies in that table in about 3 seconds on my Testing Hardware. In a “real” set of data, I was able to process nearly 100 million rows and return 1 million in less than 20 seconds. So I am really happy with the performance of this method.
How this works
While I will only demonstrate look back time frames defined in days; hours, minutes, seconds, and other time frames are not a hard leap from here. For my next example, I am going to step back a day at a time and then look back a week. This is the basic format:
DECLARE @StartingValue DATE = **point in time you want your series to start**;
WITH TimeFrame AS (
SELECT **name the group, based on incrementing** AS GroupValue,
DATEADD(**define offset size**, **enddate definition**)) AS StartDate,
**define the series** /* starting around some starting value */
AS EndDate
FROM GENERATE_SERIES(0,**number of periods**) --depends on
)
So let’s step through this 1 piece at a time for a set of rows that represent a series of 10 days, with a rolling month time frame. In the next steps, I will walk through the settings we need to implement the series: rolling 2-day sums, starting today.
First, define when you want your series to start
This is basically replacing **point in time you want your series to start**. Much of the time this will be the current day, but just like is the case with DATE_BUCKET, some of the way these calculations work will be based on the day of the week/month of your start time. Like if you want calendar or fiscal months, you probably want to start on the first of a month.
In my example, it will start the day I ran these examples.
SYSDATETIME();
Next, we define the EndDate, which is actually the base of the series
In the sample query, there is a tag: **define the series**. Replace that with a date calculation that will generally move backwards. (Though moving forward might be useful for things like seeing how much a person purchases after another purchase)
Typically using DATEADD, we just pick some amount of time to iterate from the starting point. For this example, I am iterating over days going backwards, so I used:
–This would give you a series of dates going back one day at a timeDATEADD(day,-value,@StartingValue)
Next, define the window for the calculation
This is defined in the StartDate calculation which uses this basic formula:
DATEADD(**define offset size**, **enddate definition**)
Since in SQL, you can’t reference other expressions in the SELECT clause, repeat the EndDate definition:
DATEADD(**define offset size**, DATEADD(week,-value,@StartingValue))
Then define the offset, as another DATEADD expression to say how far back to go for each iteration. In our case, we want a 2 day in the past window of activity.
DATEADD(day,-2, DATEADD(week,-value,@StartingValue))
Next, name the grouping
I often use a number because I am looking for something like the most recent time a customer hit some threshold. As an example, you could look for when was the first time that the customer bought 2 things in 2 days that were over $100.
For this example, since we are doing a day, I will make it the day, so I replace the **name the group, based on incrementing** tag with just the same calculation as the EndDate.
DATEADD(day,-value,@StartingValue)
Finally, determine how many time periods you need
Depends on how large your set is. Stepping back days for 10 years? 3655 or so are needed. I will use 10 just to keep the set small. You need to do the date math for your desired set of data. You can make more than you need in most cases because it will only output the cases where there is data.
So I change:
GENERATE_SERIES(0,**number of periods**)
To:
GENERATE_SERIES(0,10)
Now I have this code, with a select to see all the data that is generated.
DECLARE @StartingValue DATE = SYSDATETIME();;
WITH TimeFrame AS (
SELECT DATEADD(day,-value,@StartingValue) AS GroupValue,
DATEADD(week,-1, DATEADD(week,-value,@StartingValue)) AS StartDate,
/* starting around some starting value */
DATEADD(day,-value,@StartingValue) AS EndDate
FROM GENERATE_SERIES(0,10) --For the example
)
SELECT *
FROM TimeFrame;
This returns:
GroupValue StartDate EndDate ---------- ---------- ---------- 2026-02-16 2026-02-09 2026-02-16 2026-02-15 2026-02-02 2026-02-15 2026-02-14 2026-01-26 2026-02-14 2026-02-13 2026-01-19 2026-02-13 2026-02-12 2026-01-12 2026-02-12 2026-02-11 2026-01-05 2026-02-11 2026-02-10 2025-12-29 2026-02-10 2026-02-09 2025-12-22 2026-02-09 2026-02-08 2025-12-15 2026-02-08 2026-02-07 2025-12-08 2026-02-07 2026-02-06 2025-12-01 2026-02-06
You can see the start date is a week behind the end date in each of the rows.
Other Examples
In this section, I will show a few more examples, just for reference:
Rolling 2 Days
DECLARE @StartingValue DATE = SYSDATETIME();
WITH TimeFrame AS (
SELECT --generate time frames to look back to. For me, it is month or years
-- or even weeks, hours; to look back to
Value AS GroupValue, --This is what you are going to call the group
--2 weeks earlier than the EndDate
DATEADD(day,-2,DATEADD(day,-value ,@StartingValue)) AS StartDate,
--series starting with today, incremented by a day
DATEADD(day,-value ,@StartingValue) AS EndDate
FROM GENERATE_SERIES(0,10)
)
SELECT *
FROM TimeFrame;
Which returns:
GroupValue StartDate EndDate ----------- ---------- ---------- 0 2026-02-14 2026-02-16 1 2026-02-13 2026-02-15 2 2026-02-12 2026-02-14 3 2026-02-11 2026-02-13 4 2026-02-10 2026-02-12 5 2026-02-09 2026-02-11 6 2026-02-08 2026-02-10 7 2026-02-07 2026-02-09 8 2026-02-06 2026-02-08 9 2026-02-05 2026-02-07 10 2026-02-04 2026-02-06
Rolling years with a month increment:
DECLARE @StartingValue DATE = SYSDATETIME();
WITH TimeFrame AS (
SELECT --generate time frames to look back to. For me, it is month or years
-- or even weeks, hours; to look back to
Value AS GroupValue, --This is what you are going to call the group
--3 months earlier than the EndDate
DATEADD(Year,-1,DATEADD(MONTH,-value,@StartingValue)) AS StartDate,
--series starting with today, incremented by a month
DATEADD(MONTH,-value ,@StartingValue) AS EndDate
FROM GENERATE_SERIES(0,10)
)
SELECT *
FROM TimeFrame;
This outputs:
GroupValue StartDate EndDate ----------- ---------- ----------- 0 2025-02-16 2026-02-16 1 2025-01-16 2026-01-16 2 2024-12-16 2025-12-16 3 2024-11-16 2025-11-16 4 2024-10-16 2025-10-16 5 2024-09-16 2025-09-16 6 2024-08-16 2025-08-16 7 2024-07-16 2025-07-16 8 2024-06-16 2025-06-16 9 2024-05-16 2025-05-16 10 2024-04-16 2025-04-16
Or really anything you want
You can really do any step you want, and look back time period you want. It doesn’t need to be simple or even make sense, for that matter. Customers want what they want, and as programmers (while it isn’t wrong to push back a wee bit), our job is to implement what has been requested by the customer.
Get to rolling up your sums
I hope this is useful to someone other than myself one day. I also hope I am not taking someone’s algorithm! This is one of those ideas that I created the basis for 3 years ago, thinking it was really one time need, but as I found myself looking at a few rolling sum needs, I started looking around. Using windowing functions seemed like a cool thing, but even for pretty reasonable sets of real data, there were often a lot of gaps to fill.
Making a table with time ranges and using a join just made sense to me and seems to work great.
Drsql's Database Musings 
Leave a comment