CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Karthik
Karthik

Reputation: 481

SQL: Exclude only if Row has a value for the same ID

Requirement: In a table, if for the same ID, if Only for those RegStatus in (Hardcopies,SoftCopies) if there is a status 'InvalidData' , then include only that row and ignore the rest. But if for the same ID, if there is no status 'InvalidData' , don't ignore any rows

enter image description here

 with source as (
       select 'ABC' as ID , 'SoftCopies' as RegStatus , 'ValidData' as DocStatus, 'ID of  signatory' as Name
         union all
          select 'ABC' as ID , 'SoftCopies' as RegStatus , 'ValidData' as DocStatus, 'Taxable Status ' as Name
         union all
          select 'ABC' as ID , 'SoftCopies' as RegStatus , 'ValidData' as DocStatus, 'Bank Letter' as Name
         union all
            select 'ABC' as ID , 'SoftCopies' as RegStatus , 'InValidData' as DocStatus, 'Articles of Association' as Name
         union all
            select 'EDG' as ID , 'ABC Pending' as RegStatus , 'ValidData' as DocStatus, 'ID of  signatory' as Name
         union all
          select 'EDG' as ID , 'ABC Pending' as RegStatus , 'ValidData' as DocStatus, 'Questionnaire document' as Name
         union all
          select 'EDG' as ID , 'ABC Pending' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
            select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Questionnaire document' as Name
         union all
            select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Taxable Status Certificate for GB' as Name
         union all
          select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Bank Letter' as Name
         union all
          select 'JFG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
            select 'MON' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
          select 'MON' as ID , 'HardCopies' as RegStatus , 'InValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
          select 'MON' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus, 'Trade Register Extract' as Name
         union all
          select 'MON' as ID , 'Onboarding' as RegStatus , 'InValidData' as DocStatus, 'Trade Register Extract' as Name
     union all
      select 'XYZ' as ID , 'AcceptanceReview' as RegStatus , 'ValidData' as DocStatus , 'Trade Register Extract' as Name
     union all
      select 'xyz' as ID , 'AcceptanceReview' as RegStatus , 'InValidData' as DocStatus , 'Trade Register Extract' as Name
     union all
         select 'XYZ' as ID , 'PacketSubmitted' as RegStatus , 'ValidData' as DocStatus , 'Trade Register Extract' as Name
       )
     select * from source;

enter image description here

UPDATE :Requirement Update as below

If the RegStatus column shows any without HardCopies or SoftCopies and has ABC Pending, Then only one entry is required for each unique ID as Example EDG

If the RegStatus column shows any without HardCopies or SoftCopies or ABC Pending, Then leave it as is it Example JFG

If the ‘RegStatus’ shows: ‘HardCopies’ OR ‘SoftCopies’ Then we need multiple entries for each of the documents under that ID and where the DocStatus status is 'invalid data’. Example MON

Upvotes: 2

Views: 188

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176114

Using QUALIFY and conditional windowed COUNT:

SELECT *
FROM table
QUALIFY 
   COUNT_IF(DocStatus='InValidData' AND RegStatus IN('HardCopies','SoftCopies')) 
           OVER(PARTITION BY ID) = 0 OR DocStatus = 'InValidData';

Output:

enter image description here

Upvotes: 3

eshirvana
eshirvana

Reputation: 24613

here is one way:

select * from source
qualify max(case when DocStatus = 'InValidData' and RegStatus in ('HardCopies', 'SoftCopies') then 1 else 0 end) over (partition by ID) = 0 
or DocStatus = 'InValidData'

Upvotes: 3

Related Questions