CODEWITHSUNDEEP
sql-server-2005maxwhere-clause
Jeremy McDevitt
Jeremy McDevitt

Reputation: 115

Showing all users not containing a certain value

I need a way to get a list for all patients who do not have a PCP relation even though patients have multiple relations. The relation also has to be in the current episode. Thanks in advance and I'm running SQL Server 2005

select 

distinct r.patient_id, 
r.episode_id, 
r.relation

from 
(select r.*, max(episode_id) over (partition by patient_id) as maxei from relationship r) r
join patient_custom pc
on r.patient_id = pc.patient_id
where health_home = 'y' --and r.relation = 'PCP'
order by r.patient_id, r.relation

enter image description here

Upvotes: 0

Views: 24

Answers (1)

David
David

Reputation: 34563

I think this will work... See comments inline.

SELECT
    PatientEpisode.Patient_ID,
    PatientEpisode.Epsode_ID

FROM (
    -- Find the list of patients and their current episode
    SELECT r.Patient_ID, MAX(r.Episode_ID) AS Episode_ID
    FROM Relationship r
       JOIN patient_custom pc
       ON r.Patient_ID = pc.Patient_ID
    WHERE pc.Health_Home = 'y'
    GROUP BY r.Patient_ID
) PatientEpisode

-- Now exclude any patients that DO have a "PCP" 
-- relationship in their current episode.
WHERE NOT EXISTS (
    SELECT *
    FROM Relationship
    WHERE Patient_ID = PatientEpisode.Patient_ID
    AND Episode_ID = PatientEpisode.Episode_ID
    AND Relation = 'PCP'
)

EDIT: I've added the Health_Home filter. To understand how this works, first run this SELECT by itself:

    SELECT r.Patient_ID, MAX(r.Episode_ID) AS Episode_ID
    FROM Relationship r
       JOIN patient_custom pc
       ON r.Patient_ID = pc.Patient_ID
    WHERE pc.Health_Home = 'y'
    GROUP BY r.Patient_ID

You'll see that it returns all of the patients as wel as the most recent episode for each patient.

Then once you have that data, it gets wrapped in the rest of the SQL to filter out the patients with current PCP relations.

Upvotes: 1

Related Questions