crowegreg - Monday, March 27, 2017 4:05 PM
Average of the sum? That is just the sum.
I assume that you are looking for the average of both critical and high together. That can be achieved by simply removing d.[remedy alg] from the final query
WITH cteRecentWeek AS (
SELECT [fy year], [month name], [report week]
, rn = ROW_NUMBER() OVER (ORDER BY [end date] DESC)
FROM #Data
)
SELECT d.[fy year], d.[month name], d.[report week]
, [avg_outage_hr_decimal_calc] = AVG(d.[outage_hr_decimal_calc])
FROM cteRecentWeek AS rw
INNER JOIN #Data AS d
ON rw.[fy year] = d.[fy year]
AND rw.[month name] = d.[month name]
AND rw.[report week] = d.[report week]
WHERE rw.rn = 1
AND d.[remedy alg] IN ('Critical', 'High')
GROUP BY d.[fy year], d.[month name], d.[report week];