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,
    min(ACTION) as ACTION, min(ACTION_REASON) as ACTION_REASON,
    min(IsTerminated) as IsRehire,
    case when min(ACTION_REASON) = 'REH' and min(IsTerminated) = 'N' or
              min(ACTION_REASON) = 'ADL' and min(IsTerminated) = 'Y' then '!!' else '' end as IsError
from PS_Job j cross apply (
    select case first_value(ACTION) over (partition by 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
) t
where ACTION = 'REH'
group by EMPLID, EMPL_RCD, EFFDT
order by EMPLID, EFFDT;

EMPLID	EMPL_RCD	EFFDT	ACT	ACT	I	IS
12345	0	01-NOV-22	REH	REH	Y
12345	1	01-MAR-23	REH	ADL	N
12345	0	16-AUG-23	REH	REH	Y
12345	1	01-OCT-23	REH	REH	N	!!
12345	2	01-DEC-23	REH	ADL	N
12345	0	01-MAR-24	REH	REH	N	!!