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:
- This only pulls active patients (ACTIVE in patient status.) with an elevated BP >=90 systolic or diastolic.
- Must include a height.
- 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.
- 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.".
- 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.".
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 )