0

I have a table with the month, identifier and revenue for that month. What I would like to do is create a new field that for every row is the average revenue of the previous three months i.e. 'Prev_3month_Average'. Does anyone know a good sql function for this rather than joining the table onto itself three times?

E.g

    Month   ID  Revenue Prev_3month_Average
    201601  123 5       null
    201602  123 10      null
    201603  123 8       null
    201604  123 10      7.666666667
    201605  123 5       9.333333333

Thanks in advance!

Drew
  • 24,851
  • 10
  • 43
  • 78
user6856477
  • 9
  • 1
  • LAG() function may help: https://msdn.microsoft.com/en-us/library/hh231256.aspx – Anton Sep 21 '16 at 04:42
  • 1
    or http://stackoverflow.com/questions/24021819/using-the-lag-function-to-find-a-moving-average-in-sql – Anton Sep 21 '16 at 05:00
  • Possible duplicate of [T-SQL calculate moving average](http://stackoverflow.com/questions/26618353/t-sql-calculate-moving-average) – Tab Alleman Sep 21 '16 at 13:39

0 Answers0