We are currently updating the OP Help Center content for the release of OP 19, which is a member of the certified OP 14 family of products. OP 19’s 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: Elevated Blood Pressure

About

This SQL will show all active patients of your practice who have had at least one elevated BP in the past year (or time period you specify), as well as how many normal BPs you've measured on them in that same time period. If you have one elevated BP and 4 normals, you're probably OK. If you have 4 elevated ones and no normals, and no future follow up scheduled, you might want to do something about that.

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

Caveats

  1. This only pulls active patients (ACTIVE in patient status.) with an elevated BP >=90 systolic or diastolic.  
  2. Must include a height.  
  3. The "date to start" parameter you enter is the beginning of the period that looks for any high BP, as well as any other normal BP done during the period.  The end of the period is today's date.   
  4. Will look at BP's done during the period and return number of normal and number of high.  When the algorithm computes "number of normals" and "number of highs," it counts distinct date-time pairs, not distinct dates.  So if you did 4 BP readings on a single day, you're going to have a total of 4, between "high" and "normal.". 
  5. The "next appt" is the next non-cancelled appointment for that patient on the schedule.  It's ANY future appointment - checkup, complex, nurse visit, etc.  Blank in that column is "no scheduled future appointment.".

Code

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

select distinct * from
(

with BP_values as 
(
select distinct a.*, s.next_appt from
(
select highsign.patno, register.fname, register.lname, staffinit as pcp, vsd.date_examined, vsd.vital_sign_name, vsd.vital_sign_val, vsd.vital_sign_flag, vsd.vital_sign_comment from
(select  patno, vital_sign_group.date_examined as high_date, vital_sign_group.vital_sign_group_id as high_one
from vital_sign_group
left outer join vital_sign_data on vital_sign_group.vital_sign_group_id = vital_sign_data.vital_sign_group_id
where date_examined >= :date_to_start
and vital_sign_data.vital_sign_id in (100,101)
and vital_sign_flag in ('>95%','>90%','>99%') ) highsign

inner join register on register.patno = highsign.patno
inner join staff1 on staff1.staffid = register.addr_id

left outer join 
(select date_examined, patno, vital_sign_group_id, vital_sign_name, vital_sign_val, vital_sign_flag, vital_sign_comment from vital_sign_group 
inner join vital_sign_data on vital_sign_group.vital_sign_group_id = vital_sign_data.vital_sign_group_id
inner join vital_sign on vital_sign.vital_sign_id =  vital_sign_data.VITAL_SIGN_ID
where date_examined >= :date_to_start  and vital_sign_parent in (101,100)) vsd on vsd.patno = highsign.patno

where register.status_pat = 'ACTIVE' 

) a
left outer join (select patno,min(appt_date) as next_appt from schedule where schedule.appt_date > cast('today' as date) and schedule.visit_status is null group by patno) s on s.patno = a.patno
order by patno, a.date_examined desc, a.vital_sign_val desc
)

select a.patno, bp_values.fname, bp_values.lname, bp_values.pcp,  a.number_of_high as num_hi, a.most_recent_high, b.number_of_normal as num_normal, b.most_recent_normal,bp_values.next_appt from


(
select patno, count(net) as number_of_high, max(date_examined) as most_recent_high
from(

select patno, min(determination) as net, date_examined from
(
select patno, date_examined, case when vital_sign_flag like '>%' then 'hi' else 'normal' end as determination from bp_values
) group by patno, date_examined
)
where net='hi'
group by patno, net
) a
left outer join
(select patno, count(net) as number_of_normal, max(date_examined) as most_recent_normal
from(

select patno, min(determination) as net, date_examined from
(
select patno, date_examined, case when vital_sign_flag like '>%' then 'hi' else 'normal' end as determination from bp_values
) group by patno, date_examined
)
where net='normal'
group by patno, net
) b on b.patno = a.patno
left outer join BP_values on a.patno = BP_values.patno
)