• crowegreg - Monday, March 27, 2017 4:05 PM

    That works great. I only have 1 change. I need the average of the sum of the 'critical' & 'high' records.

    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];