DECLARE @FYStartDate  AS DATE
SET @FYStartDate = '2012-07-01'

SELECT PPDate, Dept,

	CASE 
		When Dept in  ('8310', '8330', '8380', '8500', '9360', '9550', '9610') THEN
		Sum([Value]/520)  --measured quarterly
		
		WHEN Dept = '7120' THEN 
		 Sum([value])     --hard coded 
		
		ELSE 
		Sum(Value/80)  --measured bi-weekly
	END AS 'Earned FTE'
		
  FROM #trends
   
  WHERE 	Dept not in ('6570','6580','6645','7221','9831') and  --don't include these dept's
	PPDate >= @FYStartDate
	
  Group By PPDate, Dept 
  Order By PPDate, Dept

07/14/2012	5.69
07/28/2012	6.12
08/11/2012	5.80
08/25/2012	5.21
09/08/2012	5.90
09/22/2012	5.89
10/06/2012	6.08
10/20/2012	6.26

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