About
The SQL is used to see what was charged for a certain time period and the payments that came in (at ANY subsequent time) for that work. The report breaks down the data by rendering provider.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- On runtime, it prompts you for two dates. These are the beginning and ending DATES OF SERVICE (eg 1/1/2017 and 12/31/2017).
- The payment amounts are any payments actually POSTED against that carrier/CPT combination. Even if the allowable is $45, if nothing was paid, nothing appears.
- The payments may have come in at any time. If a report for 1/1/2017-12/31/2017 is run on 1/1/2018, the practice would not have gotten paid for all the November and December 2017 work yet.
- Again, the date range you give is the DOS, not date of payment. That is, this report would show what was CHARGED in 2017, and what was collected as a result of the 2017 work. THIS IS NOT THE SAME AS WHAT YOU "TOOK IN" IN 2017 -- that would be a “show me all payments posted in 2017, irrespective of date of service.”
- This particular flavor counts all the payments against whatever the PRIMARY insurer was. So if UNI( United) was primary for a 99213 and paid you $25 and BLU (Blue Cross) was secondary and paid you $42, then the whole $67 gets ascribed to UNI.
Code
To highlight and copy the code below to your clipboard, simply click the Copy button.
select ins_carrier_code, staffname, cptcode, total_charge, total_units, coalesce(total_paid,0) as total_paid from ( select ins_carrier_code, rend_addr_id, cptcode, sum(charge) as total_charge, sum(units) as total_units, sum(paid) as total_paid from ( select at1.date1, at1.cptcode, at1.ins_carrier_code,at1. rend_addr_id, at1.charge, at1.units, at1.trnsxno, at2.paid from (select date1, cptcode, ins_carrier_code, rend_addr_id, charge, trnsxno, units from archive_transactions where archive_flag = 1 and date1 between :start_date and :end_date and cptcode not in ('1','2','3','4')) at1 left outer join (select sum(p) as paid, txnopaid from (select copayrecd, payment, copayrecd+payment as p, txnopaid from archive_transactions where archive_flag = 1 and (copayrecd >= 0.01 or payment >= 0.01)) t1 group by txnopaid) at2 on at2.txnopaid = at1.trnsxno ) t2 group by cptcode, ins_carrier_code, rend_addr_id )t3 left outer join staff1 on staff1.staffid = t3.rend_addr_id