We have observed that certain patients exhibit results in the chartevents table with itemid = 226732, but these results are absent in the oxygen_delivery table. One example is stay_id = 30813912.
Upon analyzing line 86 of the oxygen_delivery table, we discovered that placing the condition "where ce.rn = 1" here only includes patients present in the ce_stg2 table. Consequently, data for patients existing in the o2 table but not in the ce_stg2 table is missing.
Our solution is to remove this line of code and create a new table, ce_stg3. Do you have any suggestions for a better solution?
Thank you!
Daonan Chen & Zhou Zhou
, ce_stg2 AS ( select ce.subject_id , ce.stay_id , ce.charttime , itemid , value , valuenum , valueuom -- retain only 1 row per charttime -- prioritizing the last documented value -- primarily used to subselect o2 flows , ROW_NUMBER() OVER (PARTITION BY subject_id, charttime, itemid ORDER BY storetime DESC) as rn FROM ce_stg1 ce ) , ce_stg3 AS ( SELECT * FROM ce_stg2 WHERE rn = 1 )
Prerequisites
Description
Hello,
We have observed that certain patients exhibit results in the chartevents table with itemid = 226732, but these results are absent in the oxygen_delivery table. One example is stay_id = 30813912.
Upon analyzing line 86 of the oxygen_delivery table, we discovered that placing the condition "where ce.rn = 1" here only includes patients present in the ce_stg2 table. Consequently, data for patients existing in the o2 table but not in the ce_stg2 table is missing.
Our solution is to remove this line of code and create a new table, ce_stg3. Do you have any suggestions for a better solution?
Thank you!
Daonan Chen & Zhou Zhou
, ce_stg2 AS ( select ce.subject_id , ce.stay_id , ce.charttime , itemid , value , valuenum , valueuom -- retain only 1 row per charttime -- prioritizing the last documented value -- primarily used to subselect o2 flows , ROW_NUMBER() OVER (PARTITION BY subject_id, charttime, itemid ORDER BY storetime DESC) as rn FROM ce_stg1 ce ) , ce_stg3 AS ( SELECT * FROM ce_stg2 WHERE rn = 1 )