About

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 perfectly as you expect. But if you go back to last year, the dates might seem slightly "shifted."

SQL Code: Firebird

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

Copy

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
)

SQL Code: MySQL

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

Copy

select distinct * from (
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 
(
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
)  b
inner join 
(select total_visits, week_of_year from 
(
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
) 
b where visit_type = 'flu') c on b.week_of_year = c.week_of_year
inner join
(select total_visits, week_of_year from 

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

b where visit_type = 'not flu') d on b.week_of_year = d.week_of_year
) t1