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: Find Individual Line Items Out of Balance


This SQL finds individual line items that are over/underpaid when the total claim is in balance. A practice whose accounts are correct (at least in this way) will get no results when this report is run.

A sample image of this SQL report run in the Database Viewer is shown below:

Below are two examples that show Individual Line Items Out of Balance.

Example 1

This example shows a patient balance of 3.11, but there is also an insurance credit of 3.11. Since both items are on the same claim (in fact, the same line item), it indicates that nothing is owed. This patient has no balance according to the front of the chart. However, buried deep in the line items, there is a patient balance. If you correct the claim (essentially removing the 2nd "4" line), everything will be corrected again.

Example 2

This example shows an item where the patient owes $10 on the 99213 line item, but has a credit of $10 on the 90772 line item.  Overall, the patient doesn't owe anything, but if you run a report for "What was I paid for 99213" you will receive bad results. Back out the patient payment on 90772, reapply it to 99213, and you're good.

The field "invoiceno" is the claim number. You can jump right to it if you enter $[claimnumber] like "$145384" in the "find a patient field."


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

with myAging as 
select patno,fname,lname, sum(copaybalance) as pat_owed, min(e_dor) as eff_pat_dor, guarantor_id
select patno, fname,lname, trnsxno, date1, cptcode, copaybalance, max(effective_dor) as e_dor, guarantor_id from
select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id from
 (select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01)  at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
order by patno
group by patno, fname,lname, trnsxno, date1, cptcode, copaybalance, guarantor_id
) group by patno,fname,lname, guarantor_id
select myaging.*, register.copaybal from myaging
left outer join register on register.patno = myaging.patno
where copaybal < 0.01