Trying to Group Sets of Hours
-
I am trying to create a table that shows Indirect Hours for each employee. These hours are grouped into buckets according to the Indirect Code.
Using this code to create the source table:
--===== If the test table already exists, drop itIF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
Dept varchar(10),
EmpNum varchar(10),
IndHours decimal(21,3),
IndCode varchar(10)
)
--===== Insert the test data into the test table
INSERT INTO #mytable
(Dept, EmpNum, IndHours, IndCode)
SELECT '010', ' 100920', 0.833, 'APP' UNION ALL
SELECT '010', ' 100920', 0.017, 'SS' UNION ALL
SELECT '010', ' 100920', 0.233, 'CLN' UNION ALL
SELECT '010', ' 100920', 0.067, 'APP' UNION ALL
SELECT '010', ' 100920', 0.917, 'APP' UNION ALL
SELECT '010', ' 100920', 0.533, 'APP' UNION ALL
SELECT '010', ' 100920', 0.250, 'LB' UNION ALL
SELECT '010', ' 100920', 0.383, 'APP' UNION ALL
SELECT '010', ' 100920', 2.117, 'APP' UNION ALL
SELECT '010', ' 100920', 0.500, 'LB' UNION ALL
SELECT '010', ' 100920', 0.633, 'APP' UNION ALL
SELECT '010', ' 100920', 0.617, 'APP' UNION ALL
SELECT '010', ' 100920', 0.250, 'LB' UNION ALL
SELECT '010', ' 100920', 0.100, 'APP' UNION ALL
SELECT '010', ' 100920', 0.867, 'APP' UNION ALL
SELECT '010', ' 100920', 0.000, 'SS' UNION ALL
SELECT '010', ' 100920', 1.033, 'APP'
I am trying to create a table that looks like this:
Dept EmpNum APP SS CLN LB
---- ------ ----- ----- ----- -----
010 100920 8.100 0.017 0.233 1.000
Can someone help me figure out an efficient way to do this? The real table is very large and has a lot more Indirect Codes. I have come up with this:
SELECT DISTINCT Dept, #mytable.EmpNum, APP.IndHours APP, SS.IndHours SS, CLN.IndHours CLN, LB.IndHours LBFROM #mytable
inner join (SELECT EmpNum, SUM(IndHours) IndHours FROM #mytable WHERE indcode = 'APP' GROUP BY EmpNum) APP ON #mytable.EmpNum = APP.EmpNum
inner join (SELECT EmpNum, SUM(IndHours) IndHours FROM #mytable WHERE indcode = 'SS' GROUP BY EmpNum) SS ON #mytable.EmpNum = SS.EmpNum
inner join (SELECT EmpNum, SUM(IndHours) IndHours FROM #mytable WHERE indcode = 'CLN' GROUP BY EmpNum) CLN ON #mytable.EmpNum = CLN.EmpNum
inner join (SELECT EmpNum, SUM(IndHours) IndHours FROM #mytable WHERE indcode = 'LB' GROUP BY EmpNum) LB ON #mytable.EmpNum = LB.EmpNum
But I have a feeling that there may be a more efficient way to run it.
Thanks for any help you can give me.
Steve
-
SELECT Dept,EmpNum,
SUM(CASE WHEN IndCode='APP' THEN IndHours ELSE 0 END) AS APP,
SUM(CASE WHEN IndCode='SS' THEN IndHours ELSE 0 END) AS SS,
SUM(CASE WHEN IndCode='CLN' THEN IndHours ELSE 0 END) AS CLN,
SUM(CASE WHEN IndCode='LB' THEN IndHours ELSE 0 END) AS LB
FROM #mytable
GROUP BY Dept,EmpNum
ORDER BY Dept,EmpNum;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark, I am slapping myself in the forehead right now! I had been trying to get this to work, but I was doing it like this:
SELECT Dept,EmpNum,(CASE WHEN IndCode='APP' THEN SUM(IndHours) ELSE 0 END) AS APP,
(CASE WHEN IndCode='SS' THEN SUM(IndHours) ELSE 0 END) AS SS,
(CASE WHEN IndCode='CLN' THEN SUM(IndHours) ELSE 0 END) AS CLN,
(CASE WHEN IndCode='LB' THEN SUM(IndHours) ELSE 0 END) AS LB
FROM #mytable
GROUP BY Dept,EmpNum,IndCode
ORDER BY Dept,EmpNum,IndCode;
Thank you very much. I guess sometimes we can't see the forest for the trees.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply