none
Moving average calculation

    Domanda

  • Dear All

    I have spent hours looking for a solution with little success

     

    I am trying to do a moving average calculation of sales data.

    Tha data looks like this

     

    Period        sales_value      Moving average

    1                 100                       100                 

    2                 200                        150          (formula is period 1 sales + period 2 sales divide by two) 

    3                 300                       200            average is to be over six months in the long run

    4                 400                      250

    5                 500                     300

    6                 600                     350

    7                 700                     450  after period six it becomes a rolling average, meaning only take the last six months into the moving average calc

     

    Any help on this problem is greatfully accepted

     

    Regards

     

     

    martedì 19 luglio 2011 00:43

Tutte le risposte

  • How big is your table? In the next version of SQL Server (Denali) this problem can be easily solved. In the current SQL Server version it's not easy. The SET-based solution I will show most likely will not perform too well.

     

     

    select T.Period, T.Sales_Value, MA.AvgSales as [Moving Average]
    
    from Sales T
    
    CROSS APPLY (select sum(Sales_Value)/count(*) from Sales S1 where S1.Period between S.Period - 6 and S.Period) MA(AvgSales)
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    martedì 19 luglio 2011 01:11
  • Naomi

    Thanks but it didnt work, any other suggestions

    regards

     

    martedì 19 luglio 2011 01:56
  • Can you please tell exactly what didn't work? Also, please post your table structure and insert statements as a runnable script, so I will be able to reproduce and test the solution.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    martedì 19 luglio 2011 03:02
  • Naomi

     

    Please see table below, what I need to be able to do is to calculate a moving/rolling average. Meaning the average for period 1 - 6, then 2 - 7, 3 - 6 and so on using the sales figure. In excel I have no issues but in sql I need your help. Hoep this makes sense now

     

    Row Year Period SalesAmt

    1 2001 12 -51074250

    2 2002 1 -4075767

    3 2002 2 -4559238

    4 2002 3 -4394685

    5 2002 4 -5576792

    6 2002 5 -5143799

    7 2002 6 -4444527

    8 2002 7 -4713246

    9 2002 8 -3844052

    10 2002 9 -4572032

    11 2002 10 -3944922

    12 2002 11 -4245074

    13 2002 12 -3751900

    14 2003 1 -4202379

     

    martedì 19 luglio 2011 03:50
  • Please post as CREATE TABLE and INSERT statements so I will not need to re-create them by hand.

    In any case, it's very late right now, I will re-visit this thread tomorrow.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    martedì 19 luglio 2011 04:23