Important Content Update Message
We are currently updating the OP Help Center content for OP 20. We appreciate your patience as we continue to make these updates. To locate the version of your software, navigate to: Help tab > About.

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