Screening Today’s Appointments for Outstanding Balances

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

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.