PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/28/2016 6:42 PM by  Jeff Shumate
Crystal Report - Help Adding Fields to Existing Query
 1 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Chris-T
Application Architect
Private
New Member
(1 points)
New Member
Posts:1


Send Message:

--
11/28/2016 2:26 PM
    Good afternoon,

    Let me start by stating that while I'm a DB person at my local hospital, I have never worked with Lawson data before. I've been requested to create an employee history report and thanks to the talented folks here, I have very nearly what I need.

    Would someone be able to assist me in adding two fields to this query - I'm looking to add the employee's prior supervisor and current supervisor (much like the current position and prior position).

    I believe the data I'm looking for is in the PAEMPPOS table, but I'm not entirely certain how to add it to this existing query.

    SELECT *
    FROM (
    SELECT PAH.COMPANY
    ,EMP.PROCESS_LEVEL
    ,PAH.EMPLOYEE
    ,PAH.EFFECT_DATE
    ,PAH.ACTION_CODE
    ,PAH.REASON_01
    ,PAH.REASON_02
    ,EMP.LAST_NAME
    ,EMP.FIRST_NAME
    ,EMP.MIDDLE_INIT
    ,PEM.SEX
    ,PEM.EEO_CLASS
    ,EMP.POSITION
    ,COALESCE((
    SELECT A_VALUE
    FROM (
    SELECT COMPANY
    ,EMPLOYEE
    ,BEG_DATE
    ,A_VALUE
    FROM PRODLAW.HRHISTORY HRH
    WHERE HRH.FLD_NBR = 126
    ORDER BY BEG_DATE DESC
    ) CUR_POS
    WHERE CUR_POS.COMPANY = PAH.COMPANY
    AND CUR_POS.EMPLOYEE = PAH.EMPLOYEE
    AND CUR_POS.BEG_DATE <= PAH.EFFECT_DATE
    AND rownum = 1
    ), ' ') AS CUR_POSITION
    ,COALESCE((
    SELECT A_VALUE
    FROM (
    SELECT COMPANY
    ,EMPLOYEE
    ,BEG_DATE
    ,A_VALUE
    FROM PRODLAW.HRHISTORY HRH
    WHERE HRH.FLD_NBR = 126
    ORDER BY BEG_DATE DESC
    ) PREV_POSITION
    WHERE PREV_POSITION.COMPANY = PAH.COMPANY
    AND PREV_POSITION.EMPLOYEE = PAH.EMPLOYEE
    AND PREV_POSITION.BEG_DATE < PAH.EFFECT_DATE
    AND rownum = 1
    ), ' ') AS PREV_POSITION
    ,COALESCE((
    SELECT A_VALUE
    FROM (
    SELECT COMPANY
    ,EMPLOYEE
    ,BEG_DATE
    ,A_VALUE
    FROM PRODLAW.HRHISTORY HRH
    WHERE HRH.FLD_NBR = 20
    ORDER BY BEG_DATE DESC
    ) CUR_STATUS
    WHERE CUR_STATUS.COMPANY = PAH.COMPANY
    AND CUR_STATUS.EMPLOYEE = PAH.EMPLOYEE
    AND CUR_STATUS.BEG_DATE <= PAH.EFFECT_DATE
    AND rownum = 1
    ), ' ') AS CUR_STATUS
    ,COALESCE((
    SELECT A_VALUE
    FROM (
    SELECT COMPANY
    ,EMPLOYEE
    ,BEG_DATE
    ,A_VALUE
    FROM PRODLAW.HRHISTORY HRH
    WHERE HRH.FLD_NBR = 20
    ORDER BY BEG_DATE DESC
    ) PREV_STATUS
    WHERE PREV_STATUS.COMPANY = PAH.COMPANY
    AND PREV_STATUS.EMPLOYEE = PAH.EMPLOYEE
    AND PREV_STATUS.BEG_DATE < PAH.EFFECT_DATE
    AND rownum = 1
    ), ' ') AS PREV_STATUS
    FROM PRODLAW.PERSACTHST PAH
    LEFT OUTER JOIN PRODLAW.EMPLOYEE EMP ON (EMP.COMPANY = PAH.COMPANY)
    AND (EMP.EMPLOYEE = PAH.EMPLOYEE)
    LEFT OUTER JOIN PRODLAW.PAEMPLOYEE PEM ON (PEM.COMPANY = PAH.COMPANY)
    AND (PEM.EMPLOYEE = PAH.EMPLOYEE)
    WHERE (PAH.COMPANY = 1000)
    AND (PAH.EFFECT_DATE BETWEEN to_date('2000-01-01', 'yyyy-mm-dd')
    AND to_date('2016-12-31', 'yyyy-mm-dd')
    )
    )
    WHERE CUR_POSITION <> PREV_POSITION
    OR CUR_STATUS <> PREV_STATUS;

    Thanks in advance for helping out a poor soul :)
    Jeff Shumate
    Private
    Private
    Advanced Member
    (87 points)
    Advanced Member
    Posts:29


    Send Message:

    --
    11/28/2016 6:42 PM
    Our setup has Supervisor easily found in the EMPLOYEE table, but you could grab from PAEMPPOS if you have it set up, and your EEs can have multiple positions. I believe to get the prior supervisor, you will have to look at HRHISTORY, and HR10 will have to be set up to capture changes to the supervisor field.
    You are not authorized to post a reply.