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