Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Group By with Case Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 7:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 990, Visits: 2,695
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/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


  Post Attachments 
test_table_data.txt (13 views, 1.37 KB)
Post #1394626
Posted Monday, December 10, 2012 8:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 1,783, Visits: 1,921
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

Post #1394657
Posted Monday, December 10, 2012 8:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 990, Visits: 2,695
Wow, that was easy!
Thanks, it gives me what I'm looking for! Appreciated!
Post #1394660
Posted Monday, December 10, 2012 8:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 1,783, Visits: 1,921
no problem, you made it easy for people to help you by posting the script with the CREATE, and INSERT
Post #1394662
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse