We would like to do a daily query to alert us to any patients with an appointment for today who have an outstanding balance of $120 or more and have been billed at least once for unpaid services.
This is a pretty straight-forward query, with the exception of a subquery in the WHERE clause. The subquery determines if there are any non-insurance chargesplits carrying a balance that show a billing date, indicating that there has been at least some billing done for one or more of the outstanding services.
SELECT DISTINCT
a.SITENAME + ' (' + a.SITECODE + ')' As site,
(a.PROVLNAME + ', ' + a.PROVFNAME + ' (' + a.PROVCODE + ')') As provider,
a.PTFULLNAME,
a.ID,
b.PTBALANCE,
a.APPTDATE
FROM
sos.rv_appts a
JOIN sos.pt_noninsbalance b ON a.PTNUM = b.PTNUM
WHERE
b.ptbalance >= 120
AND a.apptdate = Today()
AND (SELECT COUNT(*)
FROM sos.jchgsplits cs
JOIN sos.ptpayors ptp ON cs.ptpayornum = ptp.ptpayornum
JOIN sos.payors pay ON ptp.payornum = pay.payornum
WHERE cs.ptnum = a.ptnum
AND pay.payortype <> 'I'
AND cs.ChgSplBal > 0
AND cs.lastbilled IS NOT NULL ) > 0
ORDER BY
site, provider, a.ptfullname, a.id
