Closed jesusitd closed 4 years ago
Calculated Columns to move to SQL:
Roundup Exposure Group 2
, Cesar) =>SQLRoundup Exposure Hours Group 3 Home
, Cesar) => SQLRoundup Exposure Hours Group 3 Work
, Cesar) => SQLCC @CesarITD
FIXED
CASE cases_cstm.case_type_c
WHEN 'Roundup'
THEN
CASE
WHEN (SELECT roundup_usage_hours_exposure_total) IS NULL THEN NULL
WHEN (SELECT roundup_usage_hours_exposure_total) <= 40 THEN "40 or less"
WHEN (SELECT roundup_usage_hours_exposure_total) <= 47 THEN "41-47"
WHEN (SELECT roundup_usage_hours_exposure_total) <= 72 THEN "48-72"
WHEN (SELECT roundup_usage_hours_exposure_total) <= 100 THEN "73-100"
ELSE (SELECT `roundup_usage_exposure_group`)
END
ELSE NULL
END AS `Roundup Exposure Group 2`,
FIXED
CASE cases_cstm.case_type_c
WHEN 'Roundup'
THEN
CASE
WHEN (SELECT roundup_usage_home_hours_total) IS NULL THEN 'No Home Exp.'
WHEN (SELECT roundup_usage_home_hours_total) = 0 THEN "No Home Exp."
WHEN (SELECT roundup_usage_home_hours_total) <= 72 THEN "72 or less"
WHEN (SELECT roundup_usage_home_hours_total) <= 500 THEN "73-500"
WHEN (SELECT roundup_usage_home_hours_total) > 500 THEN "500+"
ELSE 'No Home Exp.'
END
ELSE NULL
END AS `Roundup Exposure Hours Group 3 Home`,
FIXED
CASE cases_cstm.case_type_c
WHEN 'Roundup'
THEN
CASE
WHEN (SELECT roundup_usage_work_hours_total) IS NULL THEN 'No Work Exp.'
WHEN (SELECT roundup_usage_work_hours_total) = 0 THEN "No Work Exp."
WHEN (SELECT roundup_usage_work_hours_total) <= 72 THEN "72 or less"
WHEN (SELECT roundup_usage_work_hours_total) <= 500 THEN "73-500"
WHEN (SELECT roundup_usage_work_hours_total) > 500 THEN "500+"
ELSE 'No Work Exp.'
END
ELSE NULL
END AS `Roundup Exposure Hours Group 3 Work`,
FIXED
CASE cases_cstm.case_type_c
WHEN 'Roundup'
THEN
CASE
WHEN (SELECT `Roundup Cancer 1 Stage`) IS NULL THEN NULL
WHEN (SELECT `Roundup Cancer 1 Stage`) = "Stage 1" THEN "Stages 1-2"
WHEN (SELECT `Roundup Cancer 1 Stage`) = "Stage 2" THEN "Stages 1-2"
ELSE (SELECT `Roundup Cancer 1 Stage`)
END
ELSE NULL
END AS `Roundup Cancer 1 Stage Grouped`,
IF( cases.roundup_diagnosis1_date <> '' AND cases.roundup_diagnosis2_date >= cases.roundup_diagnosis1_date AND cases.roundup_injured_party_deceased_date >= cases.roundup_diagnosis2_date , TIMESTAMPDIFF(MONTH,cases.roundup_diagnosis2_date,cases.roundup_injured_party_deceased_date), null) AS `Roundup Deceased After Diagnosis 2 Months`,
CASE cases_cstm.case_type_c
WHEN 'Roundup'
THEN
CASE
WHEN (SELECT `Roundup Deceased After Diagnosis 2 Months`) IS NULL THEN NULL
WHEN (SELECT `Roundup Deceased After Diagnosis 2 Months`) <= 3 THEN "0-3 mo"
WHEN (SELECT `Roundup Deceased After Diagnosis 2 Months`) <= 6 THEN "4-6 mo"
WHEN (SELECT `Roundup Deceased After Diagnosis 2 Months`) <= 12 THEN "6-12 mo"
ELSE "1+ yrs"
END
ELSE NULL
END AS `Roundup Deceased After Diagnosis 2 Delay Months Group`,
CASE cases_cstm.case_type_c
WHEN 'Roundup'
THEN
CASE
WHEN (SELECT `Roundup Cancer 1 Age of Diagnosis`) > 0 AND NULLIF((SELECT `roundup_diagnosis1_cancer_type`), '') IS NOT NULL
THEN CASE WHEN (SELECT `Roundup Cancer 1 Age of Diagnosis`) <= 60 THEN "Clients Under 60" ELSE "Clients Over 60" END
WHEN (SELECT `Roundup Cancer 2 Age of Diagnosis`) > 0 AND NULLIF((SELECT `roundup_diagnosis2_cancer_type`), '') IS NOT NULL
THEN CASE WHEN (SELECT `Roundup Cancer 2 Age of Diagnosis`) <= 60 THEN "Clients Under 60" ELSE "Clients Over 60" END
ELSE NULL
END
ELSE NULL
END AS `Roundup NHL Age Group`,
CASE
WHEN cases_cstm.case_type_c = 'Roundup' THEN
CASE
WHEN (SELECT `Roundup Treatment 1 Successful Remission`) = "No" THEN "Active Cancer"
WHEN (SELECT `Roundup Treatment 1 Successful Remission`) = "Yes" THEN "Cancer in Remission"
ELSE "N/A"
END
ELSE NULL
END AS 'Roundup Treatment 1 Successful Remission Label',
Steps:
IF is Copy of AWKO (assumes it already has all the tabs, except for Campaigns Master):
IF NOT Copy of AWKO (Most reports actually)
IN ALL CASES:
Billable
measureAge of Diagnosis 1
and Age of Diagnosis 2
Verifying AWKO:
OK. Uploading
Down - Up Last -> Napoli Shkolnik Campaigns - Paralegal.pbix
Roundup 2 Roundup 3 Roundup 2nd Diagnosis ... all tabs