This benefit employee enrollments query displays the latest active open benefit enrollment details for participants during the current period, supporting enrollment tracking and program analysis.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Fetches participant's latest active benefit enrollment details with program and assigned dates.
PPNFV.FULL_NAME PARTICIPANT_FULL_NAME,
BLF.NAME LIFE_EVENT,
(
SELECT NAME
FROM BEN_PGM_F BPF
WHERE BPF.PGM_ID = BPER.PGM_ID
AND TRUNC(SYSDATE) BETWEEN BPF.EFFECTIVE_START_DATE
AND BPF.EFFECTIVE_END_DATE
) PROGRAM_NAME,
BPER.ELECTION_DATE,
BPECP.DFLT_ASND_DT DEFAULT_BENIFIT_ASSIGNED_DATE
FROM (
SELECT *
FROM (
SELECT
BPER_INNER.*,
ROW_NUMBER() OVER (
PARTITION BY PER_IN_LER_ID
ORDER BY ELECTION_DATE DESC
) RN
FROM BEN_PRTT_ENRT_RSLT BPER_INNER
)
WHERE RN = 1
) BPER,
BEN_PER_IN_LER BPIL,
BEN_LER_F BLF,
PER_PERSON_NAMES_F_V PPNFV,
BEN_PIL_ELCTBL_CHC_POPL BPECP
WHERE 1=1
AND BPER.PER_IN_LER_ID = BPIL.PER_IN_LER_ID
AND BPECP.PER_IN_LER_ID = BPIL.PER_IN_LER_ID
AND BPECP.PGM_ID = BPER.PGM_ID
AND BLF.LER_ID = BPIL.LER_ID
AND PPNFV.PERSON_ID = BPIL.PERSON_ID
AND PPNFV.PERSON_ID = BPER.PERSON_ID
AND BLF.NAME = 'Open' -- Include only records where the life event is 'Open' (i.e., Open Enrollment period)
AND BPIL.PER_IN_LER_STAT_CD = 'STRTD' --Filter to include only enrollment life events with status 'Started' (active/in-progress events)
AND PPNFV.NAME_TYPE = 'GLOBAL'
AND SYSDATE BETWEEN BLF.EFFECTIVE_START_DATE AND BLF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
ORDER BY PPNFV.FULL_NAME ;