December 10, 2012 at 7:42 am
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,
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
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:
07/14/20125.69
07/28/20126.12
08/11/20125.80
08/25/20125.21
09/08/20125.90
09/22/20125.89
10/06/20126.08
10/20/20126.26
December 10, 2012 at 8:35 am
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
December 10, 2012 at 8:39 am
Wow, that was easy!
Thanks, it gives me what I'm looking for! Appreciated!
December 10, 2012 at 8:43 am
no problem, you made it easy for people to help you by posting the script with the CREATE, and INSERT
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply