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