This report shows all open claims (either insurance-side or patient-side) with a date of service between the dates you specify. It also assigns an aging bucket of <30, 31-60, 61-90, 91-120, and 121+.
A sample image of this SQL report run in the Database Viewer is shown below:
- Any claim with a nonzero insurance balance OR a nonzero patient balance is shown.
- ALL_AGING is the computed days of aging.
- For insurance-side balances, ALL_AGING is the difference between the date of service and the run date. This report does not re-start aging with re-assignment to secondary insurances.
- For patient_side balances, ALL_AGING is the difference between the patient responsibility date and the run date.
- Only archived transactions and non-voided transactions are shown.
To highlight and copy the code below to your clipboard, simply click the Copy button.
select trnsxno, rend_staff, patno, dos, ins_carrier_code, cptcode, charge, ins_balance, pt_balance, p1 as ins_paid, a1 as ins_adj, c1 as pt_paid, w1 as pt_adj, all_aging, case when all_aging <=30 then '0-30' when all_aging >30 and all_aging <=60 then '31-60' when all_aging >60 and all_aging <=90 then '61-90' when all_aging >90 and all_aging <=120 then '91-120' else '121+' end as aging from ( select b.*, case when ins_balance >= 0.01 then cast('today' as date)-dos when pt_balance >=0.01 then cast('today' as date)- coalesce(pt_resp_date,dos) end as all_aging from ( select a.* from ( select trnsxno,staffname as rend_staff, patno, date1 as DOS, ins_carrier_code, cptcode, charge, tx_balance as ins_balance, copaybalance as pt_balance, coalesce(p,0) as p1, coalesce(a,0) as a1, coalesce( c,0) as c1,coalesce(w,0) as w1 from (select trnsxno, patno, rend_addr_id, cptcode, date1, ins_carrier_code, charge, tx_balance, copaybalance from archive_transactions where date1 between :startdate and :enddate and cptcode not in ('1','2','3','4') and archive_flag = 1) at1 left outer join (select txnopaid, sum(payment) as p, sum(adjustment) as a, sum(copayrecd) as c, sum(copayadjustment) as w from archive_transactions where archive_flag = 1 group by txnopaid) at2 on at2.txnopaid = at1.trnsxno left outer join staff1 on staff1.staffid = at1.rend_addr_id ) a where abs(charge - p1-a1-c1-w1 )>=0.01 and (ins_balance >= 0.01 or pt_balance >=0.01) ) b left outer join (select txnopaid, max(date1) as pt_resp_date from archive_transactions where cptcode ='4' and archive_flag = 1 group by txnopaid) at3 on b.trnsxno = at3.txnopaid ) c