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: Expected Revenue - Charges and Contracted Rates

About

This SQL lets you pick a date range (say, this week) and takes all your CPT codes for charges rendered, matches them to your contracted allowables, and tells you the expected amount you'll receive, based on your insurance contracts.

e.g. Today my provider saw just two patients and charged each of them 99213.  Both patients have insurance company XYZ. Let’s say we charge units of 99213 at $100 each.  Also, let's say my contracted rate is $75 for 99213 for XYZ. This report would show you a total of $200 in charges with $150 of that in expected payment.

For more information on the OP Contracts feature:

Sample images of the report run in the Database Viewer are shown below:

Totals only:

Drill down:


Caveats

  • It only shows charges that have been converted to a claim and daysheeted.  (The claims don't have to be sent yet.)
  • This will only show CPT codes that meet the following conditions:
  • CPT Codes have an associated payer contract
  • The payer contract has a fee for that CPT code loaded.  

If you are NOT using the Contracts feature (or you're using it, but you only have a few CPTs loaded) the charges and payments will either not show up properly, or not show up at all.

Code (Totals Only)

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

select sum(charge) as total_charge, sum(contracted_allowable) as expected_pmt from 
(

select cptcode,units, ins_carrier_code, charge, date1 as dos, (units *  payment_amount) as contracted_allowable from archive_transactions
inner join ins_carrier on ins_carrier.ins_carrier_code = archive_transactions.ins_carrier_code
inner  join ins_carrier_contract on ins_carrier_contract.ins_carrier_id = ins_carrier.carrier_id
inner join ins_contract_detail on ( ins_contract_detail.ins_contract_id = ins_carrier_contract.ins_contract_id) and (archive_Transactions.cptcode = ins_contract_detail.hcpcs)


where date1 between :start_date and :end_date and cptcode not in ('1','2','3','4') and archive_transactions.archive_flag = 1
and date1>=ins_carrier_contract.effective_date
and (ins_carrier_contract.expiration_date is null or date1<=ins_carrier_contract.expiration_date)
)

Code (Drill down)

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

select sum(charge) as total_charge, sum(contracted_allowable) as expected_pmt from 
(

select cptcode,units, ins_carrier_code, charge, date1 as dos, (units *  payment_amount) as contracted_allowable from archive_transactions
inner join ins_carrier on ins_carrier.ins_carrier_code = archive_transactions.ins_carrier_code
inner  join ins_carrier_contract on ins_carrier_contract.ins_carrier_id = ins_carrier.carrier_id
inner join ins_contract_detail on ( ins_contract_detail.ins_contract_id = ins_carrier_contract.ins_contract_id) and (archive_Transactions.cptcode = ins_contract_detail.hcpcs)


where date1 between :start_date and :end_date and cptcode not in ('1','2','3','4') and archive_transactions.archive_flag = 1
and date1>=ins_carrier_contract.effective_date
and (ins_carrier_contract.expiration_date is null or date1<=ins_carrier_contract.expiration_date)
)