We help IT Professionals succeed at work.

Link pay rate to check

BobRosas
BobRosas asked
on
345 Views
Last Modified: 2012-02-13
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
Comment
Watch Question

Author

Commented:
Attached is an example of what I'm getting and what I actually want.
RateChg.xls
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you!  That was very helpful!
CERTIFIED EXPERT

Commented:
You're welcome.  Glad I could help.

 James
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.