Count of Unique Patients for Period, Payor, and Service

I would like to determine the number of unique patients seen for particular services billed to a designated set of payors.

This query can deliver the goods for one or more services and one or more payors. Substitute your desired list of service codes and payor numbers, separating items with commas as in the example below. You can have just one item in the lists or as many as you like. The GROUP BY ROLLUP structure allows you to see subtotals and the grand total in addition the number of patients for each combination. Wherever you see (NULL) in the result set, just interpret it as meaning “ALL”.

SELECT
   b.payornum, a.srvcode,COUNT(DISTINCT a.ptnum) AS "Clients Seen"
FROM
   sos.rv_charges a
   JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
WHERE
   amount > 0
   AND trandate BETWEEN '2011-01-01' AND '2011-12-31'
   AND srvcode IN ('OV','CD')
   AND b.payornum IN (111, 611)
GROUP BY ROLLUP
   (b.payornum,a.srvcode)