-- Tables required for this script are
-- 
-- ANALYSISCHARLIETURNER.stages
-- ANALYSISCHARLIETURNER.ages
-- ANALYSISCHARLIETURNER.testicular_morph

WITH tumour_cohort as (

select tumourid
, diagnosisyear
, nvl(morph_icd10_o2,'8000') as morph_icd10_o2
, nvl(stage_best,'X') as stage_best
, age
from av2017.av_tumour_england
-- C-code for testicular or D-code for testicular
-- (D-codes included after discussion with Brian Rous)
where (site_icd10_o2 like 'C62%'
or site_icd10_o2 like 'D29%')
-- standard filters from CASSOP#1
and statusofregistration like 'F'-- finalised cases only
and ctry_code like 'E' -- English cases only
and dedup_flag=1 -- Excluding duplicates
and age between 0 and 200 -- Sensible age
and sex = 1 -- Sex is 'correct' for this site
-- year filter
and diagnosisyear between 2013 and 2017)

,
statisticofinterest as
(select tumourid, 1 as statisticofinterest from tumour_cohort),
tumour_cohort_linked AS ( SELECT * FROM tumour_cohort tc
LEFT JOIN statisticofinterest si ON si.tumourid = tc.tumourid
LEFT JOIN ANALYSISCHARLIETURNER.stages ON ANALYSISCHARLIETURNER.stages.STAGE_BEST = tc.STAGE_BEST
LEFT JOIN ANALYSISCHARLIETURNER.ages ON ANALYSISCHARLIETURNER.ages.AGE = tc.AGE
LEFT JOIN ANALYSISCHARLIETURNER.testicular_morph t_morph ON t_morph.MORPH_ICD10_O2 = tc.MORPH_ICD10_O2)

, extractpartitions as
(


SELECT 'All' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked


UNION ALL

SELECT '2013' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2013' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2013' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2013' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2013' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2014' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2014' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2014' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2014' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2014' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2015' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2015' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2015' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2016' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2016' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2016' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2017' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017'

UNION ALL

SELECT '2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2017' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR = '2017' 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2013-2015' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2013-2015' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2014-2016' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2014-2016' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2015-2017' AS "Year",
 'All' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017

UNION ALL

SELECT '2015-2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Non-seminoma'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 00-29'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30 = 'Age 30+'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Non-seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Non-seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Other' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Other'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'All' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Seminoma'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 00-29' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 00-29'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 30-39' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 30-39'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 40-49' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 40-49'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 1' AS "Stage",
 'Age 50+' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 1' 
AND SPLIT_30_TO_50 = 'Age 50+'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage 2-3' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage 2-3'

UNION ALL

SELECT '2015-2017' AS "Year",
 'Seminoma' AS "Tumour Type",
 'Stage unknown' AS "Stage",
 'All' AS "Age",
SUM(statisticofinterest) as Incidence
FROM tumour_cohort_linked
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017 
AND TUMOUR_TYPE = 'Seminoma' 
AND STAGE_SPLIT1_TEST = 'Stage unknown'
)

, lsoatoregionlookup
as
(
select distinct lsoa11, nhser17nm as Region from nspl_201805
left outer join ANALYSISNCR.CCG18_NHSER17_18_LKP_DATALAKE nr
on nspl_201805.ccg = nr.ccgapr18cd
where substr(lsoa11,1,1) = 'E'
)
, lsoatoalllookup
as
(
select distinct lsoa11, 'All' as Region from nspl_201805
where substr(lsoa11,1,1) = 'E'
)
, geoglookup as 
(select * from lsoatoregionlookup
union
select * from lsoatoalllookup)

/*
reminding myself how dual works:
select rownum from dual;

select rownum  from dual 
 -- LAST YEAR NEEDED
  connect by level <= 3;
*/

,sexlookup
as
(
select 1 as basesex, '1' as sex from dual 
union
select 2 as basesex, '2' as sex from dual 
union
select 1 as basesex, 'All' as sex from dual 
union
select 2 as basesex, 'All' as sex from dual
)

