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: Provider Productivity - Sick and Well

About

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:

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.

Code

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

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