troubleshooting Question

Link pay rate to check

Avatar of BobRosas
BobRosas asked on
SSRS
4 Comments1 Solution350 ViewsLast Modified:
The pay rate does not show on the check stub and I'm trying to write a report that shows hours worked but I need to link it with the correct pay rate.  The code below repeats each record based on how many rate changes they've had.  I just want the rate for that pay period.  Would you please tell me how to fix the way I have it linked?
Since an employee is paid 10 days after they work, I subtract 10 days from the pay check date and then try to link that date to the effective date of their wage.


SELECT DISTINCT
                         tblPaCheckHist.EmployeeId, vw_HrIndividuals.State, tblPaCheckHist.EmployeeName, tblPaCheckHist.DepartmentId, tblPaCheckHist.CheckDate,
                         tblPaCheckHist.HoursWorked, tblPaCheckHist.GrossPay, vw_HrIndividuals.LaborClass, tblHRIndSalary.HourlyRate, tblPaCheckHistEmplrCost.DeductionCode,
                         SUM(tblPaCheckHistEmplrCost.Amount) AS Amount, tblHRIndSalary.EffectiveDate
FROM            tblPaCheckHist INNER JOIN
                         vw_HrIndividuals ON tblPaCheckHist.EmployeeId = vw_HrIndividuals.EmployeeID INNER JOIN
                         tblPaCheckHistEmplrCost ON tblPaCheckHist.PostRun = tblPaCheckHistEmplrCost.PostRun AND
                         tblPaCheckHist.SequenceNumber = tblPaCheckHistEmplrCost.SequenceNumber INNER JOIN
                         tblHRIndSalary ON vw_HrIndividuals.IndID = tblHRIndSalary.IndID AND DATEADD(d, - 10, tblPaCheckHist.CheckDate) > tblHRIndSalary.EffectiveDate
WHERE        (tblPaCheckHist.HoursWorked <> 0) AND (DATEADD(d, - 15, tblPaCheckHist.CheckDate) BETWEEN @From AND @To) AND
                         (tblPaCheckHistEmplrCost.DeductionCode <> 'MER')
GROUP BY tblPaCheckHist.EmployeeId, vw_HrIndividuals.State, tblPaCheckHist.EmployeeName, tblPaCheckHist.CheckDate, tblPaCheckHist.DepartmentId,
                         tblPaCheckHist.HoursWorked, tblPaCheckHist.GrossPay, vw_HrIndividuals.LaborClass, tblHRIndSalary.HourlyRate, tblPaCheckHistEmplrCost.DeductionCode,
                         tblHRIndSalary.EffectiveDate
ORDER BY tblPaCheckHist.EmployeeName, tblPaCheckHist.CheckDate
ASKER CERTIFIED SOLUTION
James0628

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 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 4 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