Count of Referrals by Primary Provider and Referral Source, for Intake Date Range
Back to Query Directory
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
patients a
LEFT OUTER JOIN refsrcs b ON a.refsrcnum = b.refsrcnum
LEFT OUTER JOIN 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"
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
patients a
LEFT OUTER JOIN refsrcs b ON a.refsrcnum = b.refsrcnum
LEFT OUTER JOIN 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"
CategoryQueries