--GDO bone incidence code 
 with 
 --AGE CTE 
 AGE_DATA as ( 
 select * from GDO.age)--STAGE CTE 
 , STAGE_DATA as ( 
 select * from GDO.stage),
tumour_cohort_bone 

as 

(



select tumourid



, diagnosisyear

, age



from av2018.at_tumour_england



where site_icd10_o2_3char in ('C40','C41')



and cascade_inci_flag = 1



and diagnosisyear between 2013 and 2018



)



,
statisticofinterest_bone as 
(select tumourid, 1 as statisticofinterest_bone from tumour_cohort_bone),
tumour_cohort_linked_bone AS ( SELECT * FROM tumour_cohort_bone tc_bone
LEFT JOIN statisticofinterest_bone ON statisticofinterest_bone.tumourid = tc_bone.tumourid
)
 
 
 , extractpartition_bone as ( 

SELECT '2013' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR = '2013'

UNION ALL

SELECT '2014' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR = '2014'

UNION ALL

SELECT '2015' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR = '2015'

UNION ALL

SELECT '2016' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR = '2016'

UNION ALL

SELECT '2017' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR = '2017'

UNION ALL

SELECT '2018' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR = '2018'

UNION ALL

SELECT '2013-2015' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR BETWEEN 2013 AND 2015

UNION ALL

SELECT '2014-2016' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR BETWEEN 2014 AND 2016

UNION ALL

SELECT '2015-2017' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR BETWEEN 2015 AND 2017

UNION ALL

SELECT '2016-2018' AS "Year", 
SUM(statisticofinterest_bone) as statistic_of_interest
FROM tumour_cohort_linked_bone
WHERE DIAGNOSISYEAR BETWEEN 2016 AND 2018
 ) ,

--Geography lookup
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)
--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 <=2018
) 
--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 )
 --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)
 
--Populations table
, population_table as (
                          select g.region, s.sex, y.year, a.age, sum(popcount) as popcount 
                          from ons2018.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
),
 
bone as(
SELECT
'Bone cancer' 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",
'All stages' AS "Stage",
'Not split by Stage Detail' AS "Stage Detail",
'Not split by Stage Detail 2' AS "Stage Detail 2",
'All ages' AS "Age",
'All England' AS "Region",
'Persons' AS "Sex",
'Not split by grade' AS "Grade",
'Not split by nodal status' AS "Nodal Status",
statistic_of_interest as "Incidence",
pops.popcount  as "Population"
from extractpartition_bone 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 bone
 ) select * from bone