From 71d73336ebbfed2a6a8597a8fce3d997b2de0f2d Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Wed, 8 Dec 2021 11:57:33 -0500
Subject: [PATCH 01/10] add milrinone to medication tables

---
 mimic-iv/concepts/medication/milrinone.sql | 9 +++++++++
 1 file changed, 9 insertions(+)
 create mode 100644 mimic-iv/concepts/medication/milrinone.sql

diff --git a/mimic-iv/concepts/medication/milrinone.sql b/mimic-iv/concepts/medication/milrinone.sql
new file mode 100644
index 000000000..12d7e6c8d
--- /dev/null
+++ b/mimic-iv/concepts/medication/milrinone.sql
@@ -0,0 +1,9 @@
+-- This query extracts dose+durations of milrinone administration
+select
+stay_id, linkorderid
+, rate as vaso_rate
+, amount as vaso_amount
+, starttime
+, endtime
+from `physionet-data.mimic_icu.inputevents`
+where itemid = 221986 -- milrinone
\ No newline at end of file

From 4c8e368a065da215b87109deba741e60c95a5599 Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Wed, 8 Dec 2021 13:27:24 -0500
Subject: [PATCH 02/10] validate/harmonize units of measure for vasopressors

---
 mimic-iv/concepts/medication/dobutamine.sql     | 1 +
 mimic-iv/concepts/medication/dopamine.sql       | 1 +
 mimic-iv/concepts/medication/epinephrine.sql    | 1 +
 mimic-iv/concepts/medication/milrinone.sql      | 1 +
 mimic-iv/concepts/medication/norepinephrine.sql | 6 ++++++
 mimic-iv/concepts/medication/phenylephrine.sql  | 4 +++-
 mimic-iv/concepts/medication/vasopressin.sql    | 5 ++++-
 7 files changed, 17 insertions(+), 2 deletions(-)

diff --git a/mimic-iv/concepts/medication/dobutamine.sql b/mimic-iv/concepts/medication/dobutamine.sql
index d2641eeb1..b3e61a096 100644
--- a/mimic-iv/concepts/medication/dobutamine.sql
+++ b/mimic-iv/concepts/medication/dobutamine.sql
@@ -1,6 +1,7 @@
 -- This query extracts dose+durations of dopamine administration
 select
 stay_id, linkorderid
+-- all rows in mcg/kg/min
 , rate as vaso_rate
 , amount as vaso_amount
 , starttime
diff --git a/mimic-iv/concepts/medication/dopamine.sql b/mimic-iv/concepts/medication/dopamine.sql
index 659f7cd52..3d5229657 100644
--- a/mimic-iv/concepts/medication/dopamine.sql
+++ b/mimic-iv/concepts/medication/dopamine.sql
@@ -1,6 +1,7 @@
 -- This query extracts dose+durations of dopamine administration
 select
 stay_id, linkorderid
+-- all rows in mcg/kg/min
 , rate as vaso_rate
 , amount as vaso_amount
 , starttime
diff --git a/mimic-iv/concepts/medication/epinephrine.sql b/mimic-iv/concepts/medication/epinephrine.sql
index 1bd23c8d9..2feaf7b23 100644
--- a/mimic-iv/concepts/medication/epinephrine.sql
+++ b/mimic-iv/concepts/medication/epinephrine.sql
@@ -1,6 +1,7 @@
 -- This query extracts dose+durations of epinephrine administration
 select
 stay_id, linkorderid
+-- all rows in mcg/kg/min
 , rate as vaso_rate
 , amount as vaso_amount
 , starttime
diff --git a/mimic-iv/concepts/medication/milrinone.sql b/mimic-iv/concepts/medication/milrinone.sql
index 12d7e6c8d..c90e5204a 100644
--- a/mimic-iv/concepts/medication/milrinone.sql
+++ b/mimic-iv/concepts/medication/milrinone.sql
@@ -1,6 +1,7 @@
 -- This query extracts dose+durations of milrinone administration
 select
 stay_id, linkorderid
+-- all rows in mcg/kg/min
 , rate as vaso_rate
 , amount as vaso_amount
 , starttime
diff --git a/mimic-iv/concepts/medication/norepinephrine.sql b/mimic-iv/concepts/medication/norepinephrine.sql
index d3d2b1421..ae889cf9a 100644
--- a/mimic-iv/concepts/medication/norepinephrine.sql
+++ b/mimic-iv/concepts/medication/norepinephrine.sql
@@ -1,6 +1,12 @@
 -- This query extracts dose+durations of norepinephrine administration
 select
   stay_id, linkorderid
