troubleshooting Question

Sum field based on another field.

Avatar of BobRosas
BobRosas asked on
SSRS
2 Comments1 Solution361 ViewsLast Modified:
I want to total HoursWorked if the "EarningCode" is "REG", "HOL" or "PTO" but I want the HoursWorked separate for "OVT".  With the following code I was able to get the Earning Code to change but now if won't sum the HoursWorked.  I'm using Report Services with SQL code.  The results I have look like this...
1/10/2012 12:00:00 AM   AS0142   JENS1787   8.000000000   0.000000000   REG   0.000000000
1/10/2012 12:00:00 AM   AS0142   JENS1787  77.630000000  0.000000000        REG   0.000000000
1/10/2012 12:00:00 AM   AS0142   JENS1787   0.1200000000  0.000000000        OVT   0.000000000

What I want is this...
1/10/2012 12:00:00 AM   AS0142   JENS1787  85.630000000  0.000000000        REG   0.000000000
1/10/2012 12:00:00 AM   AS0142   JENS1787   0.1200000000  0.000000000        OVT   0.000000000


SELECT        tblPaCheckHist.CheckDate, tblPaCheckHist.DepartmentId, tblPaCheckHist.EmployeeId, SUM(tblPaCheckHistEarn.HoursWorked) AS HoursWorked,
                         CASE WHEN LaborClass = 'CL' THEN tblPaCheckHistEarn.HourlyRate ELSE 0 END AS RatePaid,
                         CASE WHEN EarningCode = 'HOL' THEN 'REG' WHEN EarningCode = 'PTO' THEN 'REG' ELSE EarningCode END AS EarningCode,
                         CASE WHEN LaborClass = 'CL' THEN SUM(tblPaCheckHistEarn.EarningsAmount) ELSE 0 END AS Earnings
FROM            tblPaCheckHistEarn INNER JOIN
                         tblPaCheckHist ON tblPaCheckHistEarn.PostRun = tblPaCheckHist.PostRun AND tblPaCheckHistEarn.SequenceNumber = tblPaCheckHist.SequenceNumber
WHERE        (DATEADD(d, - 15, tblPaCheckHist.CheckDate) BETWEEN @From AND @To) AND (tblPaCheckHist.EmployeeId = 'JENS1787')
GROUP BY tblPaCheckHist.CheckDate, tblPaCheckHist.DepartmentId, tblPaCheckHist.EmployeeId, tblPaCheckHist.EmployeeName, tblPaCheckHistEarn.HourlyRate,
                         tblPaCheckHistEarn.LaborClass, tblPaCheckHistEarn.EarningCode
ORDER BY tblPaCheckHist.DepartmentId, tblPaCheckHist.EmployeeName
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros