Patients by Provider and Category

I would like to run a report that shows patients by provider & category.

At first glance this is a very basic, multi-table query, but some patients might not have an assigned primary provider and/or category. In those situations, it would be better to output something other than “NULL” or blank space. The following query shows two ways of doing that.

In addition, rather than doing columns for every data element, we can create provider and patient columns that have the full name information in a single column.

The WHERE clause filters out discharged and inactive list patients. The order of the results can be manipulated by changing the element order in the ORDER BY clause.

SELECT
  IF a.providernum IS NULL
     THEN 'No Primary Provider'
     ELSE (b.provlname+', ' + b.provfname + ' (' + b.provcode + ')')
  ENDIF AS "Provider",
  COALESCE(c.categdesc,'No Category') AS "Category",
 (a.lastname+', ' + a.firstname + ' / ' + a.id) AS "Patient"
FROM
  sos.patients a
  LEFT OUTER JOIN sos.providers b ON a.providernum = b.providernum
  LEFT OUTER JOIN sos.ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE
  a.flag = 0
  AND a.dischargedate IS NULL
ORDER BY "Provider","Category","Patient"