About

This SQL analyses patient records of all ACTIVE patients < 8 months old and lists their Beyfortus (“Babymab”) status as “due” or “ok.” It also lists their current VFC status, most recent weight (in kg), and whether the baby qualifies for 50 mg or 100 mg based on the most recent weight.

The AAP’s dosing algorithm was used as a reference.

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

Caveats

• The “weight_kg” and “most recent weight” date fields are blank if the baby has never had a weight in your office (e.g. they've registered for your practice but have not yet been seen, or you only saw them in the hospital nursery).
• Because the DOSE field computes 50 mg or 100 mg based on the most weight, babies without a weight will show their DOSE as “unknown".
• Babies show as “ok” if a maternal dose of RSV vaccine (“MOMMYVAX”) is listed in their chart, OR if the child’s Immunizations show that Beyfortus has already been entered in their chart. Either an “Actually given” dose in your office or a “historical” dose (e.g. at birthing hospital) “counts” as "ok".
• Per AAP guidelines, babies whose mothers received a maternal dose of vaccine but the vaccine was not given more than 2 weeks prior to the baby’s birthdate show as "due".
• This algorithm does NOT include high risk second season babies.
• OP must be at least version 21.3.12 to run this SQL.

SQL Code: Firebird

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

Copy

select register.patno, birthdat as dob, floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast('today' as date)))) as age_in_months, weightkg, most_recent_weight, sickvno, case when weightkg < 5 then '50 mg' when weightkg >=5 then '100 mg' else 'unknown' end as dose, case when sickvno <=4 then 'VFC' when sickvno = 5 then 'commercial' when sickvno = 6 then 'CHIP' else 'other status' end as vfc_status, medhist.rsv_vac_hist_date as mommyvax_date, babymab_given,
case when babymab_Given is not null then 'ok' when medhist.rsv_vac_hist_date is not null and birthdat - medhist.rsv_vac_hist_date <=14 then 'ok' else 'due' end as recommendation, next_appt
 from register
left outer join (select patno, max(date1) as most_recent_weight from physical_chart where weightkg >0 group by 1) p on p.patno =register.patno
inner join medhist on medhist.patno = register.patno
left outer join (select patno, max(vacdate) as babymab_given from vaccine1 where cvxcode in ('304','315','306','307') group by 1) v on v.patno =register.patno
left outer join (select patno, date1, weightkg  from physical_chart where weightkg >0) p2 on p2.patno =register.patno and p2.date1= p.most_recent_weight
left outer join (select patno, min(appt_date) as next_appt from schedule where appt_date >= cast('today' as date) and (visit_status is null or visit_status = '') group by 1) s on s.patno =register.patno
where status_pat ='ACTIVE' and dateadd(month,8,birthdat)>cast('today' as date)

SQL Code: MySQL

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

Copy

select register.patno, birthdat, timestampdiff(month,birthdat,curdate()) as age_in_months, weightkg, most_recent_weight, sickvno, case when weightkg < 5 then '50 mg' when weightkg >=5 then '100 mg' else 'unknown' end as dose, case when sickvno <=4 then 'VFC' when sickvno = 5 then 'commercial' when sickvno = 6 then 'CHIP' else 'other status' end as vfc_status, medhist.rsv_vac_hist_date as mommyvax_date, babymab_given,
case when babymab_Given is not null then 'ok' when medhist.rsv_vac_hist_date is not null and timestampdiff(day,medhist.rsv_vac_hist_date, birthdat ) <=14 then 'ok' else 'due' end as recommendation, next_appt
 from register
left outer join (select patno, max(date1) as most_recent_weight from physical_chart where weightkg >0 group by 1) p on p.patno =register.patno
inner join medhist on medhist.patno = register.patno
left outer join (select patno, max(vacdate) as babymab_given from vaccine1 where cvxcode in ('304','315','306','307') group by 1) v on v.patno =register.patno
left outer join (select patno, date1, weightkg  from physical_chart where weightkg >0) p2 on p2.patno =register.patno and p2.date1= p.most_recent_weight
left outer join (select patno, min(appt_date) as next_appt from schedule where appt_date >= curdate()  and (visit_status is null or visit_status = '') group by 1) s on s.patno =register.patno
where status_pat ='ACTIVE' and date_add(birthdat, interval 8 month)>curdate()