Important Content Update Message
We are currently updating the OP Help Center content for the release of OP 20. OP 20 (official version 20.0.x) is the certified, 2015 Edition, version of the Office Practicum software. This is displayed in your software (Help tab > About) and in the Help Center tab labeled Version 20.0. We appreciate your patience as we continue to update all of our content.

SQL: Provider Productivity - Sick and Well


This shows, grouped by month, how many nurse, sick, well, and sick-and-well visits your providers conducted.

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


  • 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.


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

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

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
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