Thanks for the reply. I missed the partition by clause and that has definitely helped -- appreciate you pointing that out.
I thought I had the 3 month rolling average calculation correct, but it looks like I need to shift the frame of the window function by one row and I'm unsure of exactly how to do this.
Here is the logic that I'm currently using:
SELECT Company
,[Revenue Year]
,[Revenue Amount]
,CASE
WHEN COUNT(*) OVER (
PARTITION BY Company ORDER BY Company
,[Revenue Year]
) > 3
THEN AVG([Revenue Amount]) OVER (
PARTITION BY Company ORDER BY Company
,[Revenue Year] ROWS BETWEEN 3 PRECEDING
AND CURRENT ROW
)
ELSE NULL
END ThreeMonthRunningAvg
FROM myTable
ORDER BY 1
,2
This is producing these results:
CompanyRevenue YearRevenue AmountThreeMonthRunningAvg
ABC2012 15654125.71NULL
ABC2013 16336060.44NULL
ABC2014 16316640.49NULL
ABC2015 8286560.46 14148346.775
ABC2016 6599912.46 11884793.4625
What our folks are wanting to see is that when the year is 2015 they want a three year running average of 2012,2013,2014 so it is almost like i'd need to specify something like (ROWS BETWEEN 3 PRECEDING AND CURRENT ROW -1). I know that syntax is invalid, but is there a way to have the avg calculation only include the 3 prior years and not the current row like I have now?
Thanks so much!