Count of Referrals by Primary Provider and Referral Source, for Intake Date Range

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"

0 thoughts on “Count of Referrals by Primary Provider and Referral Source, for Intake Date Range

  • 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!

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.