+  -- two rows in mg/kg/min... rest in mcg/kg/min
+  -- the rows in mg/kg/min are documented incorrectly
+  , CASE WHEN rateuom = 'mg/kg/min' AND patientweight = 1 THEN rate
+  -- below row is written for completion, but doesn't impact rows
+  WHEN rateuom = 'mg/kg/min' THEN rate * 1000.0
+  ELSE rate END AS vaso_rate
   , rate as vaso_rate
   , amount as vaso_amount
   , starttime
diff --git a/mimic-iv/concepts/medication/phenylephrine.sql b/mimic-iv/concepts/medication/phenylephrine.sql
index 33af6a205..3f020f0b3 100644
--- a/mimic-iv/concepts/medication/phenylephrine.sql
+++ b/mimic-iv/concepts/medication/phenylephrine.sql
@@ -1,7 +1,9 @@
 -- This query extracts dose+durations of phenylephrine administration
 select
   stay_id, linkorderid
-  , rate as vaso_rate
+  -- one row in mcg/min, the rest in mcg/kg/min
+  , CASE WHEN rateuom = 'mcg/min' THEN rate / patientweight
+  ELSE rate END as vaso_rate
   , amount as vaso_amount
   , starttime
   , endtime
diff --git a/mimic-iv/concepts/medication/vasopressin.sql b/mimic-iv/concepts/medication/vasopressin.sql
index c898403a2..9b293d60e 100644
--- a/mimic-iv/concepts/medication/vasopressin.sql
+++ b/mimic-iv/concepts/medication/vasopressin.sql
@@ -1,7 +1,10 @@
 -- This query extracts dose+durations of vasopressin administration
 select
   stay_id, linkorderid
-  , rate as vaso_rate
+  -- three rows in units/min, rest in units/hour
+  -- the three rows in units/min look reasonable and fit with the patient course
+  , CASE WHEN rateuom = 'units/min' THEN rate * 60.0
+    ELSE rate END AS vaso_rate
   , amount as vaso_amount
   , starttime
   , endtime

From 1cf6f3253beeecba4c0ca3969f79fda294d7c5d4 Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Wed, 8 Dec 2021 13:29:28 -0500
Subject: [PATCH 03/10] add tests verifying vasopressor data quality

---
 mimic-iv/tests/test_medication.py | 109 ++++++++++++++++++++++++++++++
 1 file changed, 109 insertions(+)
 create mode 100644 mimic-iv/tests/test_medication.py

