drop table ALINE_PAIRED_DATA_APR15;
create table ALINE_PAIRED_DATA_APR15 as
with treated as
(select distinct
	m.icustay_id as icustay_id_t
  ,m.n1
	,ICU_LOS_DAY ICU_LOS_DAY_T
	,HOSPITAL_LOS_DAY HOSPITAL_LOS_DAY_T
	,HOSP_EXP_FLG HOSP_EXP_FLG_T
	,ICU_EXP_FLG ICU_EXP_FLG_T
	,AGE AGE_T
	,GENDER_NUM GENDER_NUM_T
	,GENDER GENDER_T
	,ETHNIC_GROUP ETHNIC_GROUP_T
	,WEIGHT_FIRST WEIGHT_FIRST_T
	,BMI BMI_T
	,SAPSI_FIRST SAPSI_FIRST_T
	,SOFA_FIRST SOFA_FIRST_T
	,SERVICE_UNIT SERVICE_UNIT_T
	,SERVICE_NUM SERVICE_NUM_T
	,DAY_ICU_INTIME DAY_ICU_INTIME_T
	,DAY_ICU_INTIME_NUM DAY_ICU_INTIME_NUM_T
	,HOUR_ICU_INTIME HOUR_ICU_INTIME_T
	,MORT_DAY MORT_DAY_T
	,DAY_28_FLG DAY_28_FLG_T
	,MORT_DAY_CENSORED MORT_DAY_CENSORED_T
	,CENSOR_FLG CENSOR_FLG_T
	,VENT_DAY VENT_DAY_T
	,VENT_FREE_DAY VENT_FREE_DAY_T
	,ANES_FLG ANES_FLG_T
	,ANES_DAY ANES_DAY_T
	,ANES_FREE_DAY ANES_FREE_DAY_T
	,FENTANYL_FLG FENTANYL_FLG_T
	,MIDAZOLAM_FLG MIDAZOLAM_FLG_T
	,PROPOFOL_FLG PROPOFOL_FLG_T
	,DILAUDID_FLG DILAUDID_FLG_T
	,SEPSIS_FLG SEPSIS_FLG_T
	,CHF_FLG CHF_FLG_T
	,AFIB_FLG AFIB_FLG_T
	,RENAL_FLG RENAL_FLG_T
	,LIVER_FLG LIVER_FLG_T
	,COPD_FLG COPD_FLG_T
	,CAD_FLG CAD_FLG_T
	,STROKE_FLG STROKE_FLG_T
	,MAL_FLG MAL_FLG_T
	,RESP_FLG RESP_FLG_T
	,ARDS_FLG ARDS_FLG_T
	,PNEUMONIA_FLG PNEUMONIA_FLG_T
	,MAP_1ST MAP_1ST_T
	,HR_1ST HR_1ST_T
	,TEMP_1ST TEMP_1ST_T
	,SPO2_1ST SPO2_1ST_T
	,CVP_1ST CVP_1ST_T
	,ABG_COUNT ABG_COUNT_T
	,WBC_FIRST WBC_FIRST_T
	,HGB_FIRST HGB_FIRST_T
	,PLATELET_FIRST PLATELET_FIRST_T
	,SODIUM_FIRST SODIUM_FIRST_T
	,POTASSIUM_FIRST POTASSIUM_FIRST_T
	,TCO2_FIRST TCO2_FIRST_T
	,CHLORIDE_FIRST CHLORIDE_FIRST_T
	,BUN_FIRST BUN_FIRST_T
	,CREATININE_FIRST CREATININE_FIRST_T
	,GLUCOSE_FIRST GLUCOSE_FIRST_T
	,CALCIUM_FIRST CALCIUM_FIRST_T
	,MAGNESIUM_FIRST MAGNESIUM_FIRST_T
	,PHOSPHATE_FIRST PHOSPHATE_FIRST_T
	,AST_FIRST AST_FIRST_T
	,ALT_FIRST ALT_FIRST_T
	,LDH_FIRST LDH_FIRST_T
	,BILIRUBIN_FIRST BILIRUBIN_FIRST_T
	,ALP_FIRST ALP_FIRST_T
	,ALBUMIN_FIRST ALBUMIN_FIRST_T
	,TROPONIN_T_FIRST TROPONIN_T_FIRST_T
	,CK_FIRST CK_FIRST_T
	,BNP_FIRST BNP_FIRST_T
	,LACTATE_FIRST LACTATE_FIRST_T
	,PH_FIRST PH_FIRST_T
	,SVO2_FIRST SVO2_FIRST_T
	,PO2_FIRST PO2_FIRST_T
	,PCO2_FIRST PCO2_FIRST_T
	,IV_DAY_1 IV_DAY_1_T
	,RBC_DAY_1 RBC_DAY_1_T
	,DNR_ADM_FLG DNR_ADM_FLG_T
	,DNR_SWITCH_FLG DNR_SWITCH_FLG_T
	,CMO_SWITCH_FLG CMO_SWITCH_FLG_T
	,DNR_CMO_SWITCH_FLG DNR_CMO_SWITCH_FLG_T
  ,ALINE_DURATION ALINE_DURATION_T 
	from aline_match m
	join aline_mimic_data_april15 d on m.icustay_id=d.icustay_id and m.treated=1
)

--select count(*) from treated;

