Oracle 23c

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

create table xxtest (
  person_number   number,
  full_name       varchar2(100),
  grade           varchar2(10),
  st_date         date,
  end_date        date
);
begin
insert into xxtest values (1,'John Doe','E02',to_date('2023-01-01','YYYY-MM-DD'),to_date('2023-01-23','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-01-30','YYYY-MM-DD'),to_date('2023-02-28','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-03-01','YYYY-MM-DD'),to_date('2023-03-12','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-03-15','YYYY-MM-DD'),to_date('2023-03-31','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','E02',to_date('2023-04-01','YYYY-MM-DD'),to_date('2023-12-31','YYYY-MM-DD'));
insert into xxtest values (1,'John Doe','N01',to_date('2023-01-24','YYYY-MM-DD'),to_date('2023-01-29','YYYY-MM-DD'));
end;
/
1 rows affected

select a.*, a.end_date-a.st_date days
  from xxtest a;


PERSON_NUMBER
FULL_NAME
GRADE
ST_DATE
END_DATE
DAYS


1
John Doe
E02
01-JAN-23
23-JAN-23
22

1
John Doe
E02
30-JAN-23
28-FEB-23
29

1
John Doe
E02
01-MAR-23
12-MAR-23
11

1
John Doe
E02
15-MAR-23
31-MAR-23
16

1
John Doe
E02
01-APR-23
31-DEC-23
274

1
John Doe
N01
24-JAN-23
29-JAN-23
5


select *
  from xxtest a
match_recognize(
  partition by person_number,full_name,grade
  order by st_date
  MEASURES 
     first(st_date) as dt_start,
     last(end_date) as dt_end,
     sum(end_date-st_date) as days
  PATTERN (st nxt*) 
  DEFINE 
    nxt AS st_date = prev(end_date)+1
);


PERSON_NUMBER
FULL_NAME
GRADE
DT_START
DT_END
DAYS


1
John Doe
E02
01-JAN-23
23-JAN-23
22

1
John Doe
E02
30-JAN-23
12-MAR-23
40

1
John Doe
E02
15-MAR-23
31-DEC-23
290

1
John Doe
N01
24-JAN-23
29-JAN-23
5

PERSON_NUMBER	FULL_NAME	GRADE	ST_DATE	END_DATE	DAYS
1	John Doe	E02	01-JAN-23	23-JAN-23	22
1	John Doe	E02	30-JAN-23	28-FEB-23	29
1	John Doe	E02	01-MAR-23	12-MAR-23	11
1	John Doe	E02	15-MAR-23	31-MAR-23	16
1	John Doe	E02	01-APR-23	31-DEC-23	274
1	John Doe	N01	24-JAN-23	29-JAN-23	5