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.EmployeeNam e, tblPaCheckHist.DepartmentI d, tblPaCheckHist.CheckDate,
tblPaCheckHist.HoursWorked , tblPaCheckHist.GrossPay, vw_HrIndividuals.LaborClas s, tblHRIndSalary.HourlyRate, tblPaCheckHistEmplrCost.De ductionCod e,
SUM(tblPaCheckHistEmplrCos t.Amount) AS Amount, tblHRIndSalary.EffectiveDa te
FROM tblPaCheckHist INNER JOIN
vw_HrIndividuals ON tblPaCheckHist.EmployeeId = vw_HrIndividuals.EmployeeI D INNER JOIN
tblPaCheckHistEmplrCost ON tblPaCheckHist.PostRun = tblPaCheckHistEmplrCost.Po stRun AND
tblPaCheckHist.SequenceNum ber = tblPaCheckHistEmplrCost.Se quenceNumb er INNER JOIN
tblHRIndSalary ON vw_HrIndividuals.IndID = tblHRIndSalary.IndID AND DATEADD(d, - 10, tblPaCheckHist.CheckDate) > tblHRIndSalary.EffectiveDa te
WHERE (tblPaCheckHist.HoursWorke d <> 0) AND (DATEADD(d, - 15, tblPaCheckHist.CheckDate) BETWEEN @From AND @To) AND
(tblPaCheckHistEmplrCost.D eductionCo de <> 'MER')
GROUP BY tblPaCheckHist.EmployeeId, vw_HrIndividuals.State, tblPaCheckHist.EmployeeNam e, tblPaCheckHist.CheckDate, tblPaCheckHist.DepartmentI d,
tblPaCheckHist.HoursWorked , tblPaCheckHist.GrossPay, vw_HrIndividuals.LaborClas s, tblHRIndSalary.HourlyRate, tblPaCheckHistEmplrCost.De ductionCod e,
tblHRIndSalary.EffectiveDa te
ORDER BY tblPaCheckHist.EmployeeNam e, tblPaCheckHist.CheckDate
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,
tblPaCheckHist.HoursWorked
SUM(tblPaCheckHistEmplrCos
FROM tblPaCheckHist INNER JOIN
vw_HrIndividuals ON tblPaCheckHist.EmployeeId = vw_HrIndividuals.EmployeeI
tblPaCheckHistEmplrCost ON tblPaCheckHist.PostRun = tblPaCheckHistEmplrCost.Po
tblPaCheckHist.SequenceNum
tblHRIndSalary ON vw_HrIndividuals.IndID = tblHRIndSalary.IndID AND DATEADD(d, - 10, tblPaCheckHist.CheckDate) > tblHRIndSalary.EffectiveDa
WHERE (tblPaCheckHist.HoursWorke
(tblPaCheckHistEmplrCost.D
GROUP BY tblPaCheckHist.EmployeeId,
tblPaCheckHist.HoursWorked
tblHRIndSalary.EffectiveDa
ORDER BY tblPaCheckHist.EmployeeNam
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! That was very helpful!
You're welcome. Glad I could help.
James
James
ASKER
RateChg.xls