interim_query.sql

"SELECT (hh_score + work_score + social_score + transit_score) * Power(1.3,comorbid_score) AS total_score, srp.* FROM (SELECT (LS_COHAB_BTW_18_64+LS_COHAB_OVER_64+LS_COHAB_UNDER_18*power(1.5,school_exposure)-1)* ((work_days)*(24-8-1-8) + (7-work_days)*(24-8))*Power(1.2,caregiver)+ (HH_other_adult)*(7*8) AS hh_score, work_days*8*POWER(5,high_risk_prof)*POWER(0.8,work_cleaning)*3 AS work_score, (event_location_score)*(GREATEST(1,interactions-(LS_COHAB_BTW_18_64+LS_COHAB_OVER_64+LS_COHAB_UNDER_18)))*3 AS social_score, (work_days*1)*uses_transit*5 AS transit_score, srp.* FROM (SELECT srp.*, (CASE WHEN LS_COHAB_BTW_18_64+LS_COHAB_OVER_64-1>0 THEN 1 ELSE 0 END) AS HH_other_adult, (CASE WHEN CI_GATHERINGS_LOCATION_ID = 348 THEN 1 WHEN CI_GATHERINGS_LOCATION_ID = 347 THEN 3 WHEN CI_GATHERINGS_LOCATION_ID = 601 THEN 4 ELSE 0 END) AS event_location_score, (CASE WHEN instr(':' || WS_WORK_TRANSPORTATION_IDS || ':', ':287:') > 0 or instr(':' || WS_WORK_TRANSPORTATION_IDS || ':', ':288:') > 0 or instr(':' || WS_WORK_TRANSPORTATION_IDS || ':', ':290:') > 0 THEN 1 ELSE 0 END) AS uses_transit, (CASE WHEN WS_WKPL_FREQ_ID =201 THEN 1 WHEN WS_WKPL_FREQ_ID =202 THEN 3 WHEN WS_WKPL_FREQ_ID =221 THEN 5 ELSE 0 END) AS work_days, (CASE WHEN LS_COHAB_OVER_64+LS_COHAB_UNDER_18 > 1 and (instr(':' || OCC_JOBS_IDS || ':', ':180:') > 0 or instr(':' || WS_WORK_TRANSPORTATION_IDS || ':', ':53:') > 0 or WS_WKPL_FREQ_ID = 181) THEN 1 ELSE 0 END) AS caregiver, (CASE WHEN WS_WORK_INTERACTIONS_DY_ID =584 THEN 5 WHEN WS_WORK_INTERACTIONS_DY_ID =585 THEN 20 WHEN WS_WORK_INTERACTIONS_DY_ID =586 THEN 40 WHEN WS_WORK_INTERACTIONS_DY_ID =587 THEN 60 ELSE 0  END) AS interactions, (CASE WHEN instr(':' || CSS_PRE_EXISTING_COND_IDS || ':', ':549:') > 0 or instr(':' || CSS_PRE_EXISTING_COND_IDS || ':', ':550:') > 0 or instr(':' || CSS_PRE_EXISTING_COND_IDS || ':', ':551:') > 0 or instr(':' || CSS_PRE_EXISTING_COND_IDS || ':', ':552:') > 0 or instr(':' || CSS_PRE_EXISTING_COND_IDS || ':', ':561:') > 0 or instr(':' || CSS_PRE_EXISTING_COND_IDS || ':', ':562:') > 0 or instr(':' || CSS_PRE_EXISTING_COND_IDS || ':', ':563:') > 0 or instr(':' || CSS_PRE_EXISTING_COND_IDS || ':', ':2:') > 0 or instr(':' || CSS_PRE_EXISTING_COND_IDS || ':', ':1:') > 0 THEN 1 ELSE 0 END) AS comorbid_score, ((CASE WHEN WS_WKPL_SCL_DIS_MEAS_ID=265 THEN 1 ELSE 0 END) + (CASE WHEN WS_WKPL_CLEANING_ID=268 THEN 1 ELSE 0 END) + (CASE WHEN WS_WKPL_PPE_USAGE_ID=281 THEN 1 ELSE 0 END)) AS work_cleaning, (CASE WHEN (OCC_STUDENT_ID = 121 and OCC_RETURN_TO_SCHOOL_ID = 123) THEN 1 ELSE 0 END) AS school_exposure, (CASE WHEN (instr(':' || OCC_JOBS_IDS || ':', ':135:') + instr(':' || OCC_JOBS_IDS || ':', ':137:') + instr(':' || OCC_JOBS_IDS || ':', ':138:') + instr(':' || OCC_JOBS_IDS || ':', ':139:') + instr(':' || OCC_JOBS_IDS || ':', ':140:') + instr(':' || OCC_JOBS_IDS || ':', ':161:') + instr(':' || OCC_JOBS_IDS || ':', ':162:') + instr(':' || OCC_JOBS_IDS || ':', ':163:') + instr(':' || OCC_JOBS_IDS || ':', ':164:') + instr(':' || OCC_JOBS_IDS || ':', ':166:') + instr(':' || OCC_JOBS_IDS || ':', ':168:') + instr(':' || OCC_JOBS_IDS || ':', ':171:') + instr(':' || OCC_JOBS_IDS || ':', ':173:') + instr(':' || OCC_JOBS_IDS || ':', ':178:') + instr(':' || OCC_JOBS_IDS || ':', ':701:') + instr(':' || OCC_JOBS_IDS || ':', ':621:') + instr(':' || OCC_JOBS_IDS || ':', ':622:')) > 0 THEN 1 ELSE 0 END) AS high_risk_prof, (CASE WHEN CI_GATHERINGS_LAST_2W_ID = 341 THEN 1 ELSE 0 END) AS gatherings_last_2w_score, (CASE WHEN CI_HOME_VISITOR_FREQ_ID = 361 THEN 1 ELSE 0 END) AS home_visitor_freq_score FROM SURVEY_RESPONSES_PIVOT_MV srp) srp) srp"