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: 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