Finding patients with good blood pressure control

Question

I have a results database like this

patient integer
rdate date
rvalue integer
rtype vchar

Patients have (0 .. n) blood pressure measurements at a given date. I need to find those patients who last included the measurement of blood pressure, while visiting a diastolic blood pressure, 90 and systolic blood pressure, 140.

I can get a final visit like this only for systolic measurements

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

How to choose 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 self-join will only return rows where there are both types of blood pressure measurements for the same patient on the same date (based on the criteria for joining).

Answer 2

, ,

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

( - . )

JNK , . .

, bp, . , - bp. .

, .

, .

, , 90, - 140. . , , , , , 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' ) )

bp, . , bp , , bp. .

Answer 4

( - . ).

, BOTH . , ( ). , :

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

bp (, ). .

, , , , . , bp, ( ) .

, , ( bp ).

, , !

Answer 5

, ... Firebird .

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 (?) (?)      rvalue < 140