Sort by status and effective date range

Before After

Is there any way to make ‘Before’ to ‘After’? if a function or procedure is required, please write one for me. please help me

drop table test;
create table test (employee_code varchar2(8), status varchar2(1), effective_date date, expiry_date date, rate number);
insert into test values ('1', 'U', '01-JAN-20','15-JAN-20',10);
insert into test values ('1', 'U', '06-JAN-20','01-FEB-20',11);
insert into test values ('1', 'N', '02-FEB-20','15-MAR-20',5);
insert into test values ('1', 'N', '16-MAR-20','15-JUN-20',6);
insert into test values ('2', 'N', '01-JAN-20','11-JAN-20',20);
insert into test values ('2', 'U', '12-JAN-20','12-FEB-20',100);
insert into test values ('2', 'N', '13-FEB-20','19-MAR-20',25);
insert into test values ('2', 'N', '20-MAR-20','21-JUN-20',30);

drop table result;
create table result (employee_code varchar2(8), status varchar2(1), effective_date date, expiry_date date);
insert into result values ('1', 'U', '01-JAN-20','01-FEB-20');
insert into result values ('1', 'N', '02-FEB-20','15-JUN-20');
insert into result values ('2', 'N', '01-JAN-20','11-JAN-20');
insert into result values ('2', 'U', '12-JAN-20','12-FEB-20');
insert into result values ('2', 'N', '13-FEB-20','21-JUN-20');

select * from test;
select * from result;

Answer

You just need to use GROUP BY and analytical function as follows:

SQL> Select employee_code,
  2         status,
  3     min(effective_date) effective_date,
  4     max(expiry_date) expiry_date
  5  From
  6  (Select t.*,
  7         Sum(case when lgst is null or lgst <> status then 1 end)
  8              over (partition by employee_code order by effective_date) as sm
  9    From
 10    (Select t.*,
 11            Lag(status) over (partition by employee_code order by effective_date) as lgst
 12       From test t) t
 13  )
 14  Group by employee_code, sm, status
 15  order by employee_code, effective_date;

EMPLOYEE S EFFECTIVE EXPIRY_DA
-------- - --------- ---------
1        U 01-JAN-20 01-FEB-20
1        N 02-FEB-20 15-JUN-20
2        N 01-JAN-20 11-JAN-20
2        U 12-JAN-20 12-FEB-20
2        N 13-FEB-20 21-JUN-20

SQL>