-- Lung, mesothelioma, and other thoracic cancer cohort for GDO

select t.tumourid
, t.diagnosisyear
, site.SITE_GROUP1
, site.SITE_GROUP2
, morph.MORPH_SPLIT_1
, morph.MORPH_SPLIT_2
, case 
    when t.site_icd10r4_o2_3char_from2013 = 'C45' and t.morph_icdo3rev2011= 9050 then 'Mesothelioma NOS'
    when t.site_icd10r4_o2_3char_from2013 = 'C45' and t.morph_icdo3rev2011= 9051 then 'Sarcomatoid'
    when t.site_icd10r4_o2_3char_from2013 = 'C45' and t.morph_icdo3rev2011= 9052 then 'Epithelioid'
    when t.site_icd10r4_o2_3char_from2013 = 'C45' and t.morph_icdo3rev2011= 9053 then 'Biphasic'
    else null
end as meso_morph
, t.age
, case
    when t.stage_best = '0' then 'X'
    else nvl(t.stage_best,'X')
end as stage_best
, case
when t.sex = 1 then 'Male'
when t.sex = 2 then 'Female'
end as gender
, case
    when t.basisofdiagnosis in (5,6,7) then 'Pathologically confirmed'
    else 'Not pathologically confirmed'
end as basisofdiagnosis
, case
    when atp.performancestatus is null or atp.performancestatus = '9' then '9'
    else atp.performancestatus
end as performancestatus
from AV2020.at_tumour_england t
left join AV2020.at_tumour_plus_england atp 
on t.tumourid = atp.tumourid
left join analysispollyjeffrey.site_lung site
on t.site_icd10r4_o2_from2013 = site.site_icd10r4_o2_from2013
left join analysispollyjeffrey.morph_lung morph
on nvl(t.morph_icdo3rev2011,8000) = morph.morph_icdo3rev2011
-- C-codes for lung, bronchus, trachea, thymus, heart, other, and mesothelioma
where site_icd10r4_o2_3char_from2013 in ('C33','C34','C37','C38','C39','C45')
-- standard filters from CASSOP#1
and t.statusofregistration like 'F'-- finalised cases only
and t.ctry_code like 'E' -- English cases only
and t.dedup_flag = 1 -- Excluding duplicates
and t.age between 0 and 200 -- Sensible age
and t.sex in (1,2)
-- year filter
and t.diagnosisyear between 2013 and 2020
;