| 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 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
- 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.".
SQL Code: Firebird
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
)SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select distinct * from (
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 ( 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 >= :start_date
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.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 >= :start_date 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
) bp_values
) t4 group by patno, date_examined
) t2
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 ( 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 >= :start_date
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.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 >= :start_date 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 > curdate() 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
) bp_values
) t3 group by patno, date_examined
) t1
where net='normal'
group by patno, net ) b on b.patno = a.patno
left outer join ( 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 >= :start_date
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.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 >= :start_date 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
) BP_values on a.patno = BP_values.patno
) t6
