Exclude records where particular value exists in related record

The below dataset displays the audit trail of a “review” record that has gone through a number of statuses.

caseauditid caseid  casetypename            auditcasestatusname auditcasesubstatusname
1140        1   Open Market Peer Review     Review             All Documents Available
1141        1   Open Market Peer Review     Review             Review Complete
883         41  Open Market Peer Review     Review             Checklist Review
1051        41  Open Market Peer Review     Review             Checklist Review
1052        41  Open Market Peer Review     Review             Checklist Complete
1053        41  Open Market Peer Review     Review             All Documents Available
1054        41  Open Market Peer Review     Review             All Documents Available
1055        41  Open Market Peer Review     Review             Referral Request

What I’m trying to achieve is only display the case ids where the auditcasesubstatusname is one of (‘Checklist Complete’, ‘All Documents Available’, ‘Review Complete’) and does not have a value of (‘Missing Document No Action’, ‘Missing Document Action Required’, ‘Referral Request’)

In this example, case id 1 would be returned but not case id 41 due to it having a value of “Referral Request”

Having a real coders block right now so not sure how to best achieve this in SQL Server. Any ideas?

Thanks

Answer

I think you can try this:

select distinct caseid from Table1 
where auditcasesubstatusname in('Checklist Complete', 'All Documents Available', 'Review Complete') 
and caseid not in(
    select distinct caseid from Table1 
    where auditcasesubstatusname in('Missing Document No Action', 'Missing Document Action Required', 'Referral Request'))