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
Advanced Member
Posts: 33
Advanced 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
    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
      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