CODEWITHSUNDEEP
sqloracle-database
Evillain
Evillain

Reputation: 113

Filter values based on a column SQL

I have a view with the following info:

id contractId opId opStatus opDate
1 2 201 Done 01/01/2024
2 2 202 Processing 01/01/2024
3 2 203 Done 01/01/2024
4 3 301 Waiting 01/01/2024
5 3 302 Processing 02/01/2024
6 4 401 Waiting 05/01/2024
7 4 402 Waiting 06/01/2024
8 5 501 Done 01/01/2024

What I need is to filter the data based on the next criteria:

  1. If opStatus for any operation in contract is Done - take all lines with this status and the same date
  2. If not, if opStatus for any operation in contract is Processing - take all lines with this status and the same date (like the first criteria)
  3. If not take operation with the earliest opDate

So after all this steps I should have a table with a result:

id contractId opId opStatus opDate
1 2 201 Done 01/01/2024
3 2 203 Done 01/01/2024
5 3 302 Processing 02/01/2024
6 4 401 Waiting 05/01/2024
8 5 501 Done 01/01/2024

What would be a possibly required SQL? Database is Oracle.

Upvotes: 0

Views: 75

Answers (2)

ORA-01017
ORA-01017

Reputation: 1075

You can use window function as follows:

Select t.* from
(Select t.*,
       Count(case when opstatus = 'Done' then 1 end) over (partition by contractid) as donecnt,
       Count(case when opstatus = 'Processing' then 1 end) over (partition by contractid) as processingcnt,
       Row_number() over (partition by contractid order by opdate) as rn
  From your_table t) t
Where (donecnt > 0 and opstatus = 'Done')
Or (processingcnt > 0 and donecnt = 0 and opstatus = 'Processing')
Or (processingcnt = 0 and donecnt = 0 and rn = 1)
order by id;

Upvotes: 1

MT0
MT0

Reputation: 168598

From Oracle 12, you can use MATCH_RECOGNIZE for row-by-row pattern matching:

SELECT id, contractId, opId, opStatus, opDate
FROM   table_name 
       MATCH_RECOGNIZE(
         PARTITION BY contractId
         ORDER BY opDate, id
         ALL ROWS PER MATCH
         PATTERN ( ^ ({- any_row*? -} done)+
                 | ^ ({- any_row*? -} processing)+
                 | ^ any_row
                 )
         DEFINE
           done AS  opStatus = 'Done'
                AND TRUNC(opDate) = TRUNC(FIRST(done.opDate)),
           processing AS opStatus = 'Processing'
                AND TRUNC(opDate) = TRUNC(FIRST(processing.opDate))
       )

Which, for the sample data:

CREATE TABLE table_name (id, contractId, opId, opStatus, opDate) AS
SELECT 1, 2, 201, 'Done',       DATE '2024-01-01' FROM DUAL UNION ALL
SELECT 2, 2, 202, 'Processing', DATE '2024-01-01' FROM DUAL UNION ALL
SELECT 3, 2, 203, 'Done',       DATE '2024-01-01' FROM DUAL UNION ALL
SELECT 4, 3, 301, 'Waiting',    DATE '2024-01-01' FROM DUAL UNION ALL
SELECT 5, 3, 302, 'Processing', DATE '2024-01-02' FROM DUAL UNION ALL
SELECT 6, 4, 401, 'Waiting',    DATE '2024-01-05' FROM DUAL UNION ALL
SELECT 7, 4, 402, 'Waiting',    DATE '2024-01-06' FROM DUAL UNION ALL
SELECT 8, 5, 501, 'Done',       DATE '2024-01-01' FROM DUAL;

Outputs:

ID CONTRACTID OPID OPSTATUS OPDATE
1 2 201 Done 2024-01-01 00:00:00
3 2 203 Done 2024-01-01 00:00:00
5 3 302 Processing 2024-01-02 00:00:00
6 4 401 Waiting 2024-01-05 00:00:00
8 5 501 Done 2024-01-01 00:00:00

fiddle

Upvotes: 2

Related Questions