• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Human Capital Management Lawson S3 HR/Payroll/Benefits

Crystal Formula

 2 Replies
 0 Subscribed to this topic
 68 Subscribed to this forum
Sort:
Author
Messages
barb
New Member Send Private Message
Posts: 0
New Member
3/17/2010 3:46 PM
Hello Everyone,

I am trying to have the senoir date (our re-hire date) salary listed on the report. I can't figuire out how to do this. Can someone send me the formula.

since we are on step and grade I am using the PAEMPPOS table and the senior date is on the PAEMPLOYEE table.

Thanks for your help
Chris Martin
Veteran Member Send Private Message
Posts: 277
Veteran Member
3/17/2010 5:22 PM
If your base table is PAEMPPOS, you can join to PAEMPLOYEE on COMPANY/EMPLOYEE.
Chris Martin
Veteran Member Send Private Message
Posts: 277
Veteran Member
3/17/2010 5:48 PM
Sorry, I missed the word "salary" from your original post. :)

I would probably do a nested select to pull in the rehire date salary. I haven't tested this so my logic might be flawed.

For example:

SELECT pem.*,
(SELECT pep.pay_rate
FROM paemppos pep
WHERE pep.company = pem.company
AND pep.employee = pem.empoloyee
AND pep.effect_date = (SELECT MAX(pep2.effect_date)
FROM paemppos pep2
WHERE pep2.company = pep.company
AND pep2.emnployee = pep.employee
AND pep2.effect_date < pem.senior_date)) AS rehire_salary
FROM paemployee