List Patients with No Primary Diagnosis by Primary Provider

This is a relatively easy query that can be used to generate a list of patients for which no diagnosis has
been entered. Technically, the selection is for a missing Dx1, so if for some reason there is no Dx1, but
there is a Dx entered in Dx2, 3, or 4 the name will still appear on the list.

Note that we use LEFT OUTER JOINs between the Patients table and the Providers and PtCSU tables
so that if there is no primary provider entered, or the default claim setup is missing for some reason, we
will still see the patient name in the result set. Also notice the OR condition in the WHERE clause. Here
we are looking for a missing Dx link (null) or a link number of zero (which is what it should be if no Dx
has been selected). It is ALWAYS a good idea to put your OR expressions within parentheses so that the
query parser does not make it’s own decision about this matter. Here it would not really matter because
there is no ambiguity, but it is still a good habit and one that will save you much confusion when your
conditions are more complex.

SELECT
   Providers.ProvCode,
   Providers.ProvLName, 
   Providers.ProvFName,
   Patients.LastName, 
   Patients.FirstName, 
   Patients.ID
FROM
   sos.Patients
   LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum
   LEFT OUTER JOIN sos.PtCSU ON Patients.PtNum = PtCSU.PtNum
WHERE
   Patients.LicNum = 101 
   AND Patients.Flag = 0 
   AND PtCSU.TypeFlag = 'D' 
   AND (PtCSU.Dx1 IS NULL OR PtCSU.Dx1 = 0)
ORDER BY
   Providers.ProvCode,
   Patients.LastName, 
   Patients.FirstName,Patients.ID