Post reply

Rolling Averages

  • December 15, 2008 at 7:20 pm

    #204043

    Hi Frends

    I have been asked to calculate the Rolling avg for a set of data

    Here is my sample data

    Year Month Cost

    2000 1 $2300

    2000 2 $1222

    .

    .

    .

    2005 1 $12232

    2005 2 $11111

    and so on

    Now I need to calculate the rolling average for 5 Yrsbased on this cost..

    I have got a vague idea of the rolling average concept like if the previous year's cost is null we put 0 and if there is a cost we add the previous and the current cost figure. I know I might be completely wrong.

    Any ideas guys how to write a query to calculate the rolling avg??

    Thanks

  • December 15, 2008 at 8:05 pm

    #912897

    Well there are lots of kinds of rolling (or moving) averages, but assuming that you mean a simple moving average of N values, then it should be something like this:

    Declare @N int

    Set @N = 6

    Select Year, Month, Cost

    , (Select SUM(Cost)/CAST(Count(Cost) as Money)

    From (

    Select Top (@N) Cost

    From YourTable T2

    Where T2.Year < T.Year

    Or T2.Year = T.Year And T2.Month <= T.Month) A

    ) as RollingAvg

    From YourTable T

    Order By Year, Month

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

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

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