Patients with Primary Provider and Primary Dx, Sorted by Provider Code

This query demonstrates the use of IF expressions to display “None” rather than “NULL” wherever there is no primary provider or dx.

SELECT 
  Patients.LastName, 
  Patients.FirstName, 
  Patients.ID, 
  (IF Patients.ProviderNum IS NULL 
  THEN 'None' 
  ELSE (Providers.ProvCode +':'+ Providers.ProvLName +', ' + Providers.ProvFName) 
  END IF) AS "Provider", 

  (IF ptcsudx.DxCode1 IS NULL THEN 'None' 
  ELSE ptcsudx.DxCode1 
  END IF) AS "PrimaryDx" 

FROM 
  sos.Patients 
  LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum 
  LEFT OUTER JOIN sos.PtCSU ON Patients.PtNum = PtCSU.PtNum 
  LEFT OUTER JOIN sos.ptcsudx ON PtCSU.ptcsunum =ptcsudx.ptcsunum 
WHERE 
  Patients.LicNum = 101 
  AND Patients.Flag = 0 
  AND Patients.DischargeDate IS NULL 
  AND PtCSU.TypeFlag = 'D' 
ORDER BY 
  Providers.ProvCode,Patients.LastName,Patients.FirstName,Patients.id 
;
OUTPUT TO c:\sos\provptdxlist.html FORMAT HTML