SQL: Encounters and Payments for Period Grouped by Rendering Provider

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

This report shows encounters by rendering provider along with the total payments received by rendering provider for a period between two dates that you specify.

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

Caveats

  • An encounter is defined as a unique patient-date of service combination billed in OP.  That is, if you billed anything for a patient (one line item or fifteen line items) on a particular date, that is an encounter for purposes of this report.  It does not necessarily have to have been a face-to-face visit with a provider.
  • Payments for the period are defined as payments that came in to the practice during the period irrespective of when the work was done.  For example, a report run for May 2018 will show money that was paid to the practice in May 2018 , which will mostly be for work done in (dates of service) March and April 2018.
  • The precise payment “date” for this purpose is (for insurance side) the insurance payment date (not necessarily the same as the posted date or the daysheeted date) and (for patient side) the date the patient payments were posted against a patient charge (not necessarily the date the copay was logged as a credit.

SQL Code: Firebird

To highlight and copy the code below to your clipboard, simply click the Copy button.

with prov_enc as (
select rend_addr_id, count(patno) as provider_encounters from (
select distinct rend_addr_id, patno, date1 from (
select patno, rend_addr_id, date1, cptcode from archive_transactions where date1 between :start_date and :end_date AND archive_flag = 1 and cptcode not in ('1','2','3','4') and cptcode not in ('113')) 
) group by rend_addr_id),

Prov_payments as (

select rend_addr_id, sum(payment) as p, sum(copayrecd) as c from archive_transactions where archive_flag = 1 and txnopaid >0 and date1 between :Start_date and :end_date group by rend_addr_id) 


select staffname, prov_enc.provider_encounters, p as ins_payments, c as patient_payments from prov_enc
left outer join prov_payments on prov_payments.rend_addr_id = prov_enc.rend_addr_id
left outer join staff1 on staff1.staffid = prov_enc.rend_addr_id


SQL Code: MySQL

To highlight and copy the code below to your clipboard, simply click the Copy button.

select staffname, prov_enc.provider_encounters, p as ins_payments, c as patient_payments from (select rend_addr_id, count(patno) as provider_encounters from (
select distinct rend_addr_id, patno, date1 from (
select patno, rend_addr_id, date1, cptcode from archive_transactions where date1 between @start_date and @end_date AND archive_flag = 1 and cptcode not in ('1','2','3','4') and cptcode not in ('113')) t0
) t1 group by rend_addr_id) prov_enc
left outer join (select rend_addr_id, sum(payment) as p, sum(copayrecd) as c from archive_transactions where archive_flag = 1 and txnopaid >0 and date1 between @start_date and @end_date group by rend_addr_id)
 prov_payments on prov_payments.rend_addr_id = prov_enc.rend_addr_id
left outer join staff1 on staff1.staffid = prov_enc.rend_addr_id