Given the following sample records within the table: MY_DATES, using Oracle SQL and/or PL/SQL, I need to always take the first DATE_REGISTERED in this table, i.e. 26/10/2019 and then advance to the third DATE_REGISTERED record/value, i.e. 2/02/2020 and check if the difference is greater than 13 weeks, between them, i.e.
select to_date('2/02/2020','dd/mm/yyyy') - to_date('26/10/2019','dd/mm/yyyy') a from dual
Table Name: MY_DATES
NM DATE_REGISTERED
--- ---------------
A1 26/10/2019
A1 2/11/2019
A1 2/02/2020
A1 9/02/2020
A1 16/02/2020
A1 23/02/2020
A1 1/03/2020
A1 8/03/2020
The end result for this exercise is to return distinct NM value data where this criteria is satisfied.
Advertisement
Answer
Here is an efficient solution using the MATCH_RECOGNIZE clause, introduced in Oracle version 12.1.
The WITH clause simulates the input data (it’s not part of the solution – remove it and reference your actual table and column names in the main query).
The solution partitions by NM and orders by DT, then it looks for three consecutive rows where the third date is more than 13 weeks after the first. As soon as it finds one such occurrence, it fills the “match pattern” with all the remaining rows in the partition, essentially throwing them away – since we only care about a single match.
Note the two NM I added. BB does have dates that are more than 13 weeks apart, but not within three consecutive dates. CC has two consecutive dates that are already more than 13 weeks apart; CC is not in the output because there aren’t three distinct dates to begin with. (Note – that’s how I read the requirement; if instead what was needed is “consecutive dates more than 13 weeks apart should also count”, that can be addressed easily – but that’s not the wording in the poster’s question.)
with
my_dates (nm, date_registered) as (
select 'A1', to_date('26/10/2019', 'dd/mm/yyyy') from dual union all
select 'A1', to_date( '2/11/2019', 'dd/mm/yyyy') from dual union all
select 'A1', to_date( '2/02/2020', 'dd/mm/yyyy') from dual union all
select 'A1', to_date( '9/02/2020', 'dd/mm/yyyy') from dual union all
select 'A1', to_date('16/02/2020', 'dd/mm/yyyy') from dual union all
select 'A1', to_date('23/02/2020', 'dd/mm/yyyy') from dual union all
select 'A1', to_date( '1/03/2020', 'dd/mm/yyyy') from dual union all
select 'A1', to_date( '8/03/2020', 'dd/mm/yyyy') from dual union all
select 'BB', to_date( '2/03/2019', 'dd/mm/yyyy') from dual union all
select 'BB', to_date('14/03/2019', 'dd/mm/yyyy') from dual union all
select 'BB', to_date('18/03/2019', 'dd/mm/yyyy') from dual union all
select 'BB', to_date('10/06/2019', 'dd/mm/yyyy') from dual union all
select 'BB', to_date( '3/04/2019', 'dd/mm/yyyy') from dual union all
select 'CC', to_date('15/08 2019', 'dd/mm/yyyy') from dual union all
select 'CC', to_date('15/02/2020', 'dd/mm/yyyy') from dual
)
-- end of sample data (for testing only); query begins below this comment
select nm
from my_dates
match_recognize(
partition by nm
order by date_registered
pattern ( a b c x* )
define c as date_registered > a.date_registered + 91
);
NM
----
A1
** EDIT **
Re-reading the problem, it seems that only the first three dates should ever be considered (not any three consecutive dates).
That makes the problem simpler. And the solution can be modified easily – the only change needed is to the pattern clause, it should become:
pattern ( ^ a b c )
This anchors the search for three rows at the beginning of the partition. The other rows aren’t inspected.