Patients with Unapplied Payments and Outstanding Balances

I need a report that shows me all of the patients who have an unapplied credit AND a patient balance. At the end of the month I need to run a this report and go back and apply credits to balances that somehow I have missed throughout the month. I have tried to do this by running the unapplied credit report but that report includes patients without a balance and is very time consuming to go into each of those accounts. For example, a patient received a statement for $75.00 yet she had $70 in unapplied credits.

While all the information you need is in the standard Aging by Patient report, this query filters that information so the results contain only those accounts and payors of interest.

SELECT
   (PtPayors.Age0to30 + PtPayors.Age31to60 + PtPayors.Age61to90 + PtPayors.Age91to120 + PtPayors.AgeOvr120) AS "BALANCE",
   PtPayors.Ageunapplied AS "UNAPPLIED",
   Patients.ID,
   Patients.LastName + ', ' + Patients.FirstName AS "Patient Name",
   Payors.PayorName + ' ' + Payors.FirstName AS "Payor Name",
   PtPayors.PayorNum
 FROM  
   SOS.Patients Patients
   LEFT OUTER JOIN SOS.PtPayors PtPayors ON Patients.PtNum= PtPayors.PtNum
   LEFT OUTER JOIN SOS.Payors Payors ON PtPayors.PayorNum=Payors.PayorNum
 WHERE
   "UNAPPLIED" > 0
   AND "BALANCE" > 0
 ORDER BY
  "Patient Name", Patients.ID, PtPayors.PayorNum

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.