Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


create table t (client_id, start_date, active) as
    select 1, date'2021-01-30', 1 from dual union all
    select 1, date'2021-03-01', 1 from dual union all
    select 2, date'2021-04-03', 1 from dual union all
    select 2, date'2021-05-03', 0 from dual union all
    select 3, date'2021-05-27', 1 from dual union all
    select 3, date'2021-06-28', 0 from dual
6 rows affected

select t1.*, 
    case when t1.active = 0 and t2.start_date is not null then 1 else 0 end newactive 
from t t1 left join t t2 on (
    t2.client_id = t1.client_id and 
    t2.start_date = add_months (t1.start_date, -1))


CLIENT_ID
START_DATE
ACTIVE
NEWACTIVE


2
03-MAY-21
0
1

1
01-MAR-21
1
0

2
03-APR-21
1
0

3
28-JUN-21
0
0

1
30-JAN-21
1
0

3
27-MAY-21
1
0

CLIENT_ID	START_DATE	ACTIVE	NEWACTIVE
2	03-MAY-21	0	1
1	01-MAR-21	1	0
2	03-APR-21	1	0
3	28-JUN-21	0	0
1	30-JAN-21	1	0
3	27-MAY-21	1	0