SQL: Meningitis B Doses for 16-21 Year Olds

There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator.

About

This report shows a list of active patients ages 16-21 and the total number of Meningitis B vaccines they have received.

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

Caveats

“Meningitis B” vaccines are any vaccines listed in the patient’s vaccine chart with CVX codes 162-164.  They could have been given by the practice or recorded as legacy vaccines from elsewhere.

SQL Code: Firebird

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

select a.*, total_menb from 
(
select patno, fname,lname, birthdat, floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat),  dateadd(day,-extract (day from birthdat)+1,cast('today' as date)) )/12) as age

from register

where status_pat ='ACTIVE'  and patno >99 
and lname <> 'TESTPATIENT'
) a

left outer join
(select patno, count(vacname) as total_menb from vaccine1 where cvxcode in (163,162,164) group by patno) v
on a.patno = v.patno
where age between 16 and 21


SQL Code: MySQL

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

select a.*, total_menb from 
(
select patno, fname,lname, birthdat, floor(timestampdiff(year,birthdat,curdate())) as age
from register
where status_pat ='ACTIVE'  and patno >99 
and lname <> 'TESTPATIENT'
) a

left outer join
(select patno, count(vacname) as total_menb from vaccine1 where cvxcode in (163,162,164) group by patno) v
on a.patno = v.patno
where age between 16 and 21