About

This SQL provides you an average revenue-per-visit for each payer during a period you specify.

Caveats

• The date range specifies the dates of service (the money you received may or may not have come in during that date range). This means that if you run it for this month’s date range, your numbers will be artificially low because it includes many visits that you haven’t been paid for yet.
• When a sick and well are done on the same day for the same patient, that is counted here as two visits, not one.
• Only provider-level visits are counted here.  Specifically, any visit that does not include a well visit or a non-99211 sick visit is NOT included here.   All revenue from those provider-level visits (including vaccines) is reported here.

SQL Code (both Firebird and MySQL)

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

Copy

select b.ins_fullname, round(sum(b.total_visit_pd),2) as rev, sum(b.total_em_codes) as visits,  round(sum(b.total_visit_pd)/ sum(b.total_em_codes),2) as rev_per_visit  from (
select a.patno, a.dos, a.rendering, a.ins_fullname, sum(total_em) as total_em_codes, sum(visit_total) as total_visit_pd from (
select at1.patno,at1.date1 as dos ,staff1.staffname as rendering, sum(cpttype) as total_em, ins_carrier.ins_fullname,  coalesce(sum(at2.paid),0) as visit_total  from
(select date1, patno, cptcode, case when cptcode between '99381' and '99395' then 1 when cptcode between '99212' and '99215' then 1 when cptcode between '99201' and '99205' then 1 else 0 end as cpttype, ins_carrier_code, trnsxno, rend_addr_id from archive_transactions where archive_flag <= 1 and txnopaid = 0 and date1 between :Start_date and :enddate and cptcode not in ('1','2','3','4','PLA')) at1
inner join staff1 on staff1.staffid = at1.rend_addr_id
inner join ins_carrier on ins_carrier.ins_carrier_code = at1.ins_carrier_code
left outer join
(select round(sum(payment),2) +  round(sum(copayrecd),2) as paid, txnopaid from
(select copayrecd, payment, adjustment, copayadjustment, txnopaid from archive_transactions where archive_flag =1 )
group by txnopaid) at2
on at2.txnopaid = at1.trnsxno
group by 1,2,3,5
) a where total_em >0 group by 1,2,3,4
) b  group by ins_fullname