About

This SQL provides you with aging of all balances, grouped by payer and aging bucket (e.g. 0-30, 31-60, etc). Insurance balances and patient balances are shown separately.

Caveats

• Aging dates from the date of service for insurance balances.
• Aging dates from the patient responsibility date for patient balances, if the patient responsibility date exists.  If not, it dates from the date of service.

SQL Code

To highlight and copy the code below to your clipboard, simply click the Copy button.

Copy

with ar as (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 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 where patno >99)

select ins_carrier_code,
round(sum(ins_balance),2) as total_ins_bal, round(sum(pt_balance),2) as
total_pt_bal, aging from (select * from ar) group by ins_carrier_code,
aging order by ins_carrier_code, aging