About

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

Caveats

• All visit types are defined according to what was billed in OP on the date of service. 
• Only outpatient billing (POS 11) is included.
• A visit is classified as one of the four following types (depending on what was billed):

1. Nurse visit: No E&M codes (except 99211) or procedure codes (10000-69999) were billed.  The only codes on the date of service were some combination of:

• 99211
• 36415, 36416, 36410
• Lab codes (CPT 80000-89999)
• Radiology codes (CPT 70000-79999)
• Another code in the CPT 9xxxx group besides the E&M codes.  This would include visits which only contain vaccines, surveys, injections, etc.
• Supply or medication codes
• Any other unrecognized/proprietary code (let’s say your practice made a CPT code called ‘A0000’ to charge for books or late fees)

1. Sick/procedure visit:  A non-well provider sick E&M code was billed (99212-99215, 99201-99205) and/or a procedure code (10000-69999, except 3641x).  

• Other things may or may not also have been billed, like surveys, vaccines, neb treatments, etc.

1. Well visit: A well visit E&M (9939x, 9938x) was billed.

• Other things may or may not also have been billed, like surveys, vaccines, neb treatments, etc.

1. Well and sick/procedure visit:  the visit has a well E&M and at least one of a sick E&M and a procedure code  Examples include:

• 99392 and 99213 together
• 99392 and 69210 (cerumen removal) together
• 99392 and 51701 (urine cath) together
• 99393 and 17110 (wart removal) together

• Sick-well ratio could be computed from this (depending on how the practice wants to handle sick-and-well-together in the computation).

SQL Code: Firebird

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

Copy

select count(patno) as number_of_visits, visit_type, staffname from
(

select c.patno, c.date1, c.staffname, c.money,c.ins_carrier_code, case when cpt_product <100 then 'nurse' when cpt_product >=100 and cpt_product<1000 then 'sick/proc' when cpt_product >=1000 and cpt_product <1100 then 'well' when cpt_product>=1100 then 'well and sick/proc' end as visit_type
from (
select b.patno, b.date1, b.staffname, b.ins_carrier_code, b.money, sum(b.classif) as cpt_product
from (
select a.patno, a.date1, a.staffname, a.ins_carrier_code, a.money, at3.classif from
(
select patno, date1, ins_carrier_code, staffname, sum(total_paid) as money from
(
select patno, trnsxno, date1, ins_carrier_code, staffName, p, c, p+c as total_paid
from (

select patno, trnsxno, date1, staffName, ins_carrier_code, sum(payment) as p, sum(Copayrecd) as c
from
(
select at1.patno,at1.trnsxno, at1.cptcode, at1.ins_carrier_code, at1.date1, staffname, at2.payment, at2.copayrecd from archive_transactions at1 
inner join archive_transactions at2 on at2.txnopaid = at1.trnsxno
inner join staff1 on staff1.staffid = at1.rend_addr_id
where at1.date1 >= :start_date and at1.date1 <= :end_date
and at1.pos = 11
and at1.cptcode not in ('113','99999')
and at1.archive_flag = 1 and at2.archive_flag= 1
) 
group by trnsxno, date1, staffname, patno, ins_carrier_code
) 
)
group by patno, date1, staffname, ins_carrier_code
) a 
left outer join
(select patno, date1, cptcode, case when cptcode in ('99212','99213','99214','99215','99201','99202','99203','99204','99205') then 100
when cptcode between '10000' and '69999' and cptcode not in ('36415','36416','36410') then 100 when cptcode in ('99381','99382','99383','99384','99385','99391','99392','99393','99394','99395') then 1000 else 1 end as classif from archive_transactions 
   where cptcode not in ('1','2','3','4','113','99999') and archive_flag = 1 and archive_transactions.date1 >= :start_date and archive_transactions.date1 <= :end_date 
  ) at3 on at3.patno=a.patno and at3.date1=a.date1
) b
group by b.patno, b.date1, b.staffname, b.money, b.ins_carrier_code
) c
) group by staffname, visit_type

SQL Code: MySQL

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

Copy

select count(patno) as number_of_visits, visit_type, staffname from
(

select c.patno, c.date1, c.staffname, c.money,c.ins_carrier_code, case when cpt_product <100 then 'nurse' when cpt_product >=100 and cpt_product<1000 then 'sick/proc' 
when cpt_product >=1000 and cpt_product <1100 then 'well' when cpt_product>=1100 then 'well and sick/proc' end as visit_type
from (
select b.patno, b.date1, b.staffname, b.ins_carrier_code, b.money, sum(b.classif) as cpt_product
from (
select a.patno, a.date1, a.staffname, a.ins_carrier_code, a.money, at3.classif from
(
select patno, date1, ins_carrier_code, staffname, sum(total_paid) as money from
(
select patno, trnsxno, date1, ins_carrier_code, staffName, p, c, p+c as total_paid
from (

select patno, trnsxno, date1, staffName, ins_carrier_code, sum(payment) as p, sum(Copayrecd) as c
from
(
select at1.patno,at1.trnsxno, at1.cptcode, at1.ins_carrier_code, at1.date1, staffname, at2.payment, at2.copayrecd from archive_transactions at1 
inner join archive_transactions at2 on at2.txnopaid = at1.trnsxno
inner join staff1 on staff1.staffid = at1.rend_addr_id
where at1.date1 >= :start_date and at1.date1 <= :end_date
and at1.pos = 11
and at1.cptcode not in ('113','99999')
and at1.archive_flag = 1 and at2.archive_flag= 1
) t1
group by trnsxno, date1, staffname, patno, ins_carrier_code
) t2
) t3
group by patno, date1, staffname, ins_carrier_code
) a 
left outer join
(select patno, date1, cptcode, case when cptcode in ('99212','99213','99214','99215','99201','99202','99203','99204','99205') then 100
when cptcode between '10000' and '69999' and cptcode not in ('36415','36416','36410') then 100 when cptcode in ('99381','99382','99383','99384','99385','99391','99392','99393','99394','99395') 
then 1000 else 1 end as classif from archive_transactions 
   where cptcode not in ('1','2','3','4','113','99999') and archive_flag = 1 and archive_transactions.date1 >= :start_date and archive_transactions.date1 <= :end_date 
  ) at3 on at3.patno=a.patno and at3.date1=a.date1
) b
group by b.patno, b.date1, b.staffname, b.money, b.ins_carrier_code
) c
) t4 group by staffname, visit_type