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.
A sample image of this SQL report run in the Database Viewer is shown below:
- 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.
To highlight and copy the code below to your clipboard, simply click the Copy button.
select ins_carrier_code, 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.charge, at1.units, at1.trnsxno, at2.paid from (select date1, cptcode, ins_carrier_code, charge, trnsxno, units from archive_transactions where archive_flag = 1 and date1 between :Start_date and :enddate 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)) group by txnopaid) at2 on at2.txnopaid = at1.trnsxno ) group by cptcode, ins_carrier_code order by ins_carrier_code, cptcode