-- year look up
-- This is hacky, there must be a better way? 
-- But it does produce a look up table for 2013-2017
-- and also the three year rolling groups for 2013-2017

,yearlookup
as
(
select n as baseyear
, to_char(n) as year
from
 ( select rownum n  from dual 
 -- LAST YEAR NEEDED
  connect by level <= 2017
 )
 -- FIRST YEAR NEEDED
where n >= 2013
UNION
select n as baseyear
, '2013-2015' as year
from
 ( select rownum n  from dual 
 -- LAST YEAR NEEDED
  connect by level <= 2015
 )
 -- FIRST YEAR NEEDED
where n >= 2013
UNION
select n as baseyear
, '2014-2016' as year
from
 ( select rownum n  from dual 
 -- LAST YEAR NEEDED
  connect by level <= 2016
 )
 -- FIRST YEAR NEEDED
where n >= 2014
UNION
select n as baseyear
, '2015-2017' as year
from
 ( select rownum n  from dual 
 -- LAST YEAR NEEDED
  connect by level <= 2017
 )
 -- FIRST YEAR NEEDED
where n >= 2015
)

-- Making age lookup.

--select * from analysischarlieturner.ages;

-- We don't have a systematic 'we definitely used this age partition'
-- So we should assume any age partition in the age table could be being used.
-- There are currently 30 age partitions.  
-- This is a bit tedious.  

-- You can at least get a list of them:
/*
select COLUMN_NAME from ALL_TAB_COLUMNS
where owner = 'ANALYSISCHARLIETURNER'
and TABLE_NAME = 'AGES'
order by COLUMN_NAME;
*/
-- If I had grip, I would write some R to automate creating this table
-- Instead I did it in Excel
, agelookup
as
(
select distinct floor(AGES.AGE/5)+1 as baseage, 'All' as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_20_30_50_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_20_40 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_20_50_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_20_50_70_80 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_20_TO_40 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_25 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_25_50 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_30 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_30_50 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_30_50_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_30_50_70_80 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_30_TO_50 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_30_TO_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_35 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_40 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_40_TO_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_50 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_50_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_50_70_80 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_50_TO_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_50_TO_80 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_5_10_20_30_50_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_60_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_60_70_75_80 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_60_TO_80 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_70 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_70_75_80 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_70_80 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, SPLIT_80 as age from analysischarlieturner.ages
UNION
select distinct floor(AGES.AGE/5)+1 as baseage, TENYEARAGE as age from analysischarlieturner.ages)

-- So, I have a region lookup, a sex lookup, a year lookup, and an age lookup.

-- And some populations!  Yay.  Now just join them all together.

-- We have no joins for the Welsh LSOAs and for the years before 2013. 
-- But that is what we wanted
-- All ages and sexs have joined to something.  


, population_table as
(
select g.region, s.sex, y.year, a.age, sum(popcount) as popcount from ons2017.populations_normalised POPS
left outer join geoglookup g 
on pops.lsoa11 = g.lsoa11
left outer join sexlookup s
on pops.sex = s.basesex
left outer join yearlookup y
on pops.year = y.baseyear 
left outer join agelookup a
on pops.quinaryagegroupint = a.baseage
group by g.region, s.sex, y.year, a.age
)



select 
'Testicular tumours including post-pubertal teratomas' as "Cancer Site" 
,"Year" as "Year"	
,"Tumour Type" as "Tumour Type"	
, "Stage" as "Stage" 
, case when "Age" = 'All' then 'All ages' else "Age" end as "Age"
-- Region doesn't work, you need "Region" now.  Odd odd odd
, 'All England' as "Region"
, 'Male' as "Sex"
, 'Not applicable' as "Grade" 
, 'Not split by nodal status' as "Nodal Status"
, Incidence as "Incidence"
, pops.popcount  as "Population"
from extractpartitions ep
left outer join population_table pops
on ep."Year" = pops.year
and ep."Age" = pops.age
where pops.sex = '1'
and pops.region = 'All'
;