Test highlight

Hello, my name is Felix Möller.

-- Created by: Maja Lievre (2 October 2020)
-- Purpose: Split out UNION in COMBINED_WEEK_SEX_AGEGROUP to prevent timeouts
-- 20201024 edited (Maja Lievre): added new fields from V2
-- 20201024 edited (Maja Lievre): weekly reporting all combined iso code corrected
-- 20201118 edited (Maja Lievre): removed records if agegroup is null from byage datasets
-- 20210119 edited (Maja Lievre): removed records with both cases and deaths missing from datasets
-- 20210121 edited (Maja Lievre): added with (nolock)
-- 20210205 edited (Maja Lievre): filled PATIENTS_TESTED with data from TESTED_PCR if TESTED is missing
-- 20210211 edited (Maja Lievre): renamed CASES_CONFIRMED to CASES
-- 20210621 edited (Arpad Fodor - Avanade): removed with (nolock) - not supported in Synapse

CREATE VIEW [ncov_c].[WEEKLY_V2_DATA_SEX_AGE_ALL]
AS

--weekly reporting all combined
SELECT 'WEEKLY_V2' AS REPORTING_TYPE,
        C.WHO_REGION AS WHO_REGION,
        C.ADM0_VIZ_NAME AS COUNTRY_NAME,
        c.ISO3 AS COUNTRY_CODE,
        D.ISO_YEAR,
        D.ISO_WEEK,
        'All' AS SEX,
        'All' AS AGEGROUP,
        0 AS AGEGROUP_NUM,
        NEW_CONF_PROB_CASES AS CASES,
        NEW_CONF_PROB_DEATHS AS CASES_DEATHS,
        NULL AS CASES_RECOVERED,
        NEW_CONF_PROB_HOSPITALIZED AS CASES_HOSPITALISED,
        NEW_CONF_PROB_DISCHARGED AS CASES_DISCHARGED,
        COALESCE (TESTED, TESTED_PCR) AS PATIENTS_TESTED,
        NULL AS CASES_ICU,
        NULL AS CASES_VENT,
        NEW_CONF_PROB_HCW_CASES AS CASES_HCW,
        NEW_CONF_PROB_HCW_DEATHS AS CASES_DEATHS_HCW,
        NEW_CONF_CASES AS CASES_CONFIRMED,
        NEW_PROB_CASES AS CASES_PROBABLE,
        NEW_CONF_DEATHS AS CASES_DEATHS_CONFIRMED,
        NEW_PROB_DEATHS AS CASES_DEATHS_PROBABLE,
        TESTED_PCR AS PATIENTS_TESTED_PCR
--      DATEDIFF(DAY, d.Sys_CommitDateUtc, GETUTCDATE()) COMMIT_DAYS_AGO
FROM NCOV.FACT_WEEK_AGG_V2 D 
LEFT JOIN NCOV.REF_COUNTRY C ON D.ISO3=C.ADM0_VIZ_NAME

--weekly by sex, all ages
 union
 SELECT 'WEEKLY_V2' AS REPORTING_TYPE,
        C.WHO_REGION AS WHO_REGION,
        C.ADM0_VIZ_NAME AS COUNTRY_NAME,
        D.ISO_3_CODE AS COUNTRY_CODE,
        REPORT_YEAR AS ISO_YEAR,
        REPORT_WEEK AS ISO_WEEK,
        UPPER(SUBSTRING(SEX_CODE,1,1))+LOWER(SUBSTRING(SEX_CODE,2,LEN(SEX_CODE)-1)) AS SEX,
        'All' AS AGEGROUP,
        0 AS AGEGROUP_NUM,
        SUM(NEW_CONF_PROB_CASES) AS CASES,
        SUM(NEW_CONF_PROB_DEATHS) AS CASES_DEATHS,
        NULL AS CASES_RECOVERED,
        NULL AS CASES_HOSPITALISED,
        NULL AS CASES_DISCHARGED,
        NULL AS PATIENTS_TESTED,
        NULL AS CASES_ICU,
        NULL AS CASES_VENT,
        NULL AS CASES_HCW,
        NULL AS CASES_DEATHS_HCW,
        NULL AS CASES_CONFIRMED,
        NULL AS CASES_PROBABLE,
        NULL AS CASES_DEATHS_CONFIRMED,
        NULL AS CASES_DEATHS_PROBABLE,
        NULL AS PATIENTS_TESTED_PCR
--      DATEDIFF(DAY, d.Sys_CommitDateUtc, GETUTCDATE()) COMMIT_DAYS_AGO

