About
This report shows list of patients with either (or both) a nonzero credit (either insurance or patient-side) and a nonzero balance (either insurance or patient-side), along with next scheduled appointment (if any).
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- Includes all patient statuses, not just active patients.
- If no future appointment is scheduled, the next_appt field will be blank.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select a.patno, fname, lname, round(copaybal,2) as patient_balance, pat_credit, round(currbal,2) as ins_balance, ins_credit, next_appt from
(select patno, round(sum(ins_amount),2) as ins_credit, round(sum(pat_amount),2) as pat_credit from patient_credit_acct group by patno
) a
left outer join register on a.patno = register.patno
left outer join (select min(appt_date) as next_appt, patno from schedule where appt_date >= cast('today' as date) and (visit_status is null or visit_status = '') group by patno) s on s.patno=a.patno
where (copaybal >= 0.01 or pat_credit >= 0.01 or pat_credit <=-0.01 or ins_credit >=0.01 or ins_credit <= -0.01) and lname <> 'TESTPATIENT'SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select a.patno, fname, lname, round(copaybal,2) as patient_balance, pat_credit, round(currbal,2) as ins_balance, ins_credit, next_appt from (select patno, round(sum(ins_amount),2) as ins_credit, round(sum(pat_amount),2) as pat_credit from patient_credit_acct group by patno ) a left outer join register on a.patno = register.patno left outer join (select min(appt_date) as next_appt, patno from schedule where appt_date >= curdate() and (visit_status is null or visit_status = '') group by patno) s on s.patno=a.patno where (copaybal >= 0.01 or pat_credit >= 0.01 or pat_credit <=-0.01 or ins_credit >=0.01 or ins_credit <= -0.01) and lname <> 'TESTPATIENT'
