Moving average calculation

Question

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

 

 

Answer 1

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

Answer 2

Naomi

Thanks but it didnt work, any other suggestions

regards

 

Answer 3

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

Answer 4

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

 

Answer 5

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