FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE PS_Job (
  EMPLID INTEGER,
  EMPL_RCD INTEGER,
  EFFDT DATE,
  END_DATE DATE,
  EFFSEQ INTEGER,
  ACTION VARCHAR(3),
  ACTION_REASON VARCHAR(3),
  EMPL_STATUS VARCHAR(1)
);

INSERT INTO PS_Job
  (EMPLID, EMPL_RCD, EFFDT, END_DATE, EFFSEQ, ACTION, ACTION_REASON, EMPL_STATUS)
VALUES
  (12345, 0, '14-JUN-21', '01-MAR-22', 0, 'HIR', 'INH', 'A'),
  (12345, 0, '01-MAR-22', '01-SEP-22', 0, 'PAY', 'SPI', 'A'),
  (12345, 0, '01-SEP-22', '01-NOV-22', 0, 'TER', 'CTG', 'T'),
  (12345, 0, '01-NOV-22', '01-FEB-23', 0, 'REH', 'REH', 'A'),
  (12345, 0, '01-FEB-23', '01-APR-23', 0, 'PAY', 'CWP', 'A'),
  (12345, 1, '01-MAR-23', '01-MAY-23', 0, 'REH', 'ADL', 'A'),
  (12345, 0, '01-APR-23', '16-AUG-23', 0, 'TER', 'CTG', 'T'),
  (12345, 1, '01-MAY-23', '07-JUL-23', 0, 'PAY', 'CWP', 'A'),
  (12345, 1, '01-JUL-23', '01-OCT-23', 0, 'TER', 'CTG', 'T'),
  (12345, 0, '16-AUG-23', '01-DEC-23', 0, 'REH', 'REH', 'A'),
  (12345, 1, '01-OCT-23', '16-OCT-23', 0, 'REH', 'REH', 'A'),
  (12345, 1, '16-OCT-23', '16-DEC-23', 0, 'PAY', 'CWP', 'A'),
  (12345, 0, '01-DEC-23', '01-MAR-24', 0, 'TER', 'CTG', 'T'),
  (12345, 2, '01-DEC-23', '16-APR-24', 0, 'REH', 'ADL', 'A'),
  (12345, 1, '16-DEC-23', '01-JAN-50', 0, 'TER', 'CTG', 'T'),
  (12345, 0, '01-MAR-24', '16-JUN-24', 0, 'REH', 'REH', 'A'),
  (12345, 2, '16-APR-24', '01-JAN-50', 0, 'TER', 'CTG', 'T'),
  (12345, 0, '15-JUN-21', '01-JAN-50', 0, 'TER', 'CTG', 'T');
  
select
    EMPLID, EMPL_RCD, EFFDT, ACTION, ACTION_REASON,
    IsRehire,
    case when ACTION_REASON = 'REH' and IsRehire = 'N' or
              ACTION_REASON = 'ADL' and IsRehire = 'Y' then '!!' else '' end as IsError
from PS_Job j outer apply (
    select min(IsTerminated) as IsRehire from (
        select
            case first_value(ACTION) over (partition by EMPLID, EMPL_RCD order by EFFDT desc)
                when 'TER' then 'Y' else 'N' end as IsTerminated
        from PS_Job j2
        where j2.EMPLID = j.EMPLID and j2.EFFDT < j.EFFDT and j.ACTION = 'REH'
    ) t0
) t 
order by EMPLID, EFFDT;

EMPLID	EMPL_RCD	EFFDT	ACT	ACT	I	IS
12345	0	14-JUN-21	HIR	INH
12345	0	15-JUN-21	TER	CTG
12345	0	01-MAR-22	PAY	SPI
12345	0	01-SEP-22	TER	CTG
12345	0	01-NOV-22	REH	REH	Y
12345	0	01-FEB-23	PAY	CWP
12345	1	01-MAR-23	REH	ADL	N
12345	0	01-APR-23	TER	CTG
12345	1	01-MAY-23	PAY	CWP
12345	1	01-JUL-23	TER	CTG
12345	0	16-AUG-23	REH	REH	Y
12345	1	01-OCT-23	REH	REH	N	!!
12345	1	16-OCT-23	PAY	CWP
12345	0	01-DEC-23	TER	CTG
EMPLID	EMPL_RCD	EFFDT	ACT	ACT	I	IS
12345	2	01-DEC-23	REH	ADL	N
12345	1	16-DEC-23	TER	CTG
12345	0	01-MAR-24	REH	REH	N	!!
12345	2	16-APR-24	TER	CTG