| There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator. |
About
This report shows all patients with a balance, aged into 0-30, 31-60, 61-90, 91-120, and 121+ day buckets. This report also shows guarantor ID and name, patient status PCP, and the next appointment scheduled (if applicable).
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- “Aging” is defined as how many days between the run date and patient responsibility date. Patient responsibility date is defined as the date on which the insurance company adjudicated the claim and sent a balance to patient responsibility.
- This report may take 30-45 seconds to execute if you have a large practice and/or many open balances.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
with b as
(
select guarantor_id, first_name as guarantor_first, last_name as guarantor_last, patno as chart, fname as patient_First, lname as patient_last, staffname as pcp, copaybalance, status_pat, effective_dor,
datediff(day, cast(effective_dor as date), cast('today' as date)) as elapsed, case when datediff(day, cast(effective_dor as date), cast('today' as date)) <31 then '0-30' when datediff(day, cast(effective_dor as date), cast('today' as date)) <61 then '31-60' when datediff(day, cast(effective_dor as date), cast('today' as date)) < 91 then '61-90' when datediff(day, cast(effective_dor as date), cast('today' as date)) < 121 then '91-120' else '121+' end as buckets
from
(
select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id, staffname, first_name, last_name, status_pat from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01) at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2','4')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
INNER JOIN STAFF1 on staff1.staffid = register.addr_id
inner join contact on contact.id = register.guarantor_id
order by patno
)
)
select distinct guarantor_id, guarantor_first, guarantor_last, b.chart, patient_first, patient_last, pcp , status_pat,
a1.b1 as B0_30, a2.b2 as B31_60, a3.b3 as B61_90, a4.b4 as B91_120, a5.b5 AS B121, next_appt
from b
left outer join (select coalesce(sum(copaybalance),0) as b1, chart from b where buckets='0-30' group by chart) a1 on a1.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b2, chart from b where buckets='31-60' group by chart) a2 on a2.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b3, chart from b where buckets='61-90' group by chart) a3 on a3.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b4, chart from b where buckets='91-120' group by chart) a4 on a4.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b5, chart from b where buckets='121+' group by chart) a5 on a5.chart = b.chart
left outer join (select patno, min(appt_Date) as next_appt from schedule where visit_status is null and appt_date >= cast('today' as date) group by patno) s on s.patno = b.chartSQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select distinct guarantor_id, guarantor_first, guarantor_last, b.chart, patient_first, patient_last, pcp , status_pat,
a1.b1 as B0_30, a2.b2 as B31_60, a3.b3 as B61_90, a4.b4 as B91_120, a5.b5 AS B121, next_appt
from (select distinct trnsxno, guarantor_id, first_name as guarantor_first, last_name as guarantor_last, patno as chart, fname as patient_First, lname as patient_last, staffname as pcp, copaybalance, status_pat, effective_dor,
timestampdiff(day,cast(effective_dor as date),curdate()) as elapsed, case when timestampdiff(day,cast(effective_dor as date),curdate()) <31 then '0-30'
when timestampdiff(day,cast(effective_dor as date),curdate()) <61 then '31-60' when timestampdiff(day,cast(effective_dor as date),curdate()) < 91 then '61-90'
when timestampdiff(day,cast(effective_dor as date),curdate()) < 121 then '91-120' else '121+' end as buckets
from (select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id, staffname, first_name, last_name, status_pat from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01) at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
INNER JOIN STAFF1 on staff1.staffid = register.addr_id
inner join contact on contact.id = register.guarantor_id
order by patno ) t1) b
left outer join (select coalesce(sum(copaybalance),0) as b1, chart from (select distinct trnsxno, guarantor_id, first_name as guarantor_first, last_name as guarantor_last, patno as chart, fname as patient_First, lname as patient_last, staffname as pcp, copaybalance, status_pat, effective_dor,
timestampdiff(day,cast(effective_dor as date),curdate()) as elapsed, case when timestampdiff(day,cast(effective_dor as date),curdate()) <31 then '0-30'
when timestampdiff(day,cast(effective_dor as date),curdate()) <61 then '31-60' when timestampdiff(day,cast(effective_dor as date),curdate()) < 91 then '61-90'
when timestampdiff(day,cast(effective_dor as date),curdate()) < 121 then '91-120' else '121+' end as buckets
from (select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id, staffname, first_name, last_name, status_pat from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01) at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
INNER JOIN STAFF1 on staff1.staffid = register.addr_id
inner join contact on contact.id = register.guarantor_id
order by patno ) t1) b where buckets='0-30' group by chart) a1 on a1.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b2, chart from (select distinct trnsxno, guarantor_id, first_name as guarantor_first, last_name as guarantor_last, patno as chart, fname as patient_First, lname as patient_last, staffname as pcp, copaybalance, status_pat, effective_dor,
timestampdiff(day,cast(effective_dor as date),curdate()) as elapsed, case when timestampdiff(day,cast(effective_dor as date),curdate()) <31 then '0-30'
when timestampdiff(day,cast(effective_dor as date),curdate()) <61 then '31-60' when timestampdiff(day,cast(effective_dor as date),curdate()) < 91 then '61-90'
when timestampdiff(day,cast(effective_dor as date),curdate()) < 121 then '91-120' else '121+' end as buckets
from (select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id, staffname, first_name, last_name, status_pat from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01) at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
INNER JOIN STAFF1 on staff1.staffid = register.addr_id
inner join contact on contact.id = register.guarantor_id
order by patno ) t1) b where buckets='31-60' group by chart) a2 on a2.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b3, chart from (select distinct trnsxno, guarantor_id, first_name as guarantor_first, last_name as guarantor_last, patno as chart, fname as patient_First, lname as patient_last, staffname as pcp, copaybalance, status_pat, effective_dor,
timestampdiff(day,cast(effective_dor as date),curdate()) as elapsed, case when timestampdiff(day,cast(effective_dor as date),curdate()) <31 then '0-30'
when timestampdiff(day,cast(effective_dor as date),curdate()) <61 then '31-60' when timestampdiff(day,cast(effective_dor as date),curdate()) < 91 then '61-90'
when timestampdiff(day,cast(effective_dor as date),curdate()) < 121 then '91-120' else '121+' end as buckets
from (select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id, staffname, first_name, last_name, status_pat from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01) at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
INNER JOIN STAFF1 on staff1.staffid = register.addr_id
inner join contact on contact.id = register.guarantor_id
order by patno ) t1) b where buckets='61-90' group by chart) a3 on a3.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b4, chart from (select distinct trnsxno, guarantor_id, first_name as guarantor_first, last_name as guarantor_last, patno as chart, fname as patient_First, lname as patient_last, staffname as pcp, copaybalance, status_pat, effective_dor,
timestampdiff(day,cast(effective_dor as date),curdate()) as elapsed, case when timestampdiff(day,cast(effective_dor as date),curdate()) <31 then '0-30'
when timestampdiff(day,cast(effective_dor as date),curdate()) <61 then '31-60' when timestampdiff(day,cast(effective_dor as date),curdate()) < 91 then '61-90'
when timestampdiff(day,cast(effective_dor as date),curdate()) < 121 then '91-120' else '121+' end as buckets
from (select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id, staffname, first_name, last_name, status_pat from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01) at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
INNER JOIN STAFF1 on staff1.staffid = register.addr_id
inner join contact on contact.id = register.guarantor_id
order by patno ) t1) b where buckets='91-120' group by chart) a4 on a4.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b5, chart from (select distinct trnsxno, guarantor_id, first_name as guarantor_first, last_name as guarantor_last, patno as chart, fname as patient_First, lname as patient_last, staffname as pcp, copaybalance, status_pat, effective_dor,
timestampdiff(day,cast(effective_dor as date),curdate()) as elapsed, case when timestampdiff(day,cast(effective_dor as date),curdate()) <31 then '0-30'
when timestampdiff(day,cast(effective_dor as date),curdate()) <61 then '31-60' when timestampdiff(day,cast(effective_dor as date),curdate()) < 91 then '61-90'
when timestampdiff(day,cast(effective_dor as date),curdate()) < 121 then '91-120' else '121+' end as buckets
from (select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id, staffname, first_name, last_name, status_pat from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01) at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
INNER JOIN STAFF1 on staff1.staffid = register.addr_id
inner join contact on contact.id = register.guarantor_id
order by patno ) t1)b where buckets='121+' group by chart) a5 on a5.chart = b.chart
left outer join (select patno, min(appt_Date) as next_appt from schedule where visit_status is null and appt_date >= curdate() group by patno) s on s.patno = b.chart
