About

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

Caveats

If your Practice receives a "CONVERSION ERROR", see below for an alternate SQL.

Code

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

Copy

select v4.patno, status_pat, v4.vaccines_Given, vacname, cvxcode, first_dose, case when second_test = '12/31/1999' then '-' else second_test end as second_dose, days_since, status from (
select v3.*, case
when cvxcode = '212' then 'finished'
when cvxcode in (208, '208') and vaccines_given = 1 and days_since <21 then 'not yet'
when cvxcode in ('207',207) and vaccines_given = 1 and days_since <28 then 'not yet'
when cvxcode in ('207','208',208) and vaccines_given = 2 then 'finished'
else 'due'
end as status
from (
select patno, vaccines_given, vacname, cvxcode, fi as first_dose, case when se <> fi then se else '12/31/1999' end as second_test, case when se = '12/31/1999' then datediff(day,fi,cast('today' as date)) else datediff(day,se,cast('today' as date)) end as days_since
from (
select patno, count(vacdate) as vaccines_given, min(vacdate) as fi, max(vacdate) as se from vaccine1 where cvxcode in ( '207',' 208', '210','212','211',208,210,207) group by patno
) v
inner join (select patno, cvxcode, vacname, vacdate from vaccine1 where cvxcode in ( '207',' 208', '210','212','211',208,210,207) ) v2 on v2.patno = v.patno and v2.vacdate = v.fi
) v3
) v4
inner join register on register.patno = v4.patno

SQL Code for Conversion Error

The Conversion Error displays due to corrupt vaccine data in the Practice database. Each vaccine entered into OP has a two or three-digit CVX code identifying the type of vaccine. Conversion errors occur when a Practice has vaccines in the Vaccine Table of the database without a CVX code. This may reflect incompletely converted legacy data from a previous EMR system or it may represent user error when entering legacy vaccines. To find all corrupt data, vaccines in OP without a CVX code, highlight and copy the code below to your clipboard, simply click the Copy button.

Copy

select * From vaccine1 where cvxcode is null or cvxcode = ''

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

Copy

select v4.patno, status_pat, v4.vaccines_Given, vacname, cvxcode, first_dose, case when second_test = '12/31/1999' then '-' else second_test end as second_dose, days_since, status from (
select v3.*, case
when cvxcode = '212' then 'finished'
when cvxcode in (208, '208') and vaccines_given = 1 and days_since <21 then 'not yet'
when cvxcode in ('207',207) and vaccines_given = 1 and days_since <28 then 'not yet'
when cvxcode in ('207','208',208) and vaccines_given = 2 then 'finished'
else 'due'
end as status
from (
select patno, vaccines_given, vacname, cvxcode, fi as first_dose, case when se <> fi then se else '12/31/1999' end as second_test, case when se = '12/31/1999' then datediff(day,fi,cast('today' as date)) else datediff(day,se,cast('today' as date)) end as days_since
from (
select patno, count(vacdate) as vaccines_given, min(vacdate) as fi, max(vacdate) as se from (select * from vaccine1 where cvxcode is not null and cvxcode <> '') vaccine1 where cvxcode in ( '207',' 208', '210','212','211',208,210,207) group by patno
) v
inner join (select patno, cvxcode, vacname, vacdate from (select * from vaccine1 where cvxcode is not null and cvxcode <> '') vaccine1 where cvxcode in ( '207',' 208', '210','212','211',208,210,207) ) v2 on v2.patno = v.patno and v2.vacdate = v.fi
) v3
) v4
inner join register on register.patno = v4.patno