CODEWITHSUNDEEP
sqloracle-database
Miguel
Miguel

Reputation: 2079

Selecting max record only when the records before have already been set to Y

I'm having a mental block and hoping someone here would lend a hand. DDL for table is as follows.

CREATE TABLE TEST_QUERY
   (   APPROVER VARCHAR2(20 BYTE), 
   STEP VARCHAR2(20 BYTE), 
   APPROVED VARCHAR2(20 BYTE), 
   DOCUMENT VARCHAR2(20 BYTE)
   )

Data in table is as follows.

Insert into TEST_QUERY (APPROVER,STEP,APPROVED,DOCUMENT) values ('JHON','1','Y','A1');
Insert into TEST_QUERY (APPROVER,STEP,APPROVED,DOCUMENT) values ('PAT','2','Y','A1');
Insert into TEST_QUERY (APPROVER,STEP,APPROVED,DOCUMENT) values ('FRANK','3','Y','A1');
Insert into TEST_QUERY (APPROVER,STEP,APPROVED,DOCUMENT) values ('BK1','4',null,'A1');
Insert into TEST_QUERY (APPROVER,STEP,APPROVED,DOCUMENT) values ('BK2','5',null,'A1');
Insert into TEST_QUERY (APPROVER,STEP,APPROVED,DOCUMENT) values ('JHON','1',null,'A2');
Insert into TEST_QUERY (APPROVER,STEP,APPROVED,DOCUMENT) values ('PAT','2',null,'A2');
Insert into TEST_QUERY (APPROVER,STEP,APPROVED,DOCUMENT) values ('BK1','3',null,'A2');

For every document in this table these is a step column in sequential order. It goes from 1...infinity. The key here is that BK1 and BK2 entries will always be the entry on the table with the max step. The query I'm trying to write is a query that Identifies documents that have all smaller steps (by document) set to 'Y' but Bk1 and BK2 are still null.

so the expected output for this query using the sample data would be.

Approver     Step     Document
BK1          4        A1
BK2          5        A1

The A2 document would not show up in this query because there are still steps less than the max step that are set to null. Any Ideas on how to accomplish this one?

Here is what I have thus far.( this is obviously off)

SELECT  max (step) ,
document , 
approver  
from test_query 
where approved is  null 
and approver in  ( 'BK1', 'BK2')
group by document ,  approver

Thanks.

Upvotes: 1

Views: 43

Answers (2)

Bikash Ranjan Bhoi
Bikash Ranjan Bhoi

Reputation: 516

Please Try this :

SELECT t1.approver, t1.step, t1.document
    FROM test_query t1 JOIN test_query t2 
      ON (t1.document = t2.document)
   WHERE t1.approver IN ('BK1', 'BK2') AND t2.approver NOT IN ('BK1', 'BK2')
GROUP BY t1.document, t1.approver, t1.step
  HAVING COUNT (t1.approved) = 0 AND COUNT (t2.approved) = COUNT (1)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use group by:

select document
from test_query
group by document
having sum(case when approver in ('BK1', 'BK2') and approved is not null then 1 else 0 end) = 0 and
       sum(case when approver not in ('BK1', 'BK2') and approved is not null then 1 else 0 end) = count(*) - 2;

If you want the detailed data, then this should be combined with the original rows:

select tq.*
from test_query tq
where approved is null and
      document in (select document
                   from test_query
                   group by document
                   having sum(case when approver in ('BK1', 'BK2') and approved is not null then 1 else 0 end) = 0 and
                          sum(case when approver not in ('BK1', 'BK2') and approved is not null then 1 else 0 end) = count(*) - 2
                  );

Upvotes: 1

Related Questions