About

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

Caveats

• This runs off billing data. 
• The provider listed is RENDERING provider, again from your billing records, as opposed to the schedule or the chart note.  (Remember, you have to have a licensed credentialed person be the rendering provider on a nurse-only visit too.)
• "Mo" is month, like 1 = January.  It does NOT also group by year, so if you run it for 2 years, you're still only going to see twelve divisions by month because it will lump, say, January 2017 and January 2018 into "1".
• Patients is total distinct encounters, not total distinct patients.  So if Johnny Jones comes in on 1/2, 1/3, and 1/4, he'll be counted 3 times, not once.
• This only includes outpatient stuff, i.e. POS = 11, so your ER visits and your nursery work isn't here.
• Visit Type.  Everyone defines "nurse", "sick", and "well" visits differently.  Here are the definitions used:

• Well visit: the patient had a well-visit E&M (99381-5 or 99391-5) on that date of service.  He may have had other services like imms, vision, hearing, or not.
• Sick visit: the patient had at least once of a sick E&M code (99201-5, 99212-5) or a procedure CPT code.  Procedure CPT codes are 10000 through 69999 - EXCEPT lab draws 3641x).  So 99213 plus some labs, or fracture care 29195, or 99203 plus x-rays.
• Nurse visit: the patient had some charge which isn't described above: i.e. NO sick/well E&M and no procedure code.  Nurse visits would be stuff like lab draw only, vaccine only, vision only, etc.
• Sick and well: on the same date of service, the patient met the criteria for both sick and well visits.  So that means a well E&M plus at least one of ( sick E&M *or* procedure code).  So well + 99213, well + urine cath, well + nursemaid's elbow, well + wart freeze, etc.

SQL Code: Firebird

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

Copy

select count(patno) as patients, mo, staffname, visit_type
from
(

select c.patno, c.date1, extract(month from c.date1) as mo, c.staffname, case when cpt_product <100 then 'nurse' when cpt_product >=100 and cpt_product<1000 then 'sick' 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, sum(b.classif) as cpt_product
from (
select a.patno, a.date1, a.staffname,at3.classif from
(
select patno, date1,  staffname from
(
select patno, trnsxno, date1, staffName
from (

select patno, trnsxno, date1, staffName
from
(
select at1.patno,at1.trnsxno, at1.cptcode,  at1.date1, staffname from archive_transactions at1
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','1','2','3','4')
and at1.archive_flag = 1 
) 
group by trnsxno, date1, staffname, patno
) 
)
group by patno, date1, staffname
) 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
  ) at3 on at3.patno=a.patno and at3.date1=a.date1
) b
group by b.patno, b.date1, b.staffname
) c

) group by mo,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 patients, mo, staffname, visit_type
from
(

select c.patno, c.date1, extract(month from c.date1) as mo, c.staffname, case when cpt_product <100 then 'nurse' when cpt_product >=100 and cpt_product<1000 then 'sick' 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, sum(b.classif) as cpt_product
from (
select a.patno, a.date1, a.staffname,at3.classif from
(
select patno, date1,  staffname from
(
select patno, trnsxno, date1, staffName
from (

select patno, trnsxno, date1, staffName
from
(
select at1.patno,at1.trnsxno, at1.cptcode,  at1.date1, staffname from archive_transactions at1
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','1','2','3','4')
and at1.archive_flag = 1 
) t1
group by trnsxno, date1, staffname, patno
) t2
) t3
group by patno, date1, staffname
) 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
  ) at3 on at3.patno=a.patno and at3.date1=a.date1
) b
group by b.patno, b.date1, b.staffname
) c

) t4 group by mo,staffname, visit_type