======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