Important Content Update Message
We are currently updating the OP Help Center content for the release of OP 20. OP 20 (official version 20.0.x) is the certified, 2015 Edition, version of the Office Practicum software. This is displayed in your software (Help tab > About) and in the Help Center tab labeled Version 20.0. We appreciate your patience as we continue to update all of our content.

SQL: Charges and Payments with Notes


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

(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