Link to home
Start Free TrialLog in
Avatar of BobRosas
BobRosas

asked on

Link pay rate to check

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
Avatar of BobRosas
BobRosas

ASKER

Attached is an example of what I'm getting and what I actually want.
RateChg.xls
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you!  That was very helpful!
You're welcome.  Glad I could help.

 James