sqloracle-databasegreatest-n-per-groupanalytic-functions

How to get max of one date in a table in sql


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 ?


Solution

  • 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.