Show number of referrals by referral source, sorted by provider, for a specified intake date range.
This query illustrates the use of an IF…ELSE…ENDIF expression in the SELECT list.
SELECT
COALESCE(c.provcode,'No Pri Prov') AS "Primary Provider",
IF a.refsrcnum IS NULL
THEN 'No Ref'
ELSE TRIM(b.Refname + ' '+b.firstname)
ENDIF AS "Source Name",
COALESCE(b.reftype,'') AS "Ref Type",
COUNT(*) AS "Referrals"
FROM
sos.patients a
LEFT OUTER JOIN sos.refsrcs b ON a.refsrcnum = b.refsrcnum
LEFT OUTER JOIN sos.providers c ON a.providernum = c.providernum
WHERE
a.intakedate BETWEEN '2000-01-01' AND '2008-08-20'
AND a.licnum = 101
GROUP BY "Primary Provider","Source Name", "Ref Type"
ORDER BY "Primary Provider","Source Name"
It is also possible to report the data by Rendering Provider, but because a single patient may be seen by two or more (rendering) providers, the grand total will likely be greater than the number of patients in the intake date range. Here is that variation of the query:
SELECT
DISTINCT COALESCE(c.provcode,'No Pri Prov') AS "Rendering Provider",
IF a.refsrcnum IS NULL
THEN 'No Ref'
ELSE TRIM(b.Refname + ' '+b.firstname)
ENDIF AS "Source Name",
COALESCE(b.reftype,'') AS "Ref Type",
COUNT(*) AS "Referrals"
FROM
sos.patients a
LEFT OUTER JOIN sos.refsrcs b ON a.refsrcnum = b.refsrcnum
LEFT OUTER JOIN sos.jcharges d ON a.ptnum = d.ptnum
LEFT OUTER JOIN sos.providers c ON d.providernum = c.providernum
WHERE
a.intakedate BETWEEN '2000-01-01' AND '2008-08-20'
AND a.licnum = 101
GROUP BY "Rendering Provider","Source Name", "Ref Type"
ORDER BY "Rendering Provider","Source Name"
admin says:
Yes, it will include both Active and Inactive. There is a column (field) in Patients called “flag” that holds a 0 if the patient is active and a 1 if inactive. As a result, if you just wanted one or the other you could add that condition in the WHERE clause. For example, let’s say you just want Active patients. You would add…
AND a.flag = 0
in the WHERE clause, just above the GROUP BY.
Jamie Gieraga says:
This may be a silly question, but I have to ask because I need to know what kind of number I’m looking at, and I’m still working on my understanding of how these queries work – Does this query, because it’s pulling by an Intake Date range, include patients who are both Active AND Inactive?
Thanks!