How to find the Gap between medicine ordered by the patients in sql?

Question

This is the input Table

   id   name   disease  drug   ship date   supply         
    1   aa       D1     dd     10-05-2020   30               
    1   aa       D1     dd     07-06-2020   30            
    1   aa       D1     dd     12-07-2020   30            
    1   aa       D1     dd     09-08-2020   30            
    1   aa       D1     dd     07-09-2020   28             
    1   aa       D1     dd     11-10-2020   28            
    1   aa       D1     dd     10-11-2020   28             
    2   bb       D2     cd     01-01-2020   10               
    2   bb       D2     cd     06-01-2020   10  

       

This is the output required.(Expected date,late/early,gap based on conditions mentioned)

id  name    disease drug    ship date   supply          expected date       late/early  Gap
1   aa       D1     dd     10-05-2020   30                null            first order     0
1   aa       D1     dd     07-06-2020   30             09-06-2020          early          0
1   aa       D1     dd     12-07-2020   30             09-07-2020          late           3
1   aa       D1     dd     09-08-2020   30             11-08-2020          early          0
1   aa       D1     dd     07-09-2020   28             08-09-2020          early          0
1   aa       D1     dd     11-10-2020   28             6-10-2020           late           5
1   aa       D1     dd     10-11-2020   28             08-11-2020          late           2
2   bb       D2     cd     01-01-2020   10               null             first order     0
2   bb       D2     cd     06-01-2020   10             11-01-2020         early           5

• id,name,disease,drug,shipdate,supply are given fields
• First order date is 10-05-2020 if we add supply(30) we get our next expected date i.e 09-06-2020(I have displayed it that in next row to calculate gap) but the patient has made his second order on 07-06-2020 which is early then expected(so it is early refill, hence gap is 0) and in this case next expected date is previous expected date(09-06-2020) +supply(30) i.e 09-07-2020.
• He ordered for the third time on 12-07-2020 which is delayed by 3 days,So it is a late refill and in this case the next expected date is ship date(12-07-2020) +supply (30) i.e 11-08-2020.
• In short if it is early refill then next expected date is previous expected date + supply and in case of late refill it is ship date+ supply.
• Note:- main aim is to calculate gap.

Answer 1

Here is a solution using the match_recognize clause, introduced in Oracle 12.1.

Test data:

alter session set nls_date_format = 'dd-mm-yyyy';

create table input_table(id, name, disease, drug, ship_date, supply) as
    select 1, 'aa', 'D1', 'dd', to_date('10-05-2020'), 30 from dual union all
    select 1, 'aa', 'D1', 'dd', to_date('07-06-2020'), 30 from dual union all
    select 1, 'aa', 'D1', 'dd', to_date('12-07-2020'), 30 from dual union all
    select 1, 'aa', 'D1', 'dd', to_date('09-08-2020'), 30 from dual union all
    select 1, 'aa', 'D1', 'dd', to_date('07-09-2020'), 28 from dual union all
    select 1, 'aa', 'D1', 'dd', to_date('11-10-2020'), 28 from dual union all
    select 1, 'aa', 'D1', 'dd', to_date('10-11-2020'), 28 from dual union all
    select 2, 'bb', 'D2', 'cd', to_date('01-01-2020'), 10 from dual union all
    select 2, 'bb', 'D2', 'cd', to_date('06-01-2020'), 10 from dual
;

Query:

with
  prep (id, name, disease, drug, ship_date, supply, e_date, cls, exp_date) as (
    select id, name, disease, drug, ship_date, supply, e_date, cls,
           case cls when 'A' then lag(e_date + supply) 
                                    over (partition by id, disease, drug
                                          order     by ship_date)
                    else e_date end as exp_date
    from   input_table
    match_recognize(
      partition by id, disease, drug
      order     by ship_date
      measures  a.ship_date + sum(supply) - supply as e_date,
                classifier() as cls
      all rows per match
      pattern   (a b*)
      define    b as ship_date <= a.ship_date + sum(supply) - supply
    )
  )
select id, name, disease, drug, ship_date, supply, exp_date,
       case when exp_date is null then 'first order'
            when cls = 'A'        then 'late'
            else                       'early' end     as late_or_early,
       case cls when 'A' then ship_date - exp_date end as gap
from   prep
order  by id, disease, drug, ship_date
;

Output:

ID NAME DISEASE DRUG SHIP_DATE      SUPPLY EXP_DATE   LATE_OR_EARLY GAP
-- ---- ------- ---- ---------- ---------- ---------- ------------- ---
 1 aa   D1      dd   10-05-2020         30            first order      
 1 aa   D1      dd   07-06-2020         30 09-06-2020 early            
 1 aa   D1      dd   12-07-2020         30 09-07-2020 late            3
 1 aa   D1      dd   09-08-2020         30 11-08-2020 early            
 1 aa   D1      dd   07-09-2020         28 10-09-2020 early            
 1 aa   D1      dd   11-10-2020         28 08-10-2020 late            3
 1 aa   D1      dd   10-11-2020         28 08-11-2020 late            2
 2 bb   D2      cd   01-01-2020         10            first order      
 2 bb   D2      cd   06-01-2020         10 11-01-2020 early