We are currently updating the OP Help Center content for the release of OP 19, which is a member of the certified OP 14 family of products. OP 19’s official version is 14.19.1, which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

SQL: Compare Cost to Payment by CPT

About

This report shows the practice-entered cost of a CPT code (e.g labs or vaccines) and total payment for that CPT code for a date range you specify.

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

Caveats

  • The date range is the date of service range for that CPT code.
  • The Cost field comes from the CPT_CODES table field called COST.  If you have not populated that field with your cost information, nothing will show in that column in the report.
  • The Total Paid amount includes any payment (insurance- and/or patient-side).

Code

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

select cost, cptcode, ins_carrier_code, invoiceno as claim_number, patno, date1, round( total_paid,2) as total_amount_paid from cpt_codes

inner join

( select * from 

(select charge, invoiceno, ins_carrier_code,  patno, date1, trnsxno, cptcode from archive_transactions at1 where txnopaid =0 and date1 between :start_date and :end_date) a 

left outer join (select sum(payment) + sum(copayrecd) as total_paid, txnopaid from archive_transactions where txnopaid >0 and date1 >= :start_date group by txnopaid) b 

on a.trnsxno = b.txnopaid) c on c.cptcode = cpt_codes.cptcode