sqlsubquerygreatest-n-per-groupcognoscognos-8

Join and max date with null values


I need to return the most recent (max)date for a patient_ID where a vital value exists - it's not simply the max. encounter date for the patient_ID, it must have a corresponding vital value. I also only want encounters where vitals value <>'' and Date >= '2020-01-01' and vital_ID = 232268.

Encounters (enc)

Patient_ID Encounter_ID Date
1 11 1/4/2020
1 12 1/15/2020
1 13 3/6/2020
2 14 1/12/2020
3 15 3/16/2020
3 16 4/19/2020
4 17 6/2/2020
4 18 6/12/2020
4 19 9/1/2020

Vitals

Encounter_ID Vital_ID Value
11 232268 4.8
12 232268 4.6
14 232268 3.1
16 232268 3.2
17 232268 4.1
18 232268 4.7

Desired Outcome

Patient_ID Encounter_ID Date Value
1 12 3/6/2020 4.6
2 14 1/12/2020 3.1
3 16 4/19/2020 3.2
4 18 9/1/2020 4.7

I tried this, but it returned only the vitals_encounter_ID IF it = max(date) of the encounter for the patient_ID (so did not include patient_ID if vitals were not taken on the max(date) - for instance, it negates patient_ID 1 all together because vitals weren't taken on encounter_ID 13:

select v.encounterID, e.patientID, e.date, v.value, v.vitalID 
from vitals v 
left join enc e on 
    e.encounterID = v.encounterID and 
    v.vitalID = 232268 and 
    v.value <> '' and
    e.date = (select max(date) from enc where patientID=e.patientID)
where e.date >= '2020-01-01'

Cognos 8. I'm new, so please don't eat me alive...


Solution

  • If I follow you correctly, you need the two tables in the correlated subquery:

    select v.encounterid, e.patientid, e.date, v.value, v.vitalid 
    from enc e 
    inner join vitals v on v.encounterid = e.encounterid 
    where 
        v.vitalid = 232268 
        and v.value <>'' 
        and e.date = (
            select max(e1.date) 
            from enc e1
            inner join vitals v1 on v1.encounterid = e1.encounterid 
            where 
                e1.patientid = e.patientid 
                and v1.vitalid = v.vitalid
                and v1.value <> ''
                and e.date >= '2020-01-01'
        )
    

    I don't know if Cognos supports window functions. But if it does, the query can be much simpler phrased:

    select *
    from (
        select v.encounterid, e.patientid, e.date, v.value, v.vitalid,
            row_number() over(partition by e.patientid order by e.date)
        from enc e 
        inner join vitals v on v.encounterid = e.encounterid 
        where v.vitalid = 232268 and v.value <> ''
    ) t
    where rn = 1