Our auditors have requested a report of outstanding balances per client, as of Dec 31, 2011, separated by liability (Medicare, Medicaid, private insurance and self-pay). Insurance type is specified by “Coverage Type” on the Additional tab of the Insurance Carrie/Plan. We want to report patients in either the Active or Inactive lists, as long as they have a balance of some kind at the end of 2011.
Because we are looking for balances on a previous date, we have to tally up all the charges and payments on or before that date, then subtract the latter from the former to find the balance. To do that will require a bunch of embedded subqueries as you can see in the query below. If you want to run the query for a different date, be sure to replace ALL the instances of ‘2011-12-31’!
The output specified in this example will go to an HTML format file called AUDIT2011.HTML located in the C:\SOS folder. You can output the results to screen for inspection before outputting to file by inserting two dashes or slashes at the beginning of the last line. Just remove those characters when you are ready to generate the file. The benefit of outputting in HTML format is that you can view the results in a web browser, or open the file with Excel for additional massaging.
SELECT
a.lastname, a.firstname, a.id AS "Account ID",
-- Medicare
COALESCE((SELECT SUM(chgsplamt)
FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.carriers car ON ptp.payornum = car.payornum
WHERE chs.ptnum = a.ptnum AND car.coverage = 'C' AND chs.chgspldate <= '2011-12-31'),0) AS "Medicare Charges",
COALESCE((SELECT SUM(crsplamt)
FROM sos.jcrsplits crs
JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum
JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
JOIN sos.carriers car ON ptp.payornum = car.payornum
JOIN sos.jcredits cre ON crs.jnum = cre.jnum
JOIN sos.journal jou ON cre.jnum = jou.jnum
WHERE chs.ptnum = a.ptnum
AND car.coverage = 'C'
AND chs.chgspldate <= '2011-12-31'
AND jou.trandate <= '2011-12-31') ,0) AS "Medicare Credits",
("Medicare Charges" - "Medicare Credits") AS "Medicare Balance",
-- Medicaid
COALESCE((SELECT SUM(chgsplamt)
FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.carriers car ON ptp.payornum = car.payornum
WHERE chs.ptnum = a.ptnum AND car.coverage = 'D' AND chs.chgspldate <= '2011-12-31'),0) AS "Medicaid Charges",
COALESCE((SELECT SUM(crsplamt)
FROM sos.jcrsplits crs
JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum
JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
JOIN sos.carriers car ON ptp.payornum = car.payornum
JOIN sos.jcredits cre ON crs.jnum = cre.jnum
JOIN sos.journal jou ON cre.jnum = jou.jnum
WHERE chs.ptnum = a.ptnum
AND car.coverage = 'D'
AND chs.chgspldate <= '2011-12-31'
AND jou.trandate <= '2011-12-31' ) ,0) AS "Medicaid Credits",
("Medicaid Charges" - "Medicaid Credits") AS "Medicaid Balance",
-- Private Ins
COALESCE((SELECT SUM(chgsplamt)
FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.carriers car ON ptp.payornum = car.payornum
WHERE chs.ptnum = a.ptnum AND car.coverage NOT IN ('C','D') AND chs.chgspldate <= '2011-12-31'),0) AS "Private Ins Charges",
COALESCE((SELECT SUM(crsplamt)
FROM sos.jcrsplits crs
JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum
JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
JOIN sos.carriers car ON ptp.payornum = car.payornum
JOIN sos.jcredits cre ON crs.jnum = cre.jnum
JOIN sos.journal jou ON cre.jnum = jou.jnum
WHERE chs.ptnum = a.ptnum
AND car.coverage NOT IN ('C','D')
AND chs.chgspldate <= '2011-12-31'
AND jou.trandate <= '2011-12-31' ) ,0) AS "Private Ins Credits",
("Private Ins Charges" - "Private Ins Credits") AS "Private Ins Balance",
-- Self-Pay
COALESCE((SELECT SUM(chgsplamt)
FROM sos.jchgsplits chs JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum JOIN sos.payors pay ON ptp.payornum = pay.payornum
WHERE chs.ptnum = a.ptnum AND pay.payortype IN ('P','O') AND chs.chgspldate <= '2011-12-31'),0) AS "Self-Pay Charges",
COALESCE((SELECT SUM(crsplamt)
FROM sos.jcrsplits crs
JOIN sos.jchgsplits chs ON crs.chgsplnum = chs.chgsplnum
JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
JOIN sos.payors pay ON ptp.payornum = pay.payornum
JOIN sos.jcredits cre ON crs.jnum = cre.jnum
JOIN sos.journal jou ON cre.jnum = jou.jnum
WHERE chs.ptnum = a.ptnum
AND pay.payortype IN ('P','O')
AND chs.chgspldate <= '2011-12-31'
AND jou.trandate <= '2011-12-31' ) ,0) AS "Self-Pay Credits",
("Self-Pay Charges" - "Self-Pay Credits") AS "Self-Pay Balance"
FROM
sos.patients a
WHERE
"Medicare Balance" <> 0
OR "Medicaid Balance" <> 0
OR "Private Ins Balance" <> 0
OR "Self-Pay Balance" <> 0
ORDER BY
a.lastname, a.firstname, a.id
