We are currently updating the OP Help Center content for the release of OP 14.19 or OP 19. OP 19 is a member of the certified OP 14 family of products (official version is 14.19.1), which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

SQL: Asthma Action Plan- Last Written or Updated


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:


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.


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

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