--GDO kaposi incidence code
with
--AGE CTE
AGE_DATA as (
select * from GDO.age)--STAGE CTE
, STAGE_DATA as (
select * from GDO.stage),
tumour_cohort_kaposi
as
(
select tumourid
, diagnosisyear
, age
from av2019.av_tumour_england
where SITE_ICD10R4_O2_3CHAR_FROM2013 = 'C46'
and cascade_inci_flag = 1
and diagnosisyear between 2013 and 2019
)
,
statisticofinterest_kaposi as
(select tumourid as tumourid_1, 1 as statisticofinterest_kaposi from tumour_cohort_kaposi),
tumour_cohort_linked_kaposi AS ( SELECT * FROM tumour_cohort_kaposi tc_kaposi
LEFT JOIN statisticofinterest_kaposi ON statisticofinterest_kaposi.tumourid_1 = tc_kaposi.tumourid
)
, extractpartition_kaposi as (
SELECT '2013' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR = '2013'
UNION ALL
SELECT '2014' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR = '2014'
UNION ALL
SELECT '2015' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR = '2015'
UNION ALL
SELECT '2016' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR = '2016'
UNION ALL
SELECT '2017' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR = '2017'
UNION ALL
SELECT '2018' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR = '2018'
UNION ALL
SELECT '2019' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR = '2019'
UNION ALL
SELECT '2013-2015' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015
UNION ALL
SELECT '2014-2016' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016
UNION ALL
SELECT '2015-2017' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017
UNION ALL
SELECT '2016-2018' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018
UNION ALL
SELECT '2017-2019' AS "Year",
SUM(statisticofinterest_kaposi) as statistic_of_interest
FROM tumour_cohort_linked_kaposi
WHERE DIAGNOSISYEAR BETWEEN 2017 AND 2019
) ,
--Geography lookup
lsoatoregionlookup as (
select distinct lsoa11_code as lsoa11,
case
when nhs_region_2021_name in ('East of England','Midlands') then 'Midlands and East of England'
when nhs_region_2021_name in ('North West', 'North East and Yorkshire') then 'North of England'
when nhs_region_2021_name in ('South West','South East') then 'South of England'
else nhs_region_2021_name
end as region
from av2019.at_geography_england
where substr(lsoa11_code,1,1) = 'E'
),lsoatoalllookup as(
select distinct lsoa11_code as lsoa11, 'All' as region
from av2019.at_geography_england
where substr(lsoa11_code,1,1) = 'E')
, geoglookup as (
select * from lsoatoregionlookup
union
select * from lsoatoalllookup)
--Sex lookup
, 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 lookup
--Select single years
,yearlookup as (
select n as baseyear
, to_char(n) as year
from
(select rownum n from dual
--Last year
connect by level <=2019
)
--First year
where n >=2013
UNION
select n as baseyear
,'2013-2015' as year
from
(select rownum n from dual
--Last year
connect by level <=2015
)
--First year
where n >=2013
UNION
select n as baseyear
,'2014-2016' as year
from
(select rownum n from dual
--Last year
connect by level <=2016
)
--First year
where n >=2014
UNION
select n as baseyear
,'2015-2017' as year
from
(select rownum n from dual
--Last year
connect by level <=2017
)
--First year
where n >=2015
UNION
select n as baseyear
,'2016-2018' as year
from
(select rownum n from dual
--Last year
connect by level <=2018
)
--First year
where n >=2016
UNION
select n as baseyear
,'2017-2019' as year
from
(select rownum n from dual
--Last year
connect by level <=2019
)
--First year
where n >=2017 )
--Age lookup
, agelookup as (
select distinct floor(AGE/5)+1 as baseage, 'All' as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, FIVEYEARAGE as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, TENYEARAGE as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_TO_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50_TO_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50_TO_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_70_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_50_70_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_50_70_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60_TO_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_50_70_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_50 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_30_TO_50 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_40 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_TO_40 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_50 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_40 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_35 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_40_TO_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_5_10_20_30_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_30_50_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_20_TO_90 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60_70_75_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_70_75_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_13_25_40_60_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_40_60_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_40_60 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_40_TO_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_5_THEN_10 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_60_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_40_TO_80 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_40_TO_90 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_70 as age from AGE_DATA
UNION
select distinct floor(AGE/5)+1 as baseage, SPLIT_25_60_TO_80 as age from AGE_DATA)
--Populations table
, population_table as (
select g.region, s.sex, y.year, a.age, sum(popcount) as popcount
from ons2019.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
),
kaposi as(
SELECT
'Kaposi Sarcoma' AS "Cancer Site",
"Year" AS "Year",
'All' AS "Tumour Type",
'All' AS "Tumour Type 2",
'All' AS "Tumour Type 3",
'Not split by Basis of Diagnosis' AS "Basis of Diagnosis",
'Not applicable' AS "Stage",
'Not applicable' AS "Stage Detail",
'Not applicable' AS "Stage Detail 2",
'All ages' AS "Age",
'All England' AS "Region",
'Persons' AS "Sex",
'Not applicable' AS "Grade",
'Not applicable' AS "Nodal Status",
statistic_of_interest as "Incidence",
pops.popcount as "Population"
from extractpartition_kaposi ep
left outer join population_table pops
on ep."Year" = pops.year
and pops.age = 'All' where pops.region ='All' and pops.sex='All'
--End of code for kaposi
) select * from kaposi