finding patients with good BP control

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开发者_如何学Go 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 ?

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).

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

(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.

(Second approach -- see my first approach for background info).

My first approach will ONLY return rows in which BOTH diastolic and systolic happen in the same day. It will ignore any rows which have just one or the other (not both). If you want to include these rows into the query, they you can do this second approach:

select     bp.patient   
     ,     bp.rdate                              -- the latest bp date
     ,     bp.rtype   
     , min(bp.rvalue)                            -- best bp for that date
  from results    bp 
 where bp.rtype   in ('diastolic', 'systolic')
   and bp.rdate    =                             -- and the bp date is
     ( select max(lst.rdate)                     -- the last bp date 
         from results  lst                   
        where lst.patient  = bp.patient          -- for that patient
          and lst.rtype   in ( 'diastolic'       -- in which there is a bp row
                             , 'systolic' ) )
 group by bp.patient
        , bp.rdate
        , bp.rtype
   having (    min(bp.rvalue)    <   90          -- under 90 bp
           and     bp.rtype       = 'diastolic') -- when diastolic
       or (    min(bp.rvalue)    <  140          -- or under 140 bp
           and     bp.rtype       = 'systolic' ) -- when systolic

This will give you the last bp measurements (diastolic, systolic or both). You will then have one or two rows per patient.

The main advantage of this approach is it will include any patients which have either diastolic or systolic or both in your result set. In situations where there are multiple bp measurements on the same day, this query will only return the best (lowest) measurement.

Personally, I like this solution the best because it gives you the greatest flexibility regarding the many different combinations that you could be faced with (in terms of a single bp measurement on a different day).

Hope you find one that works out for you!

This is pretty much what I want ... but Firebird doesn't support intersect.

select patient, max(rdate) as ldate from digital 
where rtype = 'Diastolic' and  RDATE between (?) and (?) 
group by patient

intersect

select patient, rdate as ldate from digital 
where rtype = 'Diastolic' and RDATE between (?) and (?) 
and rvalue < 90

intersect 

select patient, rdate as ldate as digital where rtype = 'Systolic'  and

RDATE between (?) and (?) and rvalue < 140