For multiple patient categories, we would like totals billed AND collected for a specified date range.
The following query provides the desired totals, but it is important to note that the payment total is a total of any payments received during the period, not just payments for services rendered during the period. In other words, the payments probably include those for previous services and maybe even some prepayments. Also note that we are using SPLIT amounts for both charges and credits, so if you have corruption that makes the sum of the splits on a transaction different than the transaction amount, the total won’t match reports or queries that use the main amount field.
The following is actually TWO similar queries, one for payments and one for fees, with the use of UNION to combine the results of each query into a single result set. UNIONed queries must not have their own ORDER statement. Instead, at the end you can specify an ORDER BY followed by the number of the column on which you want to sort, as shown below.
SELECT
c.categcode AS Category
,'Payments' AS "Type"
,SUM(COALESCE(b.crsplamt,0)) AS "Total"
FROM
sos.patients a
JOIN sos.rv_creditsplits b ON a.ptnum = b.ptnum
JOIN sos.ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE
a.licnum = 101
AND b.credtype IN ('CASH','CHECK','CHARGE','OTHER')
AND b.cre_date BETWEEN '2000-01-01' AND '2014-12-31'
GROUP BY
c.categcode
UNION
SELECT
c.categcode AS Category
,'Fees'
,SUM(COALESCE(b.chgsplamt,0))
FROM
sos.patients a
JOIN sos.rv_charges b ON a.ptnum = b.ptnum
JOIN sos.ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE
a.licnum = 101
AND b.trandate BETWEEN '2000-01-01' AND '2014-12-31'
GROUP BY
c.categcode
ORDER BY
1,2
