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"