Hi,
I need some help i am trying to get the date range from a table
My Source Data is a below
I am able to get result like below, but in sixth row the ACT_HIRE_DT date is coming '15-MAR-1994' but what i am expecting it should show "15-JAN-2005", 2nd result should start a new Range.
Create Table Script:
create table test_date
(
PER_ID NUMBER(11),
REC_TYPE VARCHAR2(11),
EVT_DT DATE,
HIRE_DT DATE,
TERM_DT DATE,
TRNS_DT DATE,
PROM_DT DATE,
EFF_START_DT DATE,
EFF_END_DT DATE
);
Insert Script:
Insert into TEST_DATE (PER_ID,REC_TYPE,EVT_DT,HIRE_DT,TERM_DT,TRNS_DT,PROM_DT,EFF_START_DT,EFF_END_DT) values (13331,'HIRE',to_date('15-MAR-1994','DD-MON-YYYY'),to_date('15-MAR-1994','DD-MON-YYYY'),null,null,null,to_date('15-MAR-1994','DD-MON-YYYY'),to_date('16-SEP-1996','DD-MON-YYYY'));
Insert into TEST_DATE (PER_ID,REC_TYPE,EVT_DT,HIRE_DT,TERM_DT,TRNS_DT,PROM_DT,EFF_START_DT,EFF_END_DT) values (13331,'TRANSFER',to_date('26-FEB-1998','DD-MON-YYYY'),null,null,to_date('26-FEB-1998','DD-MON-YYYY'),null,to_date('26-FEB-1998','DD-MON-YYYY'),to_date('28-FEB-1998','DD-MON-YYYY'));
Insert into TEST_DATE (PER_ID,REC_TYPE,EVT_DT,HIRE_DT,TERM_DT,TRNS_DT,PROM_DT,EFF_START_DT,EFF_END_DT) values (13331,'TRANSFER',to_date('10-JUL-2000','DD-MON-YYYY'),null,null,to_date('10-JUL-2000','DD-MON-YYYY'),null,to_date('01-OCT-1998','DD-MON-YYYY'),to_date('29-JUL-2002','DD-MON-YYYY'));
Insert into TEST_DATE (PER_ID,REC_TYPE,EVT_DT,HIRE_DT,TERM_DT,TRNS_DT,PROM_DT,EFF_START_DT,EFF_END_DT) values (13331,'TERMINATION',to_date('30-JUL-2002','DD-MON-YYYY'),null,to_date('30-JUL-2002','DD-MON-YYYY'),null,null,to_date('15-MAR-1994','DD-MON-YYYY'),to_date('14-JAN-2005','DD-MON-YYYY'));
Insert into TEST_DATE (PER_ID,REC_TYPE,EVT_DT,HIRE_DT,TERM_DT,TRNS_DT,PROM_DT,EFF_START_DT,EFF_END_DT) values (13331,'HIRE',to_date('15-JAN-2005','DD-MON-YYYY'),to_date('15-JAN-2005','DD-MON-YYYY'),null,null,null,to_date('15-JAN-2005','DD-MON-YYYY'),to_date('09-JUL-2006','DD-MON-YYYY'));
Insert into TEST_DATE (PER_ID,REC_TYPE,EVT_DT,HIRE_DT,TERM_DT,TRNS_DT,PROM_DT,EFF_START_DT,EFF_END_DT) values (13331,'TRANSFER',to_date('10-JUL-2006','DD-MON-YYYY'),null,null,to_date('10-JUL-2006','DD-MON-YYYY'),null,to_date('10-JUL-2006','DD-MON-YYYY'),to_date('31-DEC-4712','DD-MON-YYYY'));
Note: I am using Oracle 12c.
Query I am currently using:
SELECT test_date.*,
MAX(evt_dt) OVER (PARTITION BY per_id,eff_start_dt,eff_end_dt ORDER BY evt_dt DESC) AS MX_EVT_DT,
CASE WHEN hire_dt IS NULL THEN MIN(hire_dt) OVER (PARTITION BY per_id ORDER BY hire_dt ASC) ELSE hire_dt END AS ACT_HIRE_DT
FROM test_date
where per_id = 13331
order by evt_dt asc;
Thanks