troubleshooting Question

Link pay rate to check

Avatar of BobRosas
BobRosas asked on
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.

                         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,
ORDER BY tblPaCheckHist.EmployeeName, tblPaCheckHist.CheckDate

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