Calculate Moving Averages using T-SQL in SQL Server

Question

Calculate Moving Averages using T-SQL in SQL Server

Archana

Old Hand

Points: 332
More actions
February 4, 2020 at 12:00 am

#3715356

Comments posted to this topic are about the item Calculate Moving Averages using T-SQL in SQL Server

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pyt SSC Journeyman Points: 90 More actions · Answer 1

Thanks for a good walk through, but you have holes in the data set (weekends etc), so it is rather moving average of last 7 and next 7 observations.

Is there any way to apply a date range rather than the number of observations? I had this issue the other day, and had to revert to sub selects for calculating value of sales for the last 365 days compared to the individual sales. So for that test period there might be 0 records or 1000's of records.

Cheers

Peter

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 2

pyt wrote:

Is there any way to apply a date range rather than the number of observations? I had this issue the other day, and had to revert to sub selects for calculating value of sales for the last 365 days compared to the individual sales. So for that test period there might be 0 records or 1000's of records.

The easy way is to simply join a calendar table to the observations table, with the observations table LEFT JOIN'd to the calendar table. The calendar table will "prop open" the missing dates, making sure the "7 PRECEDING and 7 FOLLOWING" are based on sequential calendar dates rather than sequential observations.

steve.worley Newbie Points: 2 More actions · Answer 3

Good article, but the data in the Figure 5 chart cannot be correct. The last data point shows the 15 day average equal to the highest data point for the of the last 15, if it is an average then it cannot be equal to the highest value when there are 14 other values that are lower.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

This article, along with this one from ten years ago, should be enough to assist in solving most Moving Average problems. Thanks for posting.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden