I have written the below code -
select
PAPF.PERSON_NUMBER,
BP.NAME BENEFIT_PLAN,
BBR.BENEFIT_RELATION_NAME,
Round(BPER.BNFT_AMT, 2) * 100 COVERAGE_AMOUNT,
TO_CHAR(BPER.ENRT_CVG_STRT_DT, 'YYYYMMDD') ENROLMENTCOVSTARTDATE
TO_CHAR(BPER.ENRT_CVG_THRU_DT, 'YYYYMMDD') ENROLMENTCOVSENDDATE
FROM PER_ALL_PEOPLE_F PAPF,
BEN_PRTT_ENRT_RSLT BPER,
BEN_PL_F BP,
BEN_BENEFIT_RELATIONS_F BBR
WHERE PAPF.PERSON_ID = BPER.PERSON_ID
AND BPER.PL_ID = BP.PL_ID
AND BBR.PERSON_ID = PAPF.PERSON_ID
AND BBR.BENEFIT_RELATION_NAME = 'DFLT'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN BP.EFFECTIVE_START_DATE AND BP.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN BBR.EFFECTIVE_START_DATE AND BBR.EFFECTIVE_END_DATE
This will give me an output like -
PErson_number BENEFIT_PLAN COVERAGE_AMOUNT ENROLMENTCOVSTARTDATE ENROLMENTCOVSENDDATE
1010 US Basic PLAN 20000 20200901 20201020
1010 US Basic PLAN 20000 20201021
1011 Us Spouse PLAN 160000 20200901 20201020
1011 Us Spouse PLAN 160000 20201021 47121231
I want to retrive only the max of the ENROLMENTCOVSTARTDATE for each person. The expected output should be -
PErson_number BENEFIT_PLAN COVERAGE_AMOUNT ENROLMENTCOVSTARTDATE ENROLMENTCOVSENDDATE
1010 US Basic PLAN 20000 20201021
1011 Us Spouse PLAN 160000 20201021 20201220
how can i use Max in the main query for this ?
First rank the rows by their ENRT_CVG_STRT_DT per person (assumed identified by PAPF.PERSON_NUMBER). Then filter on rows that are top.
select person_number,
name,
BENEFIT_PLAN,
BENEFIT_RELATION_NAME,
COVERAGE_AMOUNT,
ENROLMENTCOVSTARTDATE,
ENROLMENTCOVSENDDATE
from (
select
PAPF.PERSON_NUMBER,
BP.NAME BENEFIT_PLAN,
BBR.BENEFIT_RELATION_NAME,
Round(BPER.BNFT_AMT, 2) * 100 COVERAGE_AMOUNT,
TO_CHAR(BPER.ENRT_CVG_STRT_DT, 'YYYYMMDD') ENROLMENTCOVSTARTDATE,
TO_CHAR(BPER.ENRT_CVG_THRU_DT, 'YYYYMMDD') ENROLMENTCOVSENDDATE,
row_number() over (partition by PAPF.PERSON_NUMBER order by BPER.ENRT_CVG_STRT_DT desc) rn
from
FROM PER_ALL_PEOPLE_F PAPF,
BEN_PRTT_ENRT_RSLT BPER,
BEN_PL_F BP,
BEN_BENEFIT_RELATIONS_F BBR
WHERE PAPF.PERSON_ID = BPER.PERSON_ID
AND BPER.PL_ID = BP.PL_ID
AND BBR.PERSON_ID = PAPF.PERSON_ID
AND BBR.BENEFIT_RELATION_NAME = 'DFLT'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN BP.EFFECTIVE_START_DATE AND BP.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN BBR.EFFECTIVE_START_DATE AND BBR.EFFECTIVE_END_DATE
)
where rn = 1
If you want to accept tied first place then you would change row_number to rank/dense_rank (it won't matter if you're filtering on rn = 1). One would assume that there would only be one row per person per ENROLMENTCOVSTARTDATE though.