CODEWITHSUNDEEP
sql
user3093389
user3093389

Reputation: 3

How do you exclude with two different conditions

Wonder if anyone would know how to do this?

Basically I only need to return results where the Type equals 'I' so I deleted the line ----AND PatientType = 'I' ----which works like a charm, but then I find out that I have some records with same MRN that contain both types of of "I' and "O". If they had a "O" then I can't count their "I". Now that means I have to delete the ones that meet that condition. Yes you guessed it I am stuck again. LOL

  SELECT DISTINCT 
       p.id, 
       v.PatientID,
       p.firstname, 
       p.lastname, 
       p.dob, 
       p.mrn, 
       s.SmokeStatus,  
       v.VisitNo, 
       s.VisitID,
       v.ID,
   v.AdmitedDate

   FROM
       tblPatient p 
       JOIN tblPatientVisit v ON p.id = v.PatientID
       JOIN tblPatientSmokingScreenOrder s ON v.id = s.VisitID
   WHERE 
       isdate(p.DOB) = 1 
       AND CONVERT(date,p.DOB) <'12/10/2000' 
       AND isdate(v.AdmitedDate) = 1 
       AND CONVERT(date,v.AdmitedDate) > '06/16/2013 00:00' 

   AND v.PatientType = 'I'
   AND s.TobaccoType = 'Cigarettes' OR s.TobaccoType='Cigars' or s.TobaccoType='Pipes'
   AND v.PatientType !='O'
   AND v.PatientType !='2'
   AND v.PatientType = 'I'

   order by MRN

Upvotes: 0

Views: 49

Answers (1)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

add brackets, because you have a problem of operator precedence between OR and AND.

AND (s.TobaccoType = 'Cigarettes' OR s.TobaccoType='Cigars' or s.TobaccoType='Pipes')

or better, IN clause

AND s.TobaccoType IN ('Cigarettes', 'Cigars', 'Pipes')

Then, add a NOT EXISTS clause (if I understand well) to exclude the patients with "an O in any visit"

AND NOT EXISTS (select null from
                tblPatientVisit 
                where PatientId = p.id
                and PatientType = 'O')

so the entire query should be (not sure where the v.PatientType != '2' should go)

SELECT DISTINCT 
       p.id, 
       v.PatientID,
       p.firstname, 
       p.lastname, 
       p.dob, 
       p.mrn, 
       s.SmokeStatus,  
       v.VisitNo, 
       s.VisitID,
       v.ID,
       v.AdmitedDate

   FROM
       tblPatient p 
       JOIN tblPatientVisit v ON p.id = v.PatientID
       JOIN tblPatientSmokingScreenOrder s ON v.id = s.VisitID
   WHERE 
       isdate(p.DOB) = 1 
       AND CONVERT(date,p.DOB) <'12/10/2000' 
       AND isdate(v.AdmitedDate) = 1 
       AND CONVERT(date,v.AdmitedDate) > '06/16/2013 00:00' 
       AND v.PatientType = 'I'
       AND v.PatientType !='2'
       AND s.TobaccoType IN ('Cigarettes', 'Cigars', 'Pipes')
       AND NOT EXISTS (select null from
                    tblPatientVisit 
                    where PatientId = p.id
                    and PatientType = 'O')

Upvotes: 2

Related Questions