Referral Source Net Revenue

I need a query which gives me (a) a monthly revenue total and (b) total reversals, such as refunds, for patients with a specific referral source, regardless of provider.

i.e.,  May: $10,000 revenue, $2,000 neg adjustments for all patients with referral source Dr. X (from all datasets).

This is a pretty straightforward little query, but it shows the use of the aggregate function SUM, the date functions YEAR and MONTH, and the use of the LIKE expression to get a match with just part of the referral source’s last name. Note that the WHERE condition is looking only at the referral (last) name field. In this example, the result set would include any referral sources with an “X” in the (last) name field. Also, be sure to adjust the date range to match your need.

  a.licnum AS "DataSet", 
  YEAR(c.trandate) AS "YR",
  MONTH(c.trandate) AS "MON",
  SUM((IF c.amount > 0 THEN c.amount ELSE 0 ENDIF)) AS "TotRevenue",
  SUM((IF c.amount < 0 THEN c.amount ELSE 0 ENDIF)) AS "TotReversals",
  "TotRevenue" + "TotReversals" AS "NetRevenue"
   sos.patients a
   JOIN sos.refsrcs b ON a.refsrcnum = b.refsrcnum
   JOIN sos.journal c ON a.ptnum = c.ptnum
   JOIN sos.jcredits d ON c.jnum = d.jnum
   d.credtype IN ('cash','check','charge','other')
   AND c.trandate BETWEEN '2012-01-01' AND '2012-12-31'
   AND b.refname LIKE '%X%'

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.