InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 07:52:04 GMT20http://www.sqlservercentral.com/Forums/Topic1394626-392-1.aspxno problem, you made it easy for people to help you by posting the script with the CREATE, and INSERTMon, 10 Dec 2012 08:43:39 GMTChris Harshmanhttp://www.sqlservercentral.com/Forums/Topic1394626-392-1.aspxWow, that was easy!Thanks, it gives me what I'm looking for! Appreciated!Mon, 10 Dec 2012 08:39:05 GMTrothjhttp://www.sqlservercentral.com/Forums/Topic1394626-392-1.aspxif you don't want to group by dept, then the SUM needs to be outside the CASE structure, like this:[code="sql"]DECLARE @FYStartDate AS DATESET @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[/code]Mon, 10 Dec 2012 08:35:02 GMTChris Harshmanhttp://www.sqlservercentral.com/Forums/Topic1394626-392-1.aspxI'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:[code="plain"]DECLARE @FYStartDate AS DATESET @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[/code]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:[code="plain"]07/14/2012 5.6907/28/2012 6.1208/11/2012 5.8008/25/2012 5.2109/08/2012 5.9009/22/2012 5.8910/06/2012 6.0810/20/2012 6.26[/code]Mon, 10 Dec 2012 07:42:47 GMTrothj