TABLE pivoted_oasis

[datascience100]

Prerequisites

• Put an X between the brackets on this line if you have done all of the following:
  • Checked the online documentation: https://mimic.physionet.org/about/mimic/
  • Checked that your issue isn't already addressed: https://github.com/MIT-LCP/mimic-code/issues?utf8=%E2%9C%93&q=

Description

Hello,
I found a problem when creating the TABLE pivoted_oasis in PostgreSQL. There is no previous table or view for the relation surgflag sf. I have not seen it included in the views required to run this query. Also, there are two left joins performed with the table ventdurations. They are referenced by two different initials but then the left join is made with different initials (ventdurations vd1 on co.icustay_id = vd.icustay_id, and ventdurations vd2 on co.icustay_id = vd.icustay_id), which has to be corrected in order to run the query.

Thank you.

[alistairewj]

Ah good catch, the surgflag table is identical to here:

mimic-code/concepts/severityscores/oasis.sql

Lines 35 to 46 in d512dee

	with surgflag as
	(
	select ie.icustay_id
	, max(case
	when lower(curr_service) like '%surg%' then 1
	when curr_service = 'ORTHO' then 1
	else 0 end) as surgical
	FROM `physionet-data.mimiciii_clinical.icustays` ie
	left join `physionet-data.mimiciii_clinical.services` se
	on ie.hadm_id = se.hadm_id
	and se.transfertime < DATETIME_ADD(ie.intime, INTERVAL '1' DAY)
	group by ie.icustay_id

I'll try to fix these up in a bit.

[datascience100]

Thank you very much. Yes, the ventdurations issue and the surgflag issue.