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

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 , 'HardCopies' as RegStatus , 'ValidData' as DocStatus
     union all 
      select 'ABC' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus
     union all  
      select 'ABC' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus
     union all 
        select 'ABC' as ID , 'HardCopies' as RegStatus , 'InValidData' as DocStatus
     union all 
        select 'EDG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus
     union all 
      select 'EDG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus
     union all  
      select 'EDG' as ID , 'Onboarding' as RegStatus , 'ValidData' as DocStatus
     union all 
        select 'JFG' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus
     union all 
        select 'JFG' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus
     union all 
      select 'JFG' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus
     union all  
      select 'JFG' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus
     union all 
        select 'MON' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus
     union all 
      select 'MON' as ID , 'SoftCopies' as RegStatus , 'InValidData' as DocStatus
     union all 
      select 'MON' as ID , 'HardCopies' as RegStatus , 'ValidData' as DocStatus
     union all  
      select 'MON' as ID , 'HardCopies' as RegStatus , 'InValidData' as DocStatus
 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;

CodePudding user response：

Using QUALIFY and conditional windowed COUNT:

SELECT *
FROM table
QUALIFY COUNT_IF(DocStatus='InValidData') OVER(PARTITION BY ID) = 0
   OR DocStatus = 'InValidData';

Output:

CodePudding user response：

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'