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