CODEWITHSUNDEEP
sql-serversql-server-2008-r2group-bywhere-clausehaving
Jeremy McDevitt
Jeremy McDevitt

Reputation: 115

Selecting clients who have an association with one column value, but not another

I am trying to make a report that list active clients (case_status = 'A') who have the old version of a document attached to them without the newer, but the way our database is laid out all documents are listed in one table, so some clients will have a dozen different documents attached to them. Some of them being duplicate documents.

Ex.

patient_id     Doc_code
p01            doc1
p01            doc2
p01            doc3
po1            doc4
p02            doc2
po2            doc3

I need to know who has a doc_code of 'DIAGDOC' and doesn't have a doc_code of 'DIAGDOC5', so we know who needs to be updated. 

select 

de.patient_id,
de.episode_id

from doc_entity de
join patient p
on p.patient_id = de.patient_id and p.episode_id = de.episode_id
where p.case_status = 'A' 

group by de.patient_id, de.episode_id, de.doc_code
having (de.doc_code in ('DIAGDOC'))and (de.doc_code not in ('DIAGDOC5'))
order by de.patient_id, de.episode_id

Upvotes: 0

Views: 30

Answers (2)

Neeraj
Neeraj

Reputation: 316

Does the below work - 

select 
    aa.patient_id, aa.episode_id 
from 
(
    select  
            de.patient_id
        ,   de.episode_id
        ,   MAX(case doc_code WHEN 'DIAGDOC'  THEN 'YES'  ELSE 'NO' end) as 'DIAGDOCExists'
        ,   MAX(case doc_code WHEN 'DIAGDOC5' THEN 'YES'  ELSE 'NO' end) as 'DIAGDOC5Exists'
    from 
        doc_entity de join patient p on p.patient_id = de.patient_id and p.episode_id = de.episode_id
    where 
        p.case_status = 'A' 
    group by 
        de.patient_id, de.episode_id
) as aa where aa.DIAGDOCExists = 'YES' and aa.DIAGDOC5Exists = 'NO'

Upvotes: 1

MarkD
MarkD

Reputation: 1063

Would an except clause work?

select
de.patient_id,
de.episode_id

from doc_entity de
join patient p
on p.patient_id = de.patient_id and p.episode_id = de.episode_id
where p.case_status = 'A' 
and de.doc_code = 'DIAGDOC'

EXCEPT

select 

de.patient_id,
de.episode_id

from doc_entity de
join patient p
on p.patient_id = de.patient_id and p.episode_id = de.episode_id
where p.case_status = 'A' 
and de.doc_code = 'DIAGDOC5'*

the top block returns all rows with diagdoc and the except block removes all those with diagdoc5 leaving only those with an out of date document

Upvotes: 1

Related Questions