October 3, 2006 at 8:35 am
I'm trying to combine multiple records that contain numerous different charge codes into 1 record that sums up total hours and dollars by charge code.
Here's a sample
dept job code charge code hrs worked dollars
1111 8110 NPO 40.00 1600.00
1111 8110 REG E 40.00 500.00
1111 8110 REG N 60.00 600.00
1111 8110 PRO 20.00 500.00
1111 8110 OVT 30.00 400.00
1111 8110 HOL 40.00 300.00
1111 8110 SICK 8.00 300.00
1111 8110 JURY 8.00 300.00
1111 8110 VAC 40.00 300.00
In my example the first for records are regular hrs, the last 5 are other hrs...
So the charge code is different even though they are classified as regular or other hours...
I want to combine (sum) all these records by code into one line that looks like this:
dept, job code, reg hrs, other hrs, reg $$, other $$$
1111, 8110, 160.00, 126.00, 3200.00, 1600.00
How would I do that. I'm trying to figure out a case statement..
Thanks,
October 3, 2006 at 8:46 am
Try this out
SELECT [Dept],
[Job Code],
SUM(CASE WHEN [Charge Code] IN ('NPO','REG E','HOL','VAC') THEN [Hrs Worked] ELSE 0 END) AS RegHours,
SUM(CASE WHEN [Charge Code] IN ('NPO','REG E','HOL','VAC') THEN [Dollars] ELSE 0 END) AS RegDollars,
SUM(CASE WHEN [Charge Code] IN ('REG N','PRO','OVT','SICK','JURY') THEN [Hrs Worked] ELSE 0 END) AS OthHours,
SUM(CASE WHEN [Charge Code] IN ('REG N','PRO','OVT','SICK','JURY') THEN [Dollars] ELSE 0 END) AS OthDollars
FROM tbl
GROUP BY [Dept],[Job Code]
Ram
October 3, 2006 at 11:41 am
Ram,
First off, thanks for the help. The only problem I still have in your example is when the code meets the "IN" criteria, I want it to sum up all the hours into either the regular or other bucket. Your example sums up hours by each code (REG, REG E, REG N), I tried to put another sum in your example, but it was a no go. Does that make sense?
October 3, 2006 at 11:58 am
As I see it the query is good you just need to change the *in* as you needed.
ex:
SELECT [Dept],
[Job Code],
SUM(CASE WHEN [Charge Code] IN ('NPO','REG E','REG N','PRO') THEN [Hrs Worked] ELSE 0 END) AS RegHours,
SUM(CASE WHEN [Charge Code] IN ('NPO','REG E','REG N','PRO') THEN [Dollars] ELSE 0 END) AS RegDollars,
SUM(CASE WHEN [Charge Code] IN ('OVT','HOL','SICK','JURY','VAC') THEN [Hrs Worked] ELSE 0 END) AS OthHours,
SUM(CASE WHEN [Charge Code] IN ('OVT','HOL','SICK','JURY','VAC') THEN [Dollars] ELSE 0 END) AS OthDollars
FROM tbl
GROUP BY [Dept],[Job Code]
* Noel
October 3, 2006 at 12:16 pm
You may want to make a reference table for the possible code selections, (or if you have one, add another column) and you can select from that table inside the () parameters of the IN clause...
I wasn't born stupid - I had to study.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply