SQL: Simple Charges and Payments - By Billing Provider

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 billing provider.  

A sample image of this SQL report run in the Database Viewer is shown below:

Caveats

  1. 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).
  2. 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.
  3. 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.  
  4. 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.”
  5. 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) from
(
select ins_carrier_code, p_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.p_addr_id, at1.charge, at1.units, at1.trnsxno, at2.paid from
(select date1, cptcode, ins_carrier_code, p_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,p_addr_id
order by ins_carrier_code, cptcode,p_addr_id
) a 
left outer join staff1 on staff1.staffid = a.p_addr_id