CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
SarekOfVulcan
SarekOfVulcan

Reputation: 1358

SQL - all patients who had only dental visits in 2010

I have a T-SQL query that's giving me the most recent office visit in 2010 that wasn't a dental visit. The relevant part of the query is:

AND pv.PatientVisitId IN (                
    SELECT Max(pv1.PatientVisitID)            
        FROM PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName NOT LIKE '%Dental%'    
    )

Now, I want to flip that around to get the most recent office visit for the patients who only had dental appointments. I keep hitting the wall here, though. Can anyone bust me through to the other side? :-)

Upvotes: 2

Views: 240

Answers (4)

k rey
k rey

Reputation: 631

AND pv.PatientVisitId IN (              
  SELECT MAX(pv1.PatientVisitID) 
  FROM PatientVisit pv1                
    JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID        
    JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId        
    JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId       
  WHERE pv1.PatientProfileID = pp.PatientProfileID                
    AND pv1.Visit < '2011-01-01'            
  HAVING SUM(CASE WHEN df1.ListName LIKE '%Dental%' THEN 1 ELSE 0 END) = COUNT(*)
)

Upvotes: 0

bernd_k
bernd_k

Reputation: 11966

The clue is to use where not exists

AND pv.PatientVisitId IN (                
    SELECT Max(pv1.PatientVisitID)            
        FROM PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName LIKE '%Dental%'    
    ) 
and not exists ( Select 1 from   PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName NOT LIKE '%Dental%'    
    )

Upvotes: 2

JNK
JNK

Reputation: 65157

If you are using SQL Server 2005 or later, you can use EXCEPT to exclude a group of records from another query.

So basically write your larger query (show me ALL the patients that had a visit this year!), then...

...
EXCEPT
SELECT pv1.PatientVisitID        
        FROM PatientVisit pv1        
            JOIN DoctorFacility df1 ON pv1.FacilityID = df1.DoctorFacilityID
            JOIN PatientVisitResource pvr1 ON pv1.PatientVisitId = pvr1.PatientVisitId
                JOIN DoctorFacility dfr ON pvr1.ResourceId = dfr.DoctorFacilityId
        WHERE pv1.PatientProfileID = pp.PatientProfileID        
            AND pv1.Visit < '2011-01-01'    
            AND df1.ListName NOT LIKE '%Dental%'

Something to bear in mind with this is you need to have the same columns in both queries (there can be SOME differences, but for our purposes make sure both the first SELECT and the EXCEPT query return the same fields).

Upvotes: 1

BlackICE
BlackICE

Reputation: 8926

probably numerous was to do this, but the first way that pops into my head is to compare the count(1) of patientvisit to the count(1) of patientvisit where df1.listname like '%Dental%', if those are equal, then they've only been to the dentist.

Upvotes: 2

Related Questions