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!