Revising WHERE statement to include/exclude certain patients-CodePudding

2 similar SQL Server problems:

db_members is a member-level table classified by patient_id, provider_id and level_of_care_id.

The objective is to pull patients from differing levels of aggregation.

In the first query, we want to extract all patients from these 6 levels of care but for LOC 100.007 we need to exclude patients from one provider_id ('119282’);

In the second query, we want to extract all patients from these 6 levels of care but for LOC 100.007 we need to include only provider_id ('119282’) patients;

(This is a simplified version of the query)

select patient_id, provider_id, level_of_care_cd, 
from db_members 
where  level_of_care_cd in ('100.001', '100.004', '100.007', '100.022', '100.034', '100.037')      
group by level_of_care_cd, provider_id

CodePudding user response：

Exclude a provider with one LOC

where level_of_care_cd in ( <full list> )
    and not (level_of_care_cd = '100.007' and provider_id = '119282')

This is equivalent as long as nulls don't come into play:

where level_of_care_cd in ( <full list> )
    and (level_of_care_cd <> '100.007' or provider_id <> '119282')

Allow one provider with one LOC

Be careful with nulls:

where level_of_care_cd in ( <full list> )
    and not (level_of_care_cd = '100.007' and provider_id <> '119282')

where level_of_care_cd in ( <full list> )
    and (level_of_care_cd <> '100.007' or provider_id = '119282')

The equivalencies are the result of De Morgan's Laws. The forms using not will probably read most naturally to the way people think. Remember that or has lower precedence than and so parentheses are important.

CodePudding user response：

Try this

    select case when level_of_care_cd ='100.007' and
       patient_id not in (Select patient_id from patients where 
      provider_id='119282')
        then patient_id end as patient_id 
      , provider_id, 
       level_of_care_cd, 
       from db_members 
      where  level_of_care_cd in ('100.001', '100.004', '100.022', ' 
     '100.007`,   100.034', '100.037')       
      group by level_of_care_cd, provider_id