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.
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.
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 from ( 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