This question is already answered but I could not get few parts of the query. Below 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
My requirement was to get whether next order(ship date) is early or late. e.g- First order is on 10-05-2020 + supply(30) = 09-06-2020( next expected date) but the patient ordered on 07-06-2020 so second order is Early order case. Now, 07-06-2020+supply(30)= 09-07-2020(expected date) but patient ordered on 12-07-2020.Third order is Late Order Case.
If Order is early then next expected date is ship date + suppy but if order is late then next expected date is previous expected date + supply.(op will make it better to understand)
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
Below is the query I got from Stack Overflow:
1. 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 ;
What is the pattern and define clause actually doing here and how is it calculating the desired result?
pattern (a b*)
Matches one row as a and then zero-or-more rows as b
define b as ship_date <= a.ship_date + sum(supply) - supply
a is not defined so it will match any single row.b is defined such that the ship_date of the current row being matched is less-than-or-equal-to <= the ship_date of the a row plus + the sum of the supply (in, since it is an aggregation function, all the a and b rows in the current match) minus - the supply of the current row being matched.For your data:
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
a pattern.2020-06-07 is less than or equal to 2020-05-10 plus SUM(30, 30) (total of all the supplies in the current match) minus 30 (current supply) which totals 2020-06-09. Since this true then the row is a b row.2020-07-12 is less than or equal to 2020-05-10 plus SUM(30, 30, 30) (total of all the supplies in the current match) minus 30 (current supply) which totals 2020-07-09. Since this false then the row is not included in the previous pattern and starts a new matching group as the a row.