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;
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>