Find a row that has passed different steps in the same table-CodePudding

Hi to all here it is my problem:

I have an history table where a record register different steps:

Id	Step	Item Code
1	Created	112345
2	Approved	112345
3	Completed	112345
4	Closed	112345
5	Created	112346
6	Approved	112346
8	Closed	112346

What i want to find inside this table is:

All the item codes that have done one step (for example the Approved one) and where the next one is not the "natural one" (for expample the Completed one). In the example table the time code 112346 item has done the Approved step but has skipped the Completed step).

Is there anyway to do a query like this? I've used the PARTITION BY to make a cluster of Item ,for each step, but i am unable to continue the query.

Thanks in advance for any help or suggestion

CodePudding user response：

You can use the LEAD analytic function to check if the next step is not the expected one:

SELECT id, step, item_code
FROM   (
  SELECT t.*,
         LEAD(step) OVER (PARTITION BY item_code ORDER BY id) AS next_step
  FROM   table_name t
)
WHERE step = 'Approved'
AND   (next_step IS NULL OR next_step != 'Completed')

Or, from Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing:

SELECT id, step, item_code
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY item_code
  ORDER     BY id
  ALL ROWS PER MATCH
  PATTERN ( approved {- (not_completed|$) -} )
  DEFINE
    approved      AS step = 'Approved',
    not_completed AS step <> 'Completed'
)

Which, for the sample data:

CREATE TABLE table_name (Id, Step, Item_Code) AS
SELECT 1, 'Created',   112345 FROM DUAL UNION ALL
SELECT 2, 'Approved',  112345 FROM DUAL UNION ALL
SELECT 3, 'Completed', 112345 FROM DUAL UNION ALL
SELECT 4, 'Closed',    112345 FROM DUAL UNION ALL
SELECT 5, 'Created',   112346 FROM DUAL UNION ALL
SELECT 6, 'Approved',  112346 FROM DUAL UNION ALL
SELECT 8, 'Closed',    112346 FROM DUAL;

Both output:

ID STEP ITEM_CODE

6 Approved 112346

ID	STEP	ITEM_CODE
6	Approved	112346

db<>fiddle here

CodePudding user response：

Provided you have a table of step names with a proper ordering int column (seqno)

with 
/* Sample data */
steps(seqno, Step) as (
   select 1, 'Created'   from dual union all
   select 2, 'Approved'  from dual union all
   select 3, 'Completed' from dual union all
   select 4, 'Closed'    from dual 
),
tbl(Id, Step, ItemCode) as (  
   select 1, 'Created'  , 112345 from dual union all
   select 2, 'Approved' , 112345 from dual union all
   select 3, 'Completed', 112345 from dual union all
   select 4, 'Closed'   , 112345 from dual union all
   select 5, 'Created'  , 112346 from dual union all
   select 6, 'Approved' , 112346 from dual union all
   select 8, 'Closed'   , 112346 from dual 
),
/* Find steps with the next step being out of order */
m as (
  select max(seqno) 1 term
  from steps
)
select Id, Step, ItemCode
from (
  SELECT t.*, s.seqno, lead(s.seqno, 1, (select term from m)) over(partition by ItemCode order by Id) nextSeqno
  FROM tbl t
  JOIN steps s on s.Step = t.Step
) q
cross join m
where nextseqno != Seqno   1 and nextseqno < m.term