We are currently updating the OP Help Center content for the release of OP 14.19 or OP 19. OP 19 is a member of the certified OP 14 family of products (official version is 14.19.1), which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

SQL: Encounters and Payments for Period Grouped by Rendering Provider

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.

Code

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