About

How many of your patients with asthma in their problem list have had an asthma action plan written/updated in the last _______ months/years?

This SQL defines "patients with asthma" as:

• Active patients -- any age
• An active problem in the problem list with the ICD code as anything that starts with J45    

The date in "most_recent_AAP" will be the date the last asthma action plan was written/updated, and blank if the patient has never had one.

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

Caveats

For a patient to “have asthma,” there must be an ACTIVE problem that has an ICD-10 code with J45.xx attached in the problem list.  A patient who has a problem that only contains the word “asthma” without the ICD-10 code, or who lists only a SNOMED code, will not appear in the list.

Code

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

Copy

select register.patno, fname, lname, birthdat, chartnotes.icd_1, chartnotes.severity, aap.most_recent_aap from register

inner join chartnotes on chartnotes.patno = register.patno

left outer join
(select patno, max(action_date) as most_recent_AAP from aap_actionplans group by patno
order by patno) aap on aap.patno = register.patno

where register.patno >99 and register.status_pat = 'ACTIVE'
and chartnotes.problem_list = 'Y' and chartnotes.icd_1 like 'J45%'
order by patno