Calculate Moving Averages using T-SQL in SQL Server

  • Archana

    Old Hand

    Points: 302

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

  • pyt

    SSC Journeyman

    Points: 89

    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: 23648

    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

    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: 186087

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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

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

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