FROM NCOV_C.V_FACT_CASE_WEEK_ADMIN0_AGESEX_V2 D 
LEFT JOIN NCOV.REF_COUNTRY C ON D.ISO_3_CODE=C.ISO3
where d.NEW_CONF_PROB_CASES is not null or d.NEW_CONF_PROB_DEATHS is not null
GROUP BY C.WHO_REGION, C.ADM0_VIZ_NAME, D.ISO_3_CODE, D.REPORT_YEAR, D.REPORT_WEEK, D.SEX_CODE

--weekly reporting sex combined, by age
union
SELECT 'WEEKLY_V2' AS REPORTING_TYPE,
        C.WHO_REGION AS WHO_REGION,
        C.ADM0_VIZ_NAME AS COUNTRY_NAME,
        D.ISO_3_CODE AS COUNTRY_CODE,
        REPORT_YEAR AS ISO_YEAR,
        REPORT_WEEK AS ISO_WEEK,
        'All' AS SEX,
        ag.label AS AGEGROUP,
        ag.code_num AS AGEGROUP_NUM,
        SUM(NEW_CONF_PROB_CASES) AS CASES,
        SUM(NEW_CONF_PROB_DEATHS) AS CASES_DEATHS,
        NULL AS CASES_RECOVERED,
        NULL AS CASES_HOSPITALISED,
        NULL AS CASES_DISCHARGED,
        NULL AS PATIENTS_TESTED,
        NULL AS CASES_ICU,
        NULL AS CASES_VENT,
        NULL AS CASES_HCW,
        NULL AS CASES_DEATHS_HCW,
        NULL AS CASES_CONFIRMED,
        NULL AS CASES_PROBABLE,
        NULL AS CASES_DEATHS_CONFIRMED,
        NULL AS CASES_DEATHS_PROBABLE,
        NULL AS PATIENTS_TESTED_PCR
--      DATEDIFF(DAY, d.Sys_CommitDateUtc, GETUTCDATE()) COMMIT_DAYS_AGO

FROM NCOV_C.V_FACT_CASE_WEEK_ADMIN0_AGESEX_V2 D 
LEFT JOIN NCOV.REF_COUNTRY C ON D.ISO_3_CODE=C.ISO3
left join ncov.ref_agegroup ag on d.AGEGROUP_CODE=ag.code
where agegroup_code is not null and (d.NEW_CONF_PROB_CASES is not null or d.NEW_CONF_PROB_DEATHS is not null)
GROUP BY C.WHO_REGION, C.ADM0_VIZ_NAME, D.ISO_3_CODE, D.REPORT_YEAR, D.REPORT_WEEK, ag.label, ag.code_num

--weekly reporting by sex, by age
union
SELECT 'WEEKLY_V2' AS REPORTING_TYPE,
        C.WHO_REGION AS WHO_REGION,
        C.ADM0_VIZ_NAME AS COUNTRY_NAME,
        D.ISO_3_CODE AS COUNTRY_CODE,
        REPORT_YEAR AS ISO_YEAR,
        REPORT_WEEK AS ISO_WEEK,
        UPPER(SUBSTRING(SEX_CODE,1,1))+LOWER(SUBSTRING(SEX_CODE,2,LEN(SEX_CODE)-1)) AS SEX,
        ag.label AS AGEGROUP,
        ag.code_num AS AGEGROUP_NUM,
        NEW_CONF_PROB_CASES AS CASES,
        NEW_CONF_PROB_DEATHS AS CASES_DEATHS,
        NULL AS CASES_RECOVERED,
        NULL AS CASES_HOSPITALISED,
        NULL AS CASES_DISCHARGED,
        NULL AS PATIENTS_TESTED,
        NULL AS CASES_ICU,
        NULL AS CASES_VENT,
        NULL AS CASES_HCW,
        NULL AS CASES_DEATHS_HCW,
        NULL AS CASES_CONFIRMED,
        NULL AS CASES_PROBABLE,
        NULL AS CASES_DEATHS_CONFIRMED,
        NULL AS CASES_DEATHS_PROBABLE,
        NULL AS PATIENTS_TESTED_PCR
--      DATEDIFF(DAY, d.Sys_CommitDateUtc, GETUTCDATE()) COMMIT_DAYS_AGO

FROM NCOV_C.V_FACT_CASE_WEEK_ADMIN0_AGESEX_V2 D 
LEFT JOIN NCOV.REF_COUNTRY C ON D.ISO_3_CODE=C.ISO3
left join ncov.ref_agegroup ag on d.AGEGROUP_CODE=ag.code
where agegroup_code is not null and (d.NEW_CONF_PROB_CASES is not null or d.NEW_CONF_PROB_DEATHS is not null)

GO