SQL: Simple Charges and Payments

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

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:

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.

SQL Code: Firebird

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


SQL Code: MySQL

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 :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
order by ins_carrier_code, cptcode