, untreated as
(select
	m.icustay_id as icustay_id_u
  ,m.id
	,ICU_LOS_DAY ICU_LOS_DAY_U
	,HOSPITAL_LOS_DAY HOSPITAL_LOS_DAY_U
	,HOSP_EXP_FLG HOSP_EXP_FLG_U
	,ICU_EXP_FLG ICU_EXP_FLG_U
	,AGE AGE_U
	,GENDER_NUM GENDER_NUM_U
	,GENDER GENDER_U
	,ETHNIC_GROUP ETHNIC_GROUP_U
	,WEIGHT_FIRST WEIGHT_FIRST_U
	,BMI BMI_U
	,SAPSI_FIRST SAPSI_FIRST_U
	,SOFA_FIRST SOFA_FIRST_U
	,SERVICE_UNIT SERVICE_UNIT_U
	,SERVICE_NUM SERVICE_NUM_U
	,DAY_ICU_INTIME DAY_ICU_INTIME_U
	,DAY_ICU_INTIME_NUM DAY_ICU_INTIME_NUM_U
	,HOUR_ICU_INTIME HOUR_ICU_INTIME_U
	,MORT_DAY MORT_DAY_U
	,DAY_28_FLG DAY_28_FLG_U
	,MORT_DAY_CENSORED MORT_DAY_CENSORED_U
	,CENSOR_FLG CENSOR_FLG_U
	,VENT_DAY VENT_DAY_U
	,VENT_FREE_DAY VENT_FREE_DAY_U
	,ANES_FLG ANES_FLG_U
	,ANES_DAY ANES_DAY_U
	,ANES_FREE_DAY ANES_FREE_DAY_U
	,FENTANYL_FLG FENTANYL_FLG_U
	,MIDAZOLAM_FLG MIDAZOLAM_FLG_U
	,PROPOFOL_FLG PROPOFOL_FLG_U
	,DILAUDID_FLG DILAUDID_FLG_U
	,SEPSIS_FLG SEPSIS_FLG_U
	,CHF_FLG CHF_FLG_U
	,AFIB_FLG AFIB_FLG_U
	,RENAL_FLG RENAL_FLG_U
	,LIVER_FLG LIVER_FLG_U
	,COPD_FLG COPD_FLG_U
	,CAD_FLG CAD_FLG_U
	,STROKE_FLG STROKE_FLG_U
	,MAL_FLG MAL_FLG_U
	,RESP_FLG RESP_FLG_U
	,ARDS_FLG ARDS_FLG_U
	,PNEUMONIA_FLG PNEUMONIA_FLG_U
	,MAP_1ST MAP_1ST_U
	,HR_1ST HR_1ST_U
	,TEMP_1ST TEMP_1ST_U
	,SPO2_1ST SPO2_1ST_U
	,CVP_1ST CVP_1ST_U
	,ABG_COUNT ABG_COUNT_U
	,WBC_FIRST WBC_FIRST_U
	,HGB_FIRST HGB_FIRST_U
	,PLATELET_FIRST PLATELET_FIRST_U
	,SODIUM_FIRST SODIUM_FIRST_U
	,POTASSIUM_FIRST POTASSIUM_FIRST_U
	,TCO2_FIRST TCO2_FIRST_U
	,CHLORIDE_FIRST CHLORIDE_FIRST_U
	,BUN_FIRST BUN_FIRST_U
	,CREATININE_FIRST CREATININE_FIRST_U
	,GLUCOSE_FIRST GLUCOSE_FIRST_U
	,CALCIUM_FIRST CALCIUM_FIRST_U
	,MAGNESIUM_FIRST MAGNESIUM_FIRST_U
	,PHOSPHATE_FIRST PHOSPHATE_FIRST_U
	,AST_FIRST AST_FIRST_U
	,ALT_FIRST ALT_FIRST_U
	,LDH_FIRST LDH_FIRST_U
	,BILIRUBIN_FIRST BILIRUBIN_FIRST_U
	,ALP_FIRST ALP_FIRST_U
	,ALBUMIN_FIRST ALBUMIN_FIRST_U
	,TROPONIN_T_FIRST TROPONIN_T_FIRST_U
	,CK_FIRST CK_FIRST_U
	,BNP_FIRST BNP_FIRST_U
	,LACTATE_FIRST LACTATE_FIRST_U
	,PH_FIRST PH_FIRST_U
	,SVO2_FIRST SVO2_FIRST_U
	,PO2_FIRST PO2_FIRST_U
	,PCO2_FIRST PCO2_FIRST_U
	,IV_DAY_1 IV_DAY_1_U
	,RBC_DAY_1 RBC_DAY_1_U
	,DNR_ADM_FLG DNR_ADM_FLG_U
	,DNR_SWITCH_FLG DNR_SWITCH_FLG_U
	,CMO_SWITCH_FLG CMO_SWITCH_FLG_U
	,DNR_CMO_SWITCH_FLG DNR_CMO_SWITCH_FLG_U
  ,ALINE_DURATION ALINE_DURATION_U
	from aline_match m
	join aline_mimic_data_april15 d on m.icustay_id=d.icustay_id and m.treated=0
)

--select * from untreated;

, matched as
(select 
t.*
, u.*
from treated t
join untreated u on t.n1=u.id
)

select * from matched;


------- Aline duration analysis ---------
create table aline_duration_data_apr15 as
select
m.icustay_id
,ALINE_FLG
,ALINE_DURATION
,DAY_28_FLG
,ICU_LOS_DAY
,HOSPITAL_LOS_DAY
,HOSP_EXP_FLG
,ICU_EXP_FLG
from aline_match m
join aline_mimic_data_april15 d on m.icustay_id=d.icustay_id;