I'm trying to query for Group By with a Case.
I want a total per Date but there are some Departments that have a different rule for calculating the total.
I believe I need to move the Dept into the Case statement but I'm having difficulty with the syntax.
Here is what I currently have:
DECLARE @FYStartDate AS DATE
SET @FYStartDate = '2012-07-01'
SELECT PPDate, Dept,
When Dept in ('8310', '8330', '8380', '8500', '9360', '9550', '9610') THEN
Sum([Value]/520) --measured quarterly
WHEN Dept = '7120' THEN
Sum([value]) --hard coded
Sum(Value/80) --measured bi-weekly
END AS 'Earned FTE'
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
When I try removing the Dept from the SELECT/Group By I get the error:
Column DEPT is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Thanks for any insight.
Here's the table/data (attached)
This is my goal: