michael arsnow February 2016

Join Criteria Priority Issue

Hello Stack overflow community,

I am new to SQL, so I might be overlooking something simple. For some reason when I add the t2 criteria to the WHERE clause, the t1. criteria are no longer followed. For example when I run the query without the specifications on Diagnosis, the I only see inpatients between the specified AdmitDateTimes, that are 18 years and older. However when I add the Diagnosis criteria, my joined table has patients of any status, from any AdmitDateTime and with any age.

Any and all assistance is greatly appreciated

THANKS!

SELECT TOP 1000 
       ReportTraining.dbo.IatricGetAge(t1.BirthDateTime,t1.ServiceDateTime) AS 'Age'
      ,t1.[SourceID]
      ,t1.[VisitID]
      ,t1.[AbstractStatusDateTime]
      ,t1.[AccountNumber]
      ,t1.[AdmitDateTime]
      ,t1.[AdmitFromEr]
      ,t1.[BirthDateTime]
      ,t1.[DischargeDateTime]
      ,t1.[DischargeDispositionID]
      ,t1.[DischargeDispositionName]
      ,t1.[FacilityID]
      ,t1.[FacilityName]
      ,t1.[LocationID]
      ,t1.[LocationName]
      ,t1.[Name]
      ,t1.[ObservationDateTime]
      ,t1.[ObservationPatient]
      ,t1.[PatientStatusName]
      ,t1.[PtStatus]
      ,t1.[ReasonForVisit]
      ,t1.[ServiceDateTime]
      ,t1.[Status]
      ,t1.[UnitNumber]
      ,t1.[RowUpdateDateTime]
      ,t1.[AbstractID]
      ,t1.[PatientID]
      ,t2.[VisitID]
      ,t2.[DiagnosisSeqID]
      ,t2.[Diagnosis]
      ,t2.[AbstractID]
  FROM  [Livedb].[dbo].[AbstractData] t1
        INNER JOIN [Livedb].[dbo].[AbsDrgDiagnoses] t2      ON (t2.AbstractID = t1.AbstractID   AND
                                                                t2.VisitID    = t1.VisitID)

  WHERE t1.AdmitDateTime BETWEEN '2016-01-01' AND '2016-01-31' AND
  t1.PatientStatusName LIKE 'INPATIENT' AND
  ReportTraining.dbo.IatricGetAge(t1.BirthDateTime,t1.ServiceDateTime)>= '18' AND
  t2.Diagnosis IN ('G62.1' , 'I42.6' , 'K29.2' , 'K70' , 'T39' , 'T40' , 'T41.3' , 'T51.91XA' , 'Z71.4        

Answers


D Stanley February 2016

AND is evaluated before OR - whenever you mix them you should add parentheses to make sure your conditions are grouped appropriately. In your case you likely need to group the two OR conditions so they are evaluated as one group:

WHERE ... AND ... AND ...
   AND
   (  t2.Diagnosis IN (...) OR
      t2.Diagnosis LIKE 'F%'    
   )
   AND
       ....

Post Status

Asked in February 2016
Viewed 2,716 times
Voted 11
Answered 1 times

Search




Leave an answer