About

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. This searches all dates in your OP accounts (going back for as long as you've used OP).

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."

SQL Code: Firebird

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

Copy

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

SQL Code: MySQL

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

Copy

select myaging.*, register.copaybal from 
(
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
) t1
group by patno, fname,lname, trnsxno, date1, cptcode, copaybalance, guarantor_id
) t2 group by patno,fname,lname, guarantor_id
) 
myaging
left outer join register on register.patno = myaging.patno
where copaybal < 0.01