======Patients by Provider and Category======

Back to [[QueryDirectory Query Directory]]

//
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.

%%(sql)
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
patients a
LEFT OUTER JOIN providers b ON a.providernum = b.providernum
LEFT OUTER JOIN ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE
a.flag = 0
AND a.dischargedate IS NULL
ORDER BY "Provider","Category","Patient"
%%


----
CategoryQueries