======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