======Patients with Unapplied Payments and Outstanding Balances======

Back to [[QueryDirectory Query Directory]]

// 
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.

%%(sql)
 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
%%


----
CategoryQueries