SQL: Asthma Recall

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 SQL will show all active patients, with no future appointment scheduled, for whom any Albuterol medication (such as "Albuterol" or "Ventolin" or "Proventil" or "Proair") was prescribed in the past year. For those patients, it will also show:
  • The last date asthma was discussed as a well visit ("last_well_asthma") - Dx code #2-4 on a well visit
  • The last date asthma was discussed at a sick visit ("last visit") - Dx code #1 on a sick visit
  • Last plan - this is the plan part for the note (per the sick visit noted above)
  • Severity of asthma in the Problem List - if this is blank, then you:
    • Don't have asthma in the problem list
    • Have asthma but don't have it coded J45.x in the ICD area
    • Don't have it coded with a severity
    • Some combo of the above
  • The last date the albuterol Rx was prescribed ("last Rx")

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

Report Usage Ideas

  • Make telemedicine visits for all patients who you haven't seen in 3 months and check their inhaler technique online - 94664 - this can be done virtually and it can be done by a provider or by nonclinician support staff. NHLBI 3rd edition recommends you check technique at every visit.
  • Make telemedicine visits for patients.
    • Ask them to complete the ACT - you can bill 96160 for this and then discuss management online as part of a telemedicine visit.
  • Kids who have asthma and 1 other chronic condition - your support staff can bill 99490 if they spend 20 minutes in a calendar month working on an Asthma Action Plan, phoning in prescriptions, helping them locate a spacer, then send the written plan to the family electronically (a portal message to the family bullet pointing what was done is great for this!)
  • Providers who haven't cleaned up their charts in a while/coded their problem lists/kept up with their problem lists need to do that for more streamlined recalls.

SQL Code: Firebird

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

select r.patno, staffname, fname, lname, birthdat, last_well_asthma, last_visit, cast(mdm_plan as char(30000)) as last_plan, severity as pl_severity,  last_rx, next_appt from register r
left outer join (select patno, max(date1) as last_visit from enc_note where dxcode like 'J45%' group by patno)
e on e.patno = r.patno
left outer join (select patno, max(date1) as last_well_asthma from physical where (dxcd2 like 'J45%' or dxcd3 like 'J45%' or dxcd4 like 'J45%')  group by patno) w on w.patno = r.patno
inner join
(select patno, max(date1) as last_Rx from medications  where
( lower(prescrib) like '%albuterol%' or
lower(prescrib) like '%ventolin%' or lower(prescrib) like '%proventil%' or lower(prescrib) like
'%proair%'  )
and purpose not like '%reference%'
and date1 >= cast('today' as date) - 365  group by patno) m on m.patno = r.patno
left outer join (select patno, icd_1, severity from chartnotes where problem_list = 'Y' and icd_1 like 'J45%') p on p.patno = r.patno
left outer join (select patno, date1, mdm_plan from enc_note) e2 on e2.patno = r.patno and e2.date1 = e.last_visit
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 not in ('Cancelled','Canceled','No Show','No Show*')) group by patno) s on s.patno = r.patno
left outer join staff1 on staff1.staffid = r.addr_id
where status_pat = 'ACTIVE' and lname <> 'TESTPATIENT'
and next_appt is null


SQL Code: MySQL

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

select r.patno, staffname, fname, lname, birthdat, last_well_asthma, last_visit, mdm_plan as last_plan, severity as pl_severity,  last_rx, next_appt from register r
left outer join (select patno, max(date1) as last_visit from enc_note where dxcode like 'J45%' group by patno)
 e on e.patno = r.patno
left outer join (select patno, max(date1) as last_well_asthma from physical where (dxcd2 like 'J45%' or dxcd3 like 'J45%' or dxcd4 like 'J45%')  group by patno) w on w.patno = r.patno

inner join

 (select patno, max(date1) as last_Rx from medications  where
 ( lower(prescrib) like '%albuterol%' or
 lower(prescrib) like '%ventolin%' or lower(prescrib) like '%proventil%' or lower(prescrib) like
 '%proair%'  ) 
and purpose not like '%reference%'
and  datediff(curdate(),date1) <= 365  group by patno) m on m.patno = r.patno
left outer join (select patno, icd_1, severity from chartnotes where problem_list = 'Y' and icd_1 like 'J45%') p on p.patno = r.patno
left outer join (select patno, date1, mdm_plan from enc_note) e2 on e2.patno = r.patno and e2.date1 = e.last_visit
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 not in ('Cancelled','Canceled','No Show','No Show*')) group by patno) s on s.patno = r.patno
left outer join staff1 on staff1.staffid = r.addr_id
where status_pat = 'ACTIVE' and lname <> 'TESTPATIENT'
and next_appt is null