• if you don't want to group by dept, then the SUM needs to be outside the CASE structure, like this:

    DECLARE @FYStartDate AS DATE

    SET @FYStartDate = '2012-07-01'

    SELECT PPDate, --Dept,

    SUM(CASE WHEN Dept in ('8310', '8330', '8380', '8500', '9360', '9550', '9610') THEN [Value]/520 --measured quarterly

    WHEN Dept = '7120' THEN [value] --hard coded

    ELSE Value/80 --measured bi-weekly

    END) AS 'Earned FTE'

    FROM #trends

    WHERE Dept not in ('6570','6580','6645','7221','9831') --don't include these dept's

    AND PPDate >= @FYStartDate

    GROUP BY PPDate--, Dept

    ORDER BY PPDate--, Dept