The report identifies total payments for the sick visit code of a sick-and-well visit and compares it to the contract amount for the sick visit. This can be used as a way to identify carriers who reduce payment for a sick visit when combined with a well visit.
A sample image of this SQL report run in the Database Viewer is shown below:
- This does not check well visit payments against the contracted amounts.
- You must have contracted amounts loaded in the Insurance Contracts module of OP.
To highlight and copy the code below to your clipboard, simply click the Copy button.
select b.*, payment_amount, payment_amount-b.total_paid as difference from ( select patno, date1, w as well, ins_carrier_code, s as sick, sum(payment) + sum(copayrecd) as total_paid, trnsxno from ( select at1.patno, at1.date1, at1.cptcode as w, at1.ins_carrier_code, at2.cptcode as s, at3.payment, at3.copayrecd, at2.trnsxno from archive_Transactions at1 inner join (select patno, date1,cptcode, trnsxno from archive_transactions where date1 between :start_date and :end_date and cptcode between '99201' and '99215' and archive_flag=1) at2 on at1.date1 = at2.date1 and at1.patno = at2.patno left outer join (select * from archive_transactions where archive_flag = 1 and (copayrecd >=0.01 or payment >= 0.01) and date1>= :start_date) at3 on at3.txnopaid = at2.trnsxno where at1.cptcode between '99381' and '99395' and at1.date1 between :start_date and :end_date and at1.archive_flag = 1 ) a group by patno, date1, w, ins_carrier_code, s, trnsxno ) b inner join ins_carrier on ins_carrier.ins_carrier_code = b.ins_carrier_code inner join ins_carrier_contract on ins_carrier_contract.ins_carrier_id = ins_carrier.carrier_id left outer join ins_contract_detail on ins_carrier_contract.ins_contract_id = ins_contract_detail.ins_contract_id and b.sick = ins_contract_detail.hcpcs