This is one of my favorite topics when it comes to writing SQL Queries because it utilizes my favorite data dimension, time.
A rolling window is a virtual window of time from a specific starting point. You can have a rolling window of years, quarters, months, days, hours, minutes, seconds. You get the point. So, in order to have a rolling window you simply need two elements; 1) you need to have a point of reference (one point in time) as the start or end of the rolling window; 2) you need the number of periods to include in your rolling window.
For example, you may want to have a rolling window of quarters. You need the point in time for the start or the end of the quarter. Then you need a series of time segments (start and end datetime) between which your data attributes will fall between. Optimally, the primary key for this set has a virtual meaning such as a number for that specific period. Using a number you can order ther results by the primary key.
Once you have your rolling window period data set it can be joined to any data where the timestamp of the data falls between the start and end values for each period. You can use this rolling window as a filter, since it won’t return any data not falling between the parameters. You can aggregate data or return details…that is completely up to the needs of the result set.
The best part of a rolling window query is that it works with most report tools even when presenting data from different periods because the primary key for the periods is logically defined, not physical.
Do you have other data filtering, grouping or sorting tips you like to use? Share them as part of this conversation, or drop an email to Btaylor@sswug.org.
Cheers,
Ben