finding patients with good BP control

Question

I have a results database like this

patient integer
rdate date
rvalue integer
rtype vchar

Patients have (0 .. n) BP measurements on a given date. I need to find those patients who last visit included a BP measurement, and on that visit the diastolic BP < 90 and the systolic BP < 140.

I can get the last visit like this checking only for systolic measurements

select patient, max(rdate) as maxdate 
from results 
where rtype = 'systolic' and rvalue > 0 
group by patient

How do I select out the min(rvalue) < 140 ?

Answer 1

Try:

select r.patient, max(r.rdate) as maxdate 
FROM results as R
INNER JOIN results as R2 ON r2.Patient=r.patient AND r.rdate=r2.rdate
WHERE r.type='systolic' and r.rvalue BETWEEN 0 and 140
AND r2.type='diastolic' and r2.rvalue BETWEEN 0 and 90
GROUP BY r.patient

This is a self join, and will only return rows where there are both types of BP measurements for the same patient on the same date (based on join criteria).

Answer 2

I think this will give you a list of patients and the last visit date that meets the criteria

SELECT patient, MAX(rdate) AS max_rdate
FROM (
     SELECT patient, rdate, MIN(CASE WHEN RTYPE = 'systolic'  THEN rvalue END) AS min_systolic, 
        MIN(CASE WHEN RTYPE = 'diastolic' THEN rvalue END) AS min_diastolic
    FROM BPResults
    GROUP BY patient, rdate
    ) AS results
WHERE min_systolic < 140
    AND min_diastolic < 90
GROUP BY patient

Answer 3

(First approach -- see second approach for background info)

JNK's answer is close except for one thing. It doesn't answer your question.

It will tell you when the last time a patient had good bp control, even if there are bad ones afterwards. In fact, it will not even consider any bad bp at all. It excludes them.

Graham, I have two approaches to this question.

Lets clarify the question you want.

I think you want only patients which have good blood pressure control at their very last measurements in which the diastolic is less than 90 and the systolic is less than 140. First approach. Join the table to itself to get both the best diastolic and systolic measurements for the last time they were measured, and report it when it is a good bp.

select     dia.patient       as patient   
     ,     dia.rdate         as bp_date   
     , min(dia.rvalue)       as dia_bp          -- best dia bp for that date
     , min(sys.rvalue)       as sys_bp          -- best sys bp for that date
  from results  dia
     , results  sys 
 where dia.patient            = sys.patient
   and dia.rdate              = sys.rdate       -- both bp on same date
   and dia.rtype              = 'diastolic'
   and sys.rtype              = 'systolic'
 group by dia.patient
        , dia.rdate
  having min(dia.rvalue)     <   90             -- under 90 bp
     and min(sys.rvalue)     <  140             -- under 140 bp
     and dia.rdate            =                 -- and that date is
       ( select max(lst.rdate)                  -- the last date
           from results   lst
          where lst.patient   = dia.patient     -- for that patient
            and lst.rtype    in ( 'diastolic'   -- with a bp row
                                , 'systolic' ) )

This will return all diastolic and systolic bp that are good. But the patient must have both diastolic and systolic bp on the same day, otherwise this will ignore anyone who has only a single bp. However, my second approach may solve that problem.