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

Question

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

 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;

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

user_1367454 · Answer

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'

user_5070879 · Answer

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:

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

2 Answers