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.

HI, @Atif Gap(Last coloumn) is expected result. Only first 6 colomns are given(till supply), All coloumns after that are added for explanation to calculate gap. — Random Guy, Jul 10 '21 at 10:00
I think the gap can be calculated with [DATEDIFF](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff) between `ship date` and `expected date`. With some extension, when `expected date` is null. — Luuk, Jul 10 '21 at 10:19
Hi @Luuk Expected date is not provided to us neither late/early field is provided. I have included that field for easy explanation. We have to calculate expected date and then gap. — Random Guy, Jul 10 '21 at 10:29
But can you not use the calculated date in the function DATEDIFF ? (DATEDIFF, is you use MySQL) — Luuk, Jul 10 '21 at 10:46
If you use Oracle, the difference in dates can be calculated is answered here: https://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql..... PLEASE correct the tags, using [edit] — Luuk, Jul 10 '21 at 10:49
Hi @Lukk I have included actual Table and desired result. As you can see I don't have expected date to apply datediff or any other function ,rather it has to be calculated based on conditions mentioned. — Random Guy, Jul 10 '21 at 11:02
@RandomGuy . . . Tag your question with the database you are using. — Gordon Linoff, Jul 10 '21 at 11:05
Your sample data doesn't seem to be consistent, eg Id 2 first shipdate is 01/01/2020, adding 10 to this should give 11/01/2020 not 10/01/2020? — Stu, Jul 10 '21 at 11:12
Hi Atif, As supply days change for same id, Lag is not giving correct results. — Random Guy, Jul 10 '21 at 11:30
Always include your database **version** (such as 11.2.0.4 or 12.1.0.2). If you don't know it, run `select * from v$version` to find out. Your problem may have a relatively simple `match_recognize` solution, but that feature was first added in version 12.1. So, the answer will depend on your version. — , Jul 10 '21 at 13:55
SQL Developer is your development application. It is irrelevant. What matters is the version of your Oracle database. To find it out, run the query I included in my comment. (If you did, the output would not include the word "developer"!) — , Jul 10 '21 at 14:21
Question about the output. Look at ID = 1, ship dates of 07-09-2020 and 11-10-2020. I believe what you show as the "expected date" is incorrect. The most recent late ship_date was 12-07-2020. Supply was 30 on that date (12-07) and also 30 on the next shipment (09-08), so the next expected date is 12-07 + 60 days = 10-09. You show 08-09. It seems that you added 30 + 28, but 28 is the supply for the September shipment (either 10-09 or 08-09), it does not affect the expected ship date for September. Then the same propagates to the next line in the table. Please confirm. — , Jul 10 '21 at 14:26
@mathguy Sorry for this! After I execute the query you mentioned . Oracle DB version is 12.2.0.1.0 — Random Guy, Jul 10 '21 at 14:30
@mathguy expected date is not simply adding supply days to ship date but it also depends whether previous shipment was early or delayed. If the previous shipment was late then, next expected date calculated is based on ship date + supply if it is early then previous expected date+supply date — Random Guy, Jul 10 '21 at 14:36
If you notice second row is early refill so next expected date is expected date+supply while third row is late refill so next expected date is ship date+supply. — Random Guy, Jul 10 '21 at 14:38
Right, and in a sequence of several "early" shipments, the "expected date" is always computed from the ship date of the most recent late shipment. Don't look at the first two rows. Look at rows 3-4-5-6. 12-07-2020 was a late shipment date. The next expected date is 11-08-2020. The next shipment went out on 09-08 which is early. The next **expected** date should be 10-09-2020: supply in July was 30, and in August also 30, so the patient should have enough supply until 10-09-2020. Why is your expected date 08-09-2020? By what calculation? I think the calculation I just gave is the right one. — , Jul 10 '21 at 14:42
Hi @mathguy! You are right I made some mistake while making sample data. Glad you noticed that! BTW is there any other method other than match_recognize if not could you please tell me where to explore this more! — Random Guy, Jul 10 '21 at 15:12
Not an Oracle user but I would think a combination of a CTE using `lag` combined with another recursive CTE could work. — Stu, Jul 10 '21 at 15:22
This can also be done using a recursive CTE. However, that solution would be much slower; your exact use case is the reason Oracle developed the `match_recognize` feature. (It's in the SQL Standard now, and supported by a few other database vendors too, but I suspect it was all at Oracle's initiative.) Learning `match_recognize` is very much worth the effort - it can solve many types of problems quickly and elegantly. But it's a big machine. You may want to start with the documentation, and perhaps here too: https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1 — , Jul 10 '21 at 20:39
Hi @mathguy Could you please explain this part of the qury "**pattern (a b*) define b as ship_date <= a.ship_date + sum(supply) - supply**" — Random Guy, Jul 11 '21 at 05:37

score 3 · Accepted Answer · answered Jul 10 '21 at 14:49

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

Awesome solution @mathguy. I was trying this for the past 1 hour but with no success. Is it possible to do this without match_recognize also? — Ankit Bajpai, Jul 10 '21 at 15:42

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

1 Answers1