diff --git a/mimic-iv/tests/test_medication.py b/mimic-iv/tests/test_medication.py
new file mode 100644
index 000000000..4a42622b9
--- /dev/null
+++ b/mimic-iv/tests/test_medication.py
@@ -0,0 +1,109 @@
+import pandas as pd
+from pandas.io import gbq
+import logging
+
+_LOGGER = logging.getLogger(__name__)
+
+def test_vasopressor_units(dataset, project_id):
+    # verify vasopressors in expected units
+    units = {
+        'milrinone': 'mcg/kg/min',
+        'dobutamine': 'mcg/kg/min',
+        'dopamine': 'mcg/kg/min',
+        'epinephrine': 'mcg/kg/min',
+        'norepinephrine': 'mcg/kg/min',
+        'phenylephrine': 'mcg/kg/min',
+        'vasopressin': 'units/hour',
+    }
+
+    itemids = {
+        'milrinone': 221986,
+        'dobutamine': 221653,
+        'dopamine': 221662,
+        'epinephrine': 221289,
+        'norepinephrine': 221906,
+        'phenylephrine': 221749,
+        'vasopressin': 222315,
+    }
+
+    hadm_id = {
+        'norepinephrine': [21898267],
+        'phenylephrine': [26809360],
+        'vasopressin': [26272149]
+    }
+
+    # verify we always have a unit of measure for the rate
+    query = f"""
+    select itemid, COUNT(*) AS n
+    FROM mimic_icu.inputevents
+    WHERE itemid IN ({", ".join([str(x) for x in itemids.values()])})
+    AND rateuom IS NULL
+    GROUP BY itemid
+    """
+    df = gbq.read_gbq(query, project_id=project_id, dialect="standard")
+    assert df.shape[0] == 0, 'found vasopressors with null units'
+
+    # norepinephrine has two rows in mg/kg/min
+    #   these are actually supposed to be mcg/kg/min - and the patient weight has been set to 1 to make it work
+    # phenylephrine has one row in mcg/min - looks fine, within expected dose
+    # vasopressin three rows in units/min - these look OK
+    
+    for drug, hadm_id_list in hadm_id.items():
+        query = f"""
+        select hadm_id, rate, rateuom
+        FROM mimic_icu.inputevents
+        WHERE itemid = {itemids[drug]}
+        AND rateuom != '{units[drug]}'
+        LIMIT 10
+        """
+        df = gbq.read_gbq(query, project_id=project_id, dialect="standard")
+        # if we find new uninspected rows, raise a warning. this will only happen when mimic-iv is updated.
+        if (~df['hadm_id'].contains(hadm_id_list)).any():
+            _LOGGER.warn(f"""New data found with non-standard unit. Inspect the data with this query:
+
+            select *
+            from `physionet-data.mimic_icu.inputevents`
+            where itemid = {itemids['vasopressin']}
+            and stay_id in (
+                select stay_id from `physionet-data.mimic_icu.inputevents`
+                where itemid = {itemids['vasopressin']}
+                and rateuom != '{units['vasopressin']}'
+            )
+            order by starttime
+            """)
+        assert df.shape[0] != 10, f'many rows found with non-standard unit for {drug}'
+
+def test_vasopressor_doses(dataset, project_id):
+    # verify vasopressors have reasonable doses
+    # based on uptodate graphic 99963 version 19.0
+    # double the maximum dose used in refractory shock is the upper limit used
+    itemids = {
+        'milrinone': 221986,
+        'dobutamine': 221653,
+        'dopamine': 221662,
+        'epinephrine': 221289,
+        'norepinephrine': 221906,
+        'phenylephrine': 221749,
+        'vasopressin': 222315,
+    }
+    max_dose = {
+        'milrinone': 1.5,
+        'dobutamine': 40,
+        'dopamine': 40,
+        'epinephrine': 4,
+        'norepinephrine': 6.6,
+        'phenylephrine': 18.2,
+        'vasopressin': 0.08,
+    }
+
+    for vaso, dose in max_dose.items():
+        query = f"""
+        select COUNT(vaso_rate) AS n_above_rate
+        FROM mimic_derived.{vaso}
+        WHERE vaso_rate >= {dose}
+        """
+        df = gbq.read_gbq(query, project_id=project_id, dialect="standard")
+        n_above_rate = df.loc[0, 'n_above_rate']
+        assert n_above_rate == 0, f'found {vaso} rows with dose above {dose}, potentially incorrect'
+
+

From 5a27a0d380173ab7ca6a937e6a8055551d7552df Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Wed, 8 Dec 2021 14:05:06 -0500
Subject: [PATCH 04/10] combine vasoactive agent doses into one table

---
 .../concepts/medication/vasoactive_agent.sql  | 95 +++++++++++++++++++
 1 file changed, 95 insertions(+)
 create mode 100644 mimic-iv/concepts/medication/vasoactive_agent.sql

