MIMIC-IV ED : Obtain vital signs of emergency room patients during emergency stay.

[030vegetable]

• For some emergency patients, vital signs can be recorded in the triage form.
• For patients whose vital signs were not recorded at triage, I tried to look for them in the vitalsign table under the MIMIC-IV ED module.I have encountered some problems in the process of searching for guidance, thank you.

-- (estay--vitalsign) : I applied subject_id and stay_id to connect, and found that some patients still could not retrieve relevant records.
--So we tried something else: only subject_id was used for the link, but with a time limit so that the charttime time was limited, for example, the charttime between intime and outtime of the emergency room.
Basically, everyone has corresponding records, but I found that the subject_id of each record is the same, and the time of recording is also between entering the emergency room and leaving the emergency room.
But their stay_id has many, I do not understand the same patient in such a short period of intime--outtime will have so many stay_id.
the picture is as follow: https://github.com/030vegetable/test/blob/main/vitalsign.png
on the first try:
select
t1.subject_id
,t1.stay_id
,t2.stay_id
,t1.intime
,t1.outtime
,t2.charttime
,t2.temperature
,t2.heartrate
,t2.resprate
,t2.sbp
,t2.dbp
,t2.o2sat
from table7 t1
LEFT JOIN mimiciv_ed.vitalsign t2
on t1.subject_id=t1.subject_id
AND t1.stay_id=t2.stay_id
second try:
select
t1.subject_id
,t1.stay_id
,t2.stay_id
,t1.intime
,t1.outtime
,t2.charttime
,t2.temperature
,t2.heartrate
,t2.resprate
,t2.sbp
,t2.dbp
,t2.o2sat
from table7 t1
LEFT JOIN mimiciv_ed.vitalsign t2
on t1.subject_id=t1.subject_id
and t2.charttime between t1.intime and t1.outtime

I just want to know if this data is from the same person, the same ER visit, and if I can use it for analysis.
Do I not fully understand the meaning of stay_id?
I really need help. Thank you for your reply.

[030vegetable]

Okay, now I know what's wrong with me.

[alistairewj]

What was the issue?

[030vegetable]

• Sorry, the problem is me. My script was wrong. The last condition I wrote was t1.subject_id=t1.subject_id, which is equivalent to limiting the record time only, so the results to be retrieved include the vital signs of other people during the time period of leaving and entering the emergency room. That's why there are so many stay_id, and I have checked their subject_id is also inconsistent. (The correct notation is t1.subject_id=t2.subject_id).
• However, after correction, I found that nearly half of the patients lacked vital signs records. I could not find relevant records in the triage chart and vitalsign form. Some of the patients stayed in the emergency room for more than 4 hours, but there was still no relevant records. I don't understand why. Since nearly half of the value is missing, I am trying to solve it as much as possible, but I still have not found a way to remedy it.Is there any other way to find this data?
• Thank you for your reply. Thank you very much!