Exclude records where particular value exists in related record-CodePudding

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

CodePudding user response：

To filter a subset of values within group you can use group by having clause

select distinct dbo.caseid 
from dbo.vw_caseaudit 
group by dbo.caseid 
having sum(dbo.auditcasesubstatusname in ('Missing Document No Action', 'Missing Document Action Required', 'Referral Request')) = 0 and 
       sum(dbo.auditcasesubstatusname in ('Checklist Complete', 'All Documents Available', 'Review Complete')) > 0

CodePudding user response：

You can use a conditional case expression:

with v as (
    select * ,
        Sum(case when auditcasesubstatusname not in ('Checklist Complete', 'All Documents Available', 'Review Complete') then 1 else 0 end)
            over(partition by caseid) invalid
    from t
)
select *
from v
where invalid=0

CodePudding user response：

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'))

CodePudding user response：

If I understand your question correctly, did you try to use in for auditcasesubstatusname? Something like auditcasesubstatusname in (List of possible values) in sql query.