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.

SELECT
  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"
FROM 
   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
WHERE 
   d.credtype IN ('cash','check','charge','other')
   AND c.trandate BETWEEN '2012-01-01' AND '2012-12-31'
   AND b.refname LIKE '%X%'
GROUP BY
   DataSet,YR,MON
ORDER BY  
   DataSet,YR,MON