Recursive Query Sql Oracle 3 months difference between dates

I have a simply table with three columns, policy_no, casenumber and created_date. Multiple casenumbers can be logged against unique policy numbers. I need a query that runs through the data finds the earliest case for a policy, and then checks the difference between that and the next one. If the difference is greater than three months it includes the first case, if the difference is shorter, it exclude the first case and then moves on to the second case and again checks the difference between that and the third case. Basically, I only want to include cases which did not have another case raised raised within the next three months.

Select * from policy_table
where policy_no = '17850850'

Would return the following results;

policy_no   casenumber  created_date
17850850    4150778     16-APR-19
17850850    3955549     22-JAN-19
17850850    3188447     14-MAY-18
17850850    2998931     14-MAR-18
17850850    2767545     29-DEC-17
17850850    2420594     05-SEP-17

The first case raised on 05/09/2017 should be included, as no further case was raised until 29/12/2017 (so greater than 3 months). The case raised on 29/12/2017 should be excluded as a case was raised on 14/03/2018. The case raised on 14/03/2018 should also be excluded, as another case was raised on 14/05/2018 (within 3 months). The case raised on 14/05/2018 should be included, 22/01/2019 should be excluded, and 16/04/2019 should be temporarily included (subject to no further case being raised within the following three months). Any help would be much appreciated. Looking to get to this;

policy_no   casenumber  created_date    Diff    Outcome
17850850    4150778     16-APR-19               latest
17850850    3955549     22-JAN-19       2.8     excluded
17850850    3188447     14-MAY-18       8.3     Included
17850850    2998931     14-MAR-18       2       excluded
17850850    2767545     29-DEC-17       2.5     excluded
17850850    2420594     05-SEP-17       3.77    Included

Answer

You don’t need to do this recursively, you can use not exists to check for any cases within three months:

-- CTE for sample data
with your_table (policy_no, casenumber, created_date) as (
            select 17850850, 4150778, date '2019-04-16' from dual
  union all select 17850850, 3955549, date '2019-01-22' from dual
  union all select 17850850, 3188447, date '2018-05-14' from dual
  union all select 17850850, 2998931, date '2018-03-14' from dual
  union all select 17850850, 2767545, date '2017-12-29' from dual
  union all select 17850850, 2420594, date '2017-09-17' from dual
)
-- actual query
select policy_no, casenumber, created_date
from your_table t
where policy_no = 17850850
and not exists (
  select *
  from your_table t2
  where t2.policy_no = t.policy_no
  and t2.casenumber != t.casenumber
  and t2.created_date >= t.created_date
  and t2.created_date <= add_months(t.created_date, 3)
)
order by policy_no, created_date desc;

 POLICY_NO CASENUMBER CREATED_DATE
---------- ---------- ------------
  17850850    4150778 2019-04-16  
  17850850    3188447 2018-05-14  
  17850850    2420594 2017-09-17  

If you want to see all rows with the month-based difference and the latest/excluded/included flag, as shown in your edited question, then you can instead use an inline view to find the next created date and the difference, and then work out the flag based on that in an outer query:

-- with same CTE for sample data
select policy_no, casenumber, created_date,
  trunc(diff, 2) as diff,
  case when diff is null then 'latest'
       when diff <= 3 then 'excluded'
       else 'included'
  end as outcome
from (
  select policy_no, casenumber, created_date,
    months_between(lead(created_date) over (partition by policy_no order by created_date),
      created_date) as diff
  from your_table t
)
where policy_no = 17850850
order by policy_no, created_date desc;

 POLICY_NO CASENUMBER CREATED_DATE       DIFF OUTCOME 
---------- ---------- ------------ ---------- --------
  17850850    4150778 2019-04-16              latest  
  17850850    3955549 2019-01-22          2.8 excluded
  17850850    3188447 2018-05-14         8.25 included
  17850850    2998931 2018-03-14            2 excluded
  17850850    2767545 2017-12-29         2.51 excluded
  17850850    2420594 2017-09-17         3.38 included

You could use the same inline-view approach to get just the included rows instead:

-- with same CTE
select policy_no, casenumber, created_date
from (
  select policy_no, casenumber, created_date,
    months_between(lead(created_date) over (partition by policy_no order by created_date),
      created_date) as diff
  from your_table t
)
where policy_no = 17850850
and (diff is null or diff > 3)
order by policy_no, created_date desc;

 POLICY_NO CASENUMBER CREATED_DATE
---------- ---------- ------------
  17850850    4150778 2019-04-16  
  17850850    3188447 2018-05-14  
  17850850    2420594 2017-09-17  

db<>fiddle