diff --git a/mimic-iv/concepts/medication/vasoactive_agent.sql b/mimic-iv/concepts/medication/vasoactive_agent.sql
new file mode 100644
index 000000000..bba6fa874
--- /dev/null
+++ b/mimic-iv/concepts/medication/vasoactive_agent.sql
@@ -0,0 +1,95 @@
+-- This query creates a single table with ongoing doses of vasoactive agents.
+-- TBD: rarely angiotensin II, methylene blue, and isoprenaline/isoproterenol are used.
+-- these are not in the query currently (they don't appear to be documented in MetaVision).
+
+-- collect all vasopressor administration times
+-- create a single table with these as start/stop times
+WITH tm AS
+(
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.dobutamine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.dopamine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.epinephrine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.norepinephrine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.phenylephrine
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.vasopressin
+    UNION DISTINCT
+    SELECT stay_id, starttime AS vasotime FROM mimic_derived.milrinone
+    UNION DISTINCT
+    -- combine end times from the same tables
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.dobutamine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.dopamine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.epinephrine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.norepinephrine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.phenylephrine
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.vasopressin
+    UNION DISTINCT
+    SELECT stay_id, endtime AS vasotime FROM mimic_derived.milrinone
+)
+-- create starttime/endtime from all possible times collected
+, tm_lag AS
+(
+    SELECT stay_id
+    , vasotime AS starttime
+    -- note: the last row for each partition (stay_id) will have a NULL endtime
+    -- we can drop this row later, as we know that no vasopressor will start at this time
+    -- (otherwise, we would have a later end time, which would mean it's not the last row!)
+    -- QED? :)
+    , LEAD(vasotime, 1) OVER (PARTITION BY stay_id ORDER BY vasotime) AS endtime
+    FROM tm
+)
+-- left join to raw data tables to combine doses
+SELECT t.stay_id, t.starttime, t.endtime
+-- inopressors/vasopressors
+, dop.vaso_rate AS dopamine
+, epi.vaso_rate AS epinephrine
+, nor.vaso_rate AS norepinephrine
+, phe.vaso_rate AS phenylephrine
+, vas.vaso_rate AS vasopressin
+-- inodialators
+, dob.vaso_rate AS dobutamine
+, mil.vaso_rate AS milrinone
+-- isoproterenol is used in CCU/CVICU but not in metavision
+-- other drugs not included here but (rarely) used in the BIDMC:
+-- angiotensin II, methylene blue
+FROM tm_lag t
+LEFT JOIN mimic_derived.dobutamine dob
+    ON t.stay_id = dob.stay_id
+    AND t.starttime >= dob.starttime
+    AND t.endtime <= dob.endtime
+LEFT JOIN mimic_derived.dopamine dop
+    ON t.stay_id = dop.stay_id
+    AND t.starttime >= dop.starttime
+    AND t.endtime <= dop.endtime
+LEFT JOIN mimic_derived.epinephrine epi
+    ON t.stay_id = epi.stay_id
+    AND t.starttime >= epi.starttime
+    AND t.endtime <= epi.endtime
+LEFT JOIN mimic_derived.norepinephrine nor
+    ON t.stay_id = nor.stay_id
+    AND t.starttime >= nor.starttime
+    AND t.endtime <= nor.endtime
+LEFT JOIN mimic_derived.phenylephrine phe
+    ON t.stay_id = phe.stay_id
+    AND t.starttime >= phe.starttime
+    AND t.endtime <= phe.endtime
+LEFT JOIN mimic_derived.vasopressin vas
+    ON t.stay_id = vas.stay_id
+    AND t.starttime >= vas.starttime
+    AND t.endtime <= vas.endtime
+LEFT JOIN mimic_derived.milrinone mil
+    ON t.stay_id = mil.stay_id
+    AND t.starttime >= mil.starttime
+    AND t.endtime <= mil.endtime
+-- remove the final row for each stay_id
+-- it will not have any infusions associated with it
+WHERE t.endtime IS NOT NULL;
\ No newline at end of file

From b36635d8bfa03fe0858d9da2387cb05782a98574 Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Wed, 8 Dec 2021 14:05:23 -0500
Subject: [PATCH 05/10] calculate NED from vasoactive agent table

---
 .../norepinephrine_equivalent_dose.sql        | 20 +++++++++++++++++++
 1 file changed, 20 insertions(+)
 create mode 100644 mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql

diff --git a/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql b/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql
new file mode 100644
index 000000000..2a2924af7
--- /dev/null
+++ b/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql
@@ -0,0 +1,20 @@
+-- This query calculates norepinephrine equivalent dose for vasopressors.
+-- Based on "Vasopressor dose equivalence: A scoping review and suggested formula"
+-- by Goradia et al. 2020.
+SELECT t.stay_id, t.starttime, t.endtime
+-- calculate the dose
+, norepinephrine
+  + epinephrine
+  + phenylephrine/10
+  + dopamine/100
+  -- + metaraminol/8 -- metaraminol not used in BIDMC
+  + vasopressin*2.5
+  -- angotensin_ii*10 -- angitensin ii rarely used, currently not incorporated
+  -- (it could be included due to norepinephrine sparing effects)
+  AS norepinephrine_equivalent_dose
+FROM mimic_derived.vasoactive_agent
+WHERE norepinephrine IS NOT NULL
+OR epinephrine IS NOT NULL
+OR phenylephrine IS NOT NULL
+OR dopamine IS NOT NULL
+OR vasopressin IS NOT NULL;
\ No newline at end of file

From 9caae3388f1eed1cb13433a6b3b788305b88299c Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Wed, 8 Dec 2021 14:09:25 -0500
Subject: [PATCH 06/10] add vasoactive agent/NED skips

---
 mimic-iv/concepts/make_concepts.sh | 13 ++++++++++++-
 1 file changed, 12 insertions(+), 1 deletion(-)

diff --git a/mimic-iv/concepts/make_concepts.sh b/mimic-iv/concepts/make_concepts.sh
index f8275e188..5a9c818bb 100644
--- a/mimic-iv/concepts/make_concepts.sh
+++ b/mimic-iv/concepts/make_concepts.sh
@@ -22,6 +22,11 @@ do
             # kdigo_stages needs to be run after creat/uo
             elif [[ "${tbl}" == "kdigo_stages" ]]; then
                 continue
+            # vasoactive tables also need to be run last
+            elif [[ "${tbl}" == "vasoactive_agent" ]]; then
+                continue
+            elif [[ "${tbl}" == "norepinephrine_equivalent_dose" ]]; then
+                continue
             fi
             echo "Generating ${TARGET_DATASET}.${tbl}"
             bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.${tbl} < ${d}/${fn}
@@ -35,4 +40,10 @@ bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.
 
 # generate first_day_sofa table last
 echo "Generating ${TARGET_DATASET}.kdigo_stages"
-bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.kdigo_stages < organfailure/kdigo_stages.sql
\ No newline at end of file
+bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.kdigo_stages < organfailure/kdigo_stages.sql
+
+# generate vasoactive tables - first agent table, then NED table
+echo "Generating ${TARGET_DATASET}.vasoactive_agent"
+bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.vasoactive_agent < medication/vasoactive_agent.sql
+echo "Generating ${TARGET_DATASET}.norepinephrine_equivalent_dose"
+bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.norepinephrine_equivalent_dose < medication/norepinephrine_equivalent_dose.sql
\ No newline at end of file

From 958fcd50d3f2a6f29cf403d1b2c8fb1b392d66cb Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Wed, 8 Dec 2021 14:16:00 -0500
Subject: [PATCH 07/10] refactor so skipped tables are later generated in one
 loop

---
 mimic-iv/concepts/make_concepts.sh | 46 ++++++++++++++----------------
 1 file changed, 22 insertions(+), 24 deletions(-)

diff --git a/mimic-iv/concepts/make_concepts.sh b/mimic-iv/concepts/make_concepts.sh
index 5a9c818bb..565a190a3 100644
--- a/mimic-iv/concepts/make_concepts.sh
+++ b/mimic-iv/concepts/make_concepts.sh
@@ -16,34 +16,32 @@ do
             # table name is file name minus extension
             tbl=`echo $fn | rev | cut -d. -f2- | rev`
 
-            # skip first_day_sofa as it depends on other firstday queries
-            if [[ "${tbl}" == "first_day_sofa" ]]; then
-                continue
-            # kdigo_stages needs to be run after creat/uo
-            elif [[ "${tbl}" == "kdigo_stages" ]]; then
-                continue
-            # vasoactive tables also need to be run last
-            elif [[ "${tbl}" == "vasoactive_agent" ]]; then
-                continue
-            elif [[ "${tbl}" == "norepinephrine_equivalent_dose" ]]; then
-                continue
+            # skip certain tables where order matters - generated at the end of the script
+            skip=0
+            for skip_table in first_day_sofa kdigo_stages vasoactive_agent norepinephrine_eqivalent_dose
+            do
+              if [[ "${tbl}" == "${skip_table}" ]]; then
+                skip=1
+                break
+              fi
+            done;
+            if [[ "${skip}" == "1" ]]; then
+              continue
             fi
+
+            # not skipping - so generate the table on bigquery
             echo "Generating ${TARGET_DATASET}.${tbl}"
-            bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.${tbl} < ${d}/${fn}
+            echo bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.${tbl} < ${d}/${fn}
         fi
     done
 done
 
-# generate first_day_sofa table last
-echo "Generating ${TARGET_DATASET}.first_day_sofa"
-bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.first_day_sofa < firstday/first_day_sofa.sql
-
-# generate first_day_sofa table last
-echo "Generating ${TARGET_DATASET}.kdigo_stages"
-bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.kdigo_stages < organfailure/kdigo_stages.sql
+echo "Now generating tables which were skipped due to depending on other tables."
+# generate tables after the above, and in a specific order to ensure dependencies are met
+for table_path in firstday/first_day_sofa organfailure/kdigo_stages medication/vasoactive_agent medication/norepinephrine_equivalent_dose;
+do
+  table=`echo $table_path | rev | cut -d/ -f1 | rev`
 
-# generate vasoactive tables - first agent table, then NED table
-echo "Generating ${TARGET_DATASET}.vasoactive_agent"
-bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.vasoactive_agent < medication/vasoactive_agent.sql
-echo "Generating ${TARGET_DATASET}.norepinephrine_equivalent_dose"
-bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.norepinephrine_equivalent_dose < medication/norepinephrine_equivalent_dose.sql
\ No newline at end of file
+  echo "Generating ${TARGET_DATASET}.${table}"
+  bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.${table} < ${table_path}.sql
+done
\ No newline at end of file

From ddf4d70da3a636e72def4a2c739b8cf1f9bbeeca Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Wed, 8 Dec 2021 17:09:45 -0500
Subject: [PATCH 08/10] remove unused alias

---
 mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql b/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql
index 2a2924af7..e004d1770 100644
--- a/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql
+++ b/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql
@@ -1,7 +1,7 @@
 -- This query calculates norepinephrine equivalent dose for vasopressors.
 -- Based on "Vasopressor dose equivalence: A scoping review and suggested formula"
 -- by Goradia et al. 2020.
-SELECT t.stay_id, t.starttime, t.endtime
+SELECT stay_id, starttime, endtime
 -- calculate the dose
 , norepinephrine
   + epinephrine

From 4a3621f21b6b83d17ce0cded4014b8c11fbe369f Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Wed, 8 Dec 2021 17:11:57 -0500
Subject: [PATCH 09/10] round to 4 decimal places, fix null values forcing
 result to be null

---
 .../medication/norepinephrine_equivalent_dose.sql   | 13 ++++++++-----
 1 file changed, 8 insertions(+), 5 deletions(-)

diff --git a/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql b/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql
index e004d1770..c06c983d6 100644
--- a/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql
+++ b/mimic-iv/concepts/medication/norepinephrine_equivalent_dose.sql
@@ -3,12 +3,15 @@
 -- by Goradia et al. 2020.
 SELECT stay_id, starttime, endtime
 -- calculate the dose
-, norepinephrine
-  + epinephrine
-  + phenylephrine/10
-  + dopamine/100
+, ROUND(COALESCE(norepinephrine, 0)
+  + COALESCE(epinephrine, 0)
+  + COALESCE(phenylephrine/10, 0)
+  + COALESCE(dopamine/100, 0)
   -- + metaraminol/8 -- metaraminol not used in BIDMC
-  + vasopressin*2.5
+  + COALESCE(vasopressin*2.5, 0)
+  -- angotensin_ii*10 -- angitensin ii rarely used, currently not incorporated
+  -- (it could be included due to norepinephrine sparing effects)
+  , 4) AS norepinephrine_equivalent_dose
   -- angotensin_ii*10 -- angitensin ii rarely used, currently not incorporated
   -- (it could be included due to norepinephrine sparing effects)
   AS norepinephrine_equivalent_dose

From f214349f90b2f868e61822dc4e89f3a7f8d1de9c Mon Sep 17 00:00:00 2001
From: Alistair Johnson <alistairewj@gmail.com>
Date: Tue, 14 Dec 2021 21:05:15 -0500
Subject: [PATCH 10/10] remove echo added for debug

---
 mimic-iv/concepts/make_concepts.sh | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/mimic-iv/concepts/make_concepts.sh b/mimic-iv/concepts/make_concepts.sh
index 565a190a3..29d25d771 100644
--- a/mimic-iv/concepts/make_concepts.sh
+++ b/mimic-iv/concepts/make_concepts.sh
@@ -31,7 +31,7 @@ do
 
             # not skipping - so generate the table on bigquery
             echo "Generating ${TARGET_DATASET}.${tbl}"
-            echo bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.${tbl} < ${d}/${fn}
+            bq query --use_legacy_sql=False --replace --destination_table=${TARGET_DATASET}.${tbl} < ${d}/${fn}
         fi
     done
 done