Hi Gurus,
Please find below the create table SQL:
create table a(employee_no varchar2(100),start_date date,end_date date,status varchar2(100));
Insert Script:
Insert into a values ('100',to_date('12-JAN-22','DD-MON-RR'),to_date('10-JUN-22','DD-MON-RR'),'Active');
Insert into a values ('100',to_date('10-NOV-22','DD-MON-RR'),to_date('10-JAN-23','DD-MON-RR'),'Active');
Insert into a values ('100',to_date('10-FEB-23','DD-MON-RR'),to_date('10-AUG-23','DD-MON-RR'),'InActive');
Insert into a values ('100',to_date('30-AUG-23','DD-MON-RR'),to_date('30-NOV-23','DD-MON-RR'),'Active');
Insert into a values ('100',to_date('30-DEC-23','DD-MON-RR'),to_date('10-JAN-24','DD-MON-RR'),'Active');
Insert into a values ('100',to_date('10-FEB-24','DD-MON-RR'),to_date('10-MAR-24','DD-MON-RR'),'Active');
Insert into a values ('100',to_date('10-MAY-24','DD-MON-RR'),to_date('10-JUL-24','DD-MON-RR'),'Active');
My requirement is retrieve above highlighted row, requirement is pick the oldest record with status as ‘Active’, as you can see in 3rd row status is ‘InActive’, and again from 30-Aug-23 status is changed to ‘Active’ and ‘Active’ status for future rows, hence I need to get the 30-Aug-23 row which is the oldest record having status ‘Active’. Could you please help on this.
Regards
Raj