Select IDs in which two conditions are met

Question

I have been trying to query from an events table (bellow I show an example with expected output).

This table has more than 3 eventtypes, but I only show the relevant ones. Those relevant 3 types are: 'HRa1' (code 211), 'HRa2' (code 12712) and 'HRy' (code 220048). 'HRa' is divided in 'HRa1' and 'HRa2' because they varied from people inserting them on the table, but they don't happen both at the same time.

I want to select the following:

• If only one of those 3 types appear: if it's 'HRa1' or 'HRa2', select those which ALL its 'HRa1'/'HRa2' values (column valuenum) are between 50 and 110; if it's 'HRy', select those with ALL its values (column value) equal to 'sinus'. See ID = 1, 2, 3, 4 and 5. Other types of events can be present, it doesn't matter, but only one of the mentioned 3 appear (see ID = 3).

• If both 'HRa1'/'HRa2' and 'HRy' appear, then 'HRa1'/'HRa2' (valuenum) must be between 50 and 110 across ALL the 'HRa1'/'HRa2' entries, and 'HRy' (value) entries must all be equal to 'sinus'. ID = 8 shows an example where this condition is NOT met.

• If none of the 3 appear, exclude, of course.

How can I achieve this?

drop table testevents cascade;
create table testevents(
hadm_id int not null,
subject_id int not null,
eventtype int not null,
eventlabel char(30) not null,
value char(360) not null,
valuenum int
);

insert into testevents(hadm_id, subject_id, eventtype, eventlabel, value, valuenum)
values
    (1, 1, 211, 'HRa1', '59', 59),
    (1, 1, 211, 'HRa1', '89', 59),
    (1, 1, 211, 'HRa1', '54', 59),
    (1, 1, 211, 'HRa1', '99', 59),
    (1, 1, 211, 'HRa1', '69', 59),
    (1, 1, 211, 'HRa1', '104', 59),

    (2, 2, 211, 'HRa1', '59', 59),
    (2, 2, 211, 'HRa1', '89', 89),
    (2, 2, 211, 'HRa1', '54', 54),
    (2, 2, 211, 'HRa1', '140', 140), -- not allowed
    (2, 2, 211, 'HRa1', '69', 69),
    (2, 2, 211, 'HRa1', '104', 104),

    (3, 3, 12712, 'HRa2', '69', 69),
    (3, 3, 12712, 'HRa2', '89', 89),
    (3, 3, 12712, 'HRa2', '94', 94),
    (3, 3, 12712, 'HRa2', '59', 59),
    (3, 3, 12712, 'HRa2', '69', 69),
    (3, 3, 12712, 'HRa2', '84', 84),
    (3, 3, 353, 'RXa', 'OK', null), 

    (4, 4, 220048, 'HRy', 'sinus', null),
    (4, 4, 220048, 'HRy', 'sinus', null),
    (4, 4, 220048, 'HRy', 'sinus', null),
    (4, 4, 220048, 'HRy', 'sinus', null),
    (4, 4, 220048, 'HRy', 'sinus', null),
    (4, 4, 220048, 'HRy', 'sinus', null),

    (5, 5, 220048, 'HRy', 'sinus', null),
    (5, 5, 220048, 'HRy', 'sinus', null),
    (5, 5, 220048, 'HRy', 'sinus', null),
    (5, 5, 220048, 'HRy', 'AF', null), -- not allowed
    (5, 5, 220048, 'HRy', 'sinus', null),
    (5, 5, 220048, 'HRy', 'sinus', null),

    (6, 6, 220048, 'HRy', 'sinus', null),
    (6, 6, 220048, 'HRy', 'sinus', null),
    (6, 6, 211, 'HRa2', '94', 95),
    (6, 6, 211, 'HRa2', '150', 150), -- not allowed
    (6, 6, 220048, 'HRy', 'sinus', null),
    (6, 6, 220048, 'HRy', 'sinus', null),

    (7, 7, 220048, 'HRy', 'sinus', null),
    (7, 7, 220048, 'HRy', 'sinus', null),
    (7, 7, 211, 'HRa2', '94', 94),
    (7, 7, 211, 'HRa2', '87', 87),
    (7, 7, 220048, 'HRy', 'sinus', null),
    (7, 7, 220048, 'HRy', 'sinus', null),
    (7, 7, 4053, 'BP', '87', 87), 
    (7, 7, 503, 'RR', '72', 72),

    (8, 8, 220048, 'HRy', 'sinus', null),
    (8, 8, 211, 'HRa2', '94', 94),
    (8, 8, 211, 'HRa2', '87', 87),
    (8, 8, 220048, 'HRy', 'AF', null), -- not allowed
    (8, 8, 220048, 'HRy', 'sinus', null),
    (8, 8, 4053, 'BP', '87', 87), 
    (8, 8, 503, 'RR', '72', 72),

    (9, 9, 220048, 'HRy', 'AF', null), -- not allowed
    (9, 9, 211, 'HRa2', '134', 134), -- not allowed
    (9, 9, 211, 'HRa2', '187', 187), -- not allowed
    (9, 9, 220048, 'HRy', 'AF', null), -- not allowed
    (9, 9, 220048, 'HRy', 'AF', null) -- not allowed
;


output:
hadm_id, subject_id
1            1
3            3
4            4
7            7

I was trying the following query but it fails for ID = 8:

select subject_id, hadm_id
from testevents
where eventlabel in ('HRa1', 'HRa2', 'HRy') 
group by subject_id, hadm_id
having ((min(valuenum) >= 50 and max(valuenum) <= 110) or
       (min(value) = 'sinus' and max(value) = 'sinus'))
order by subject_id;

Can someone help me? Many thanks!

Answer 1

Your conditions are a little complicated. I think this captures the logic:

select subject_id, hadm_id
from testevents
where eventlabel in ('HRa1', 'HRa2', 'HRy') 
group by subject_id, hadm_id
having (min(eventlabel) = max(eventlabel) and
        min(eventlabel) in ('HRa1', 'HRa2') and
        min(valuenum) >= 50 and
        max(valuenum) <= 110
       ) or
       (min(eventlabel) = max(eventlabel) and
        min(eventlabel) in ('HRy') and
        min(value) = 'sinus' and
        max(value) = 'sinus'
       ) or
       (sum( (eventlabel in ('HRa1', 'HRa2'))::int) > 0 and
        sum( (eventlabel in ('HRy'))::int) > 0 and
        min(case when eventlabel in ('HRa1', 'HRa2') then valuenum end) >= 50 and
        max(case when eventlabel in ('HRa1', 'HRa2') then valuenum end) <= 110 and
        sum( (eventlabel in ('HRy') and value <> 'sinus')::int ) = 0
       ) 

Here is a SQL Fiddle.