First option:

Make 1st view and in that view calculate COUNT_STATE. Then write the 2nd view on top of the 1st view and here you can do your GROUP by clause...

Stephen,

Here is my suggestion, in this solution you will get the moving avarage from current month backwards for 12 months.

select ReportingType, GroupingField,

    SUM(CASE month(getdate()) - CalendarPeriod WHEN 0 THEN...

Jeniffer:

If you could post your table schema and a few rows of data, then i can write a view on it for the desired result, though this looks a bit complicated but...

Chris,

I went thru your SP and i joined all the tables and created a view instead.

Where every the temp table is used read from this view instead with the appropriate date...

Chris,

I think the stored procedure is very inefficient in whatever it is doing. This usually happens when the functionality grows over time and lot of patches are put to it....