| There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator. |
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.
SQL Code: Firebird
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)
)Drill down
To highlight and copy the code below to your clipboard, simply click the Copy button.
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)
order by ins_carrier_code, cptcodeSQL Code: MySQL
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.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)
) t1SQL Code: MySQL (Drill down)
To highlight and copy the code below to your clipboard, simply click the Copy button.
select cptcode,units, ins_carrier.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)
order by ins_carrier_code, cptcode
