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 :)
|