Duplicate Rows

Summary

duplicate rows with left outer join

Content

Hello. I need to build a report that returns benefit information for everyone with a column indicating if someone is on a LOA and the dates of the LOA.

When someone is on a LOA, I am getting two rows, one with the LOA information and one without. If I use an inner join for the absence column, I only get results for employees that are on a LOA so I wrote it as a outer join. I have attached the code and an example of the results.

Code Snippet

SELECT DISTINCT
PAPF.PERSON_NUMBER PERSON_NUMBER
,ENROLL.person_ID
, TO_CHAR(ENROLL.ENRT_CVG_STRT_DT , 'MM/DD/YYYY')  EFFECTIVE_START_DATE
, TO_CHAR(ENROLL.ENRT_CVG_THRU_DT , 'MM/DD/YYYY')  EFFECTIVE_END_DATE
, TO_CHAR(ENROLL.LAST_UPDATE_DATE , 'MM/DD/YYYY') LAST_UPDATED_DATE
,PER_EMAIL_ADDRESSES.EMAIL_ADDRESS
,PER_PHONES.SEARCH_PHONE_NUMBER
,PL_TYP.NAME PLAN_TYPE
,plan.NAME PLAN_NAME
,PAAM.ASSIGNMENT_NUMBER  ASSIGN_NUMBER
,PAAM.ASSIGNMENT_STATUS_TYPE  ASSIGN_STATUS
, PA.POSTAL_CODE POSTAL_CODE
,PA.ADDRESS_LINE_1 ADDRESS_LINE_1
,PA.ADDRESS_LINE_2 ADDRESS_LINE_2
,PA.TOWN_OR_CITY CITY
,PA.REGION_3 PROVINCE
, PER_NAME.FIRST_NAME FIRST_NAME
, PER_NAME.LAST_NAME LAST_NAME
,LEG_NAME.NAME LEGAL_EMPLOYER
, TO_CHAR(PPOS.ACTUAL_TERMINATION_DATE  , 'MM/DD/YYYY')  TERMINATION_DATE
, TO_CHAR(PPOS.DATE_START  , 'MM/DD/YYYY')  HIRE_DATE
, ANC_ABSENCE_TYPES_VL.name ABSENCE_TYPE
, TO_CHAR(ANC_PER_ABS_ENTRIES.start_date,'MM/DD/YYYY') ABSENCE_START_DATE
, TO_CHAR(ANC_PER_ABS_ENTRIES.end_date, 'MM/DD/YYYY') ABSENCE_END_DATE
,paam.bargaining_unit_code Bargaining_Unit




FROM
PER_ALL_PEOPLE_F PAPF
LEFT JOIN PER_ADDRESSES_F PA ON PAPF.MAILING_ADDRESS_ID = PA.ADDRESS_ID and TRUNC(SYSDATE) BETWEEN pa.EFFECTIVE_START_DATE AND pa.EFFECTIVE_END_DATE 
INNER JOIN BEN_PRTT_ENRT_RSLT ENROLL ON ENROLL.PERSON_ID = PAPF.PERSON_ID AND ENROLL.prtt_enrt_rslt_stat_cd IS NULL AND ENROLL.sspndd_flag <> 'Y' 
INNER JOIN PER_EMAIL_ADDRESSES ON PAPF.PERSON_ID = PER_EMAIL_ADDRESSES.PERSON_ID 
INNER JOIN PER_PHONES ON PAPF.PRIMARY_PHONE_ID = PER_PHONES.PHONE_ID
INNER JOIN BEN_PL_TYP_F