This report shows all CPTs and charges, payments and adjustments, claim notes and adjustment codes, diagnoses, and age of patient at the time of service for a date of service range that you specify.
A sample image of this SQL report run in the Database Viewer is shown below:
- The column MONTHS_OLD gives the patient’s age in months on the date of service.
- INVOICENO is the claim number associated with the charge.
- PENDING is the balance on the claim (either patient-side or insurance-side) once there has been at least one payment or adjustment on the line item. A PENDING of 0 means the claim has been completely paid/adjusted and is closed.
To highlight and copy the code below to your clipboard, simply click the Copy button.
select at1.patno, birthdat, (datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,date1))) as months_old, at1.invoiceno, at1.date1, at1.cptcode, at1.mod1, at1.mod2, at1.ins_carrier_code, at1.ins_carrier_code_other, at1.charge, at1.units, at1.trnsxno, at1.pos, at1.dxcode1, at1.dxcode2, at1.dxcode3, at1.dxcode4, at2.p as ins_paid, at2.c as pt_paid, at2.p + at2.c as total_paid,at2.ins_adj, at2.pt_adj, round(at1.charge-at2.p-at2.c-at2.ins_adj-at2.pt_adj,2) as pending, claim_note , ins_adj_codes from (select date1, patno, invoiceno, cptcode,mod1, mod2, ins_carrier_code, ins_carrier_code_other, charge, trnsxno, pos, units, dxcode1, dxcode2, dxcode3, dxcode4 from archive_transactions where archive_flag = 1 and date1 between :Start_date and :enddate and cptcode not in ('1','2','3','4','PLA')) at1 inner join register on register.patno = at1.patno left outer join (select round(sum(payment),2) as p, round(sum(copayrecd),2) as c, sum(adjustment) as ins_adj, sum(copayadjustment) as pt_adj, txnopaid, list(descript,',') as ins_adj_codes from (select copayrecd, payment, adjustment, copayadjustment, txnopaid, c.descript from archive_transactions left outer join (select code_id, descript from code_table where group_id = 1) c on c.code_id = archive_transactions.adj_code where archive_flag =1 ) group by txnopaid) at2 on at2.txnopaid = at1.trnsxno left outer join claims1 on claims1.invoiceno = at1.invoiceno order by patno, invoiceno