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
 开发者_如何学JAVA       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? :-)

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

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.

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

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(*)
)