Revision [437]
Last edited on 2011-01-12 13:15:29 by SethKriegerAdditions:
======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
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
Deletions:
comments here appear in italics
paste your tested query here
