By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (CaseKey varchar2(1), memberId Int, CallDate Date)
insert into mytable(CaseKey, memberId, CallDate) values ('A', 1, '1-Apr-2022')
1 rows affected
insert into mytable(CaseKey, memberId, CallDate) values ('B', 2, '21-Mar-2022')
1 rows affected
insert into mytable(CaseKey, memberId, CallDate) values ('C', 2, '28-Mar-2022')
1 rows affected
insert into mytable(CaseKey, memberId, CallDate) values ('D', 3, '2-Mar-2022')
1 rows affected
insert into mytable(CaseKey, memberId, CallDate) values ('E', 3, '28-Mar-2022')
1 rows affected
insert into mytable(CaseKey, memberId, CallDate) values ('F', 3, '5-Apr-2022')
1 rows affected
insert into mytable(CaseKey, memberId, CallDate) values ('G', 3, '12-Apr-2022')
1 rows affected
select a.*, LEAD(CallDate, 1) OVER (Partition by memberId ORDER BY CallDate) AS "CallbackDate",
LEAD(CallDate, 1) OVER (Partition by memberId ORDER BY CallDate) - a.calldate AS DateDiff from mytable a
| CASEKEY | MEMBERID | CALLDATE | CallbackDate | DATEDIFF |
|---|---|---|---|---|
| A | 1 | 01-APR-22 | null | null |
| B | 2 | 21-MAR-22 | 28-MAR-22 | 7 |
| C | 2 | 28-MAR-22 | null | null |
| D | 3 | 02-MAR-22 | 28-MAR-22 | 26 |
| E | 3 | 28-MAR-22 | 05-APR-22 | 8 |
| F | 3 | 05-APR-22 | 12-APR-22 | 7 |
| G | 3 | 12-APR-22 | null | null |