Search code examples
sqloracle-databaseobiee

Oracle Counts of partitions based on conditions

I have following data enter image description here

I need to count the orders which skipped a particular status. This is a status history table where statuses are arranged in descending order.

I have tried below Oracle SQL Logic but it's giving me count of all individual records and not the individual orders

I need this to be part of select statement only and not in whare clause or CTE or subquery as I'd be replicating this in OBIEE Answers.

select
count(CASE WHEN STATUS NOT LIKE '%Approved%' THEN 1 END) NOT_APPROVED,
count(CASE WHEN STATUS NOT LIKE '%Dispatched%' THEN 1 END) NOT_DISPATCHED
From Orders
Solution

  • I would suggest changing the approach a bit:

    1. For each order, aggregate all the statuses, sorted by the creation date of the row
    2. Look for the missing status in the list
    SELECT count(
       CASE
         WHEN INSTR(status_array, 'Approved') > 0 THEN 0
         ELSE 1) as 'Not approved', 
       ... Repeat for each status
FROM (
  SELECT order_id, LISTAGG(status, ',') WITHIN GROUP (ORDER BY created_at) AS status_array
  FROM Orders
  GROUP BY order_id
) t;

    Please note that that's just the general idea, I don't have your data, so I can't execute the query for you.