I'm looking to return the next status after an order has been received, however only looking to capture certain statuses within the following list: 'Shipped', 'Partially shipped', 'Cancelled' Below is the orders table

As you can see for order ID 2 the following status after received is Acknowledged, but I want to only grab the next status that falls within the following list : 'Shipped', 'Partially shipped', 'Cancelled' Therefore the following output would be correct, skipping acknowledged and returning shipped

CodePudding user response：

You can use a conditional LAG analytic statement with the IGNORE NULLS clause:

SELECT Order_id,
       Order_status,
       "DATE"
FROM   (
  SELECT t.*,
         LAG(
           CASE
           WHEN order_status IN ('Received', 'Shipped', 'Partially Shipped', 'Cancelled')
           THEN order_status
           END
         ) IGNORE NULLS OVER (PARTITION BY order_id ORDER BY "DATE") AS prev_status
  FROM   table_name t
)
WHERE  prev_status = 'Received'
AND    order_status IN ('Shipped', 'Partially Shipped', 'Cancelled')

Which, for the sample data:

CREATE TABLE table_name (Order_ID, Order_Status, "DATE") AS
SELECT 1, 'Received',          DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 1, 'Partially Shipped', DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 1, 'Shipped',           DATE '2021-01-02' FROM DUAL UNION ALL
SELECT 1, 'Returned',          DATE '2021-02-01' FROM DUAL UNION ALL
SELECT 1, 'Received',          DATE '2021-02-01' FROM DUAL UNION ALL
SELECT 1, 'Shipped',           DATE '2021-03-01' FROM DUAL UNION ALL
SELECT 2, 'Received',          DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 2, 'Acknowledged',      DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 2, 'Shipped',           DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 3, 'Received',          DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 3, 'Acknowledged',      DATE '2021-02-01' FROM DUAL UNION ALL
SELECT 4, 'Received',          DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 4, 'Acknowledged',      DATE '2021-02-01' FROM DUAL UNION ALL
SELECT 4, 'Received',          DATE '2021-01-01' FROM DUAL;

Outputs:

ORDER_ID	ORDER_STATUS	DATE
1	Partially Shipped	2021-01-01 00:00:00
1	Shipped	2021-03-01 00:00:00
2	Shipped	2021-01-01 00:00:00

db<>fiddle here

CodePudding user response：

You may use match_recognize for this that looks suitable here, because it can handle repetition of the statuses to be included between. It is available for all supported versions (since 12c).

I've added extra rows to show how it handles, for example, 'Shipped' after 'Partially Shipped', and absence of the next status from the list of required statuses (as for order_id = 3).

with a (Order_ID, Order_Status, dt) as (
  select 1, 'Received', date '2021-01-01'           from dual union all
  select 1, 'Partially Shipped', date '2021-01-01'  from dual union all
  select 1, 'Shipped', date '2021-01-02'           from dual union all
  select 1, 'Returned', date '2021-02-01'           from dual union all
  select 1, 'Received', date '2021-02-01'           from dual union all
  select 1, 'Shipped', date '2021-03-01'           from dual union all
  select 2, 'Received', date '2021-01-01'           from dual union all
  select 2, 'Acknowledged', date '2021-01-01'       from dual union all
  select 2, 'Shipped', date '2021-01-01'           from dual union all
  select 3, 'Received', date '2021-01-01'           from dual union all
  select 3, 'Acknowledged', date '2021-02-01'       from dual union all
  select 3, 'Received', date '2021-03-01'           from dual union all
  select 4, 'Received', date '2021-01-01'           from dual union all
  select 4, 'Acknowledged', date '2021-02-01'       from dual union all
  select 4, 'Received', date '2021-03-01'           from dual union all
  select 4, 'Acknowledged', date '2021-03-02'       from dual union all
  select 4, 'Shipped', date '2021-03-05'       from dual
)
select *
from a
match_recognize (
  partition by order_id
  order by dt asc
  all rows per match
  /*Return the next status after 'Received', that is in a list of interest*/
  pattern (
    {- received -} /*Exclude 'Received' from output*/
    {- exclude* -} /*Exclude "other" statuses after 'Received'*/
    next? /*And include the next status that should be in the list.
            Marked as optional to allow previous exclusion group
            to catch not listed statuses*/
  )
  define
    received as order_status = 'Received',
    /*Other statuses (that are out of interest)*/
    exclude as order_status not in ('Shipped', 'Partially Shipped', 'Cancelled'),
    /*To break match if the next status is not from the list*/
    next as order_status in ('Shipped', 'Partially Shipped', 'Cancelled')
)

ORDER_ID | DT         | ORDER_STATUS
-------: | :--------- | :-----------
       1 | 2021-01-02 | Shipped     
       1 | 2021-03-01 | Shipped     
       2 | 2021-01-01 | Shipped     
       4 | 2021-03-05 | Shipped     

db<>fiddle here