We are currently updating the OP Help Center content for the release of OP 19, which is a member of the certified OP 14 family of products. OP 19’s 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: Weekly Flu Prevalence

About

This SQL shows the percentage of sick-visit patient's that came in for a flu-related visit.

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

Caveats

  • "Sick visits" are 99212-99215 and 99201-99205.
  • "Influenza-related" means the first diagnosis code on your claim was J09x, J10x, or J11x.  If you are using other codes, or are putting the diagnosis in the 2nd, 3rd, or 4th slot, those will be considered "non flu related." If you don't use OP for billing, this report won't work.  In addition, you must bill and daysheet for results to be rendered.  If you try to go back to when we were using ICD9, it will look 100% not flu.
  • The week definition uses “ISO weeks".  That is, the first "week" in the year starts with a Monday that has the majority of its days in the new year.   In 2018, since January 1 was a Monday, it aligns perfect as you expect. But if you go back to last year, the dates might seem slightly "shifted."

Code

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

select distinct * from
(

with b as
(
select count(patno) as total_visits, week_of_year, visit_type from
(
select date1, patno, extract(WEEK from date1) as week_of_year,  case when dxcode1 like 'J09%' then 'flu'  when dxcode1 like 'J10%' then 'flu'  when dxcode1 like 'J11%' then 'flu'  else 'not flu' end as visit_type
from archive_transactions 
where date1 between :start_date and :end_date
and cptcode in ('99201','99202','99203','99204','99205','99212','99213','99214','99215')
) a group by week_of_year, visit_type
order by week_of_year
) 
select b.week_of_year, c.total_visits as flu_visits, d.total_visits as not_flu_visits,(c.total_visits/(c.total_visits + d.total_visits))*100 as pct from b

inner join 
(select total_visits, week_of_year from b where visit_type = 'flu') c on b.week_of_year = c.week_of_year

inner join
(select total_visits, week_of_year from b where visit_type = 'not flu') d on b.week_of_year = d.week_of_year
)