--Prostate tumour cohort

select t.tumourid
, t.diagnosisyear 
, t.age
, case 
-- If PIs say it isn't stageable, it's got no stage, even if it clearly has a stage in the box
when e.stage_pi_2206 = 'N' then 'Stage unknown'
-- These 7 lines fix the stage 4 T4, N?, M0 case - in pre TNM 8, this is ambigious (can be node negative or node positive)
-- And so goes in the 'unknown' group
-- In TNM 8, all stage 4s are either M1 or N1, so can go in the node positive group
-- Assume not TNM 8 if stage system unknown and diagnosis year before 2018
when (e.stage_best_system_2206 in ('UICC 5','UICC 6', 'UICC 7','AJCC 7') 
                or (e.stage_best_system_2206 is null and t.diagnosisyear >= 2013 and t.diagnosisyear <=2017) ) 
and substr(e.stage_best_2206,1,1) = '4'
and (substr(t.t_best,1,1) = '4' or t.t_best is null)
and substr(t.m_best,1,1) = '0' 
and (substr(t.n_best,1,1) = 'X' or n_best is null)
then 'Stage unknown'
when substr(e.stage_best_2206,1,1) in ('1','2') then 'Stage localised'
when substr(e.stage_best_2206,1,1) = '3' then 'Stage locally advanced'
when substr(e.stage_best_2206,1,1) = '4' and substr(m_best,1,1) = '0' then 'Stage locally advanced'
when substr(e.stage_best_2206,1,1) = '4' and substr(m_best,1,1) = '1' then 'Stage metastatic'
else 'Stage unknown' end as stage_partition
, case 
-- If PIs say it isn't stageable, it's got no stage, even if it clearly has a stage in the box
when e.stage_pi_2206 = 'N' then '? N/A'
-- Same fix as for overall stage partition for the stage 4 T4, N?, M0 pre TNM 8 cases
-- They go in overall 'unknown' and so do not get broken down by nodal status 
-- as they are not in the locally advanced group
when (e.stage_best_system_2206 in ('UICC 5','UICC 6', 'UICC 7','AJCC 7') 
                or (e.stage_best_system_2206 is null and t.diagnosisyear >= 2013 and t.diagnosisyear <=2017) ) 
and substr(e.stage_best_2206,1,1) = '4'
and (substr(t.t_best,1,1) = '4' or t.t_best is null)
and substr(t.m_best,1,1) = '0' 
and (substr(t.n_best,1,1) = 'X' or t.n_best is null)
then '? N/A'
when substr(e.stage_best_2206,1,1) = '3'  then 'Node Negative'
when substr(e.stage_best_2206,1,1) = '4' and substr(t.m_best,1,1) = '0' then 'Node Positive'
else '? N/A' end as node_status
, case
when gleason_primary = 3 and gleason_secondary = 3 then 'Grade less than 7'
when gleason_primary = 3 and gleason_secondary = 4 then 'Grade 3+4'
when gleason_primary = 4 and gleason_secondary = 3 then 'Grade 4+3'
when gleason_primary = 3 and gleason_secondary = 5 then 'Grade more than 7'
when gleason_primary = 4 and gleason_secondary in (4, 5) then 'Grade more than 7'
when gleason_primary = 5 and gleason_secondary in (3,4, 5) then 'Grade more than 7'
else 'Grade unknown'
end as grades_split
    
, case
when gleason_primary = 3 and gleason_secondary = 3 then 'Grade known'
when gleason_primary = 3 and gleason_secondary = 4 then 'Grade known'
when gleason_primary = 4 and gleason_secondary = 3 then 'Grade known'
when gleason_primary = 3 and gleason_secondary = 5 then 'Grade known'
when gleason_primary = 4 and gleason_secondary in (4, 5) then 'Grade known'
when gleason_primary = 5 and gleason_secondary in (3,4, 5) then 'Grade known'
else 'Grade unknown'
end as grades_split_met

from av2020.av_tumour_england t
left join av2020.av_tumour_experimental_england e on t.tumourid=e.tumourid
    
where t.diagnosisyear between '2013' and '2020'
and t.site_icd10r4_o2_3char_from2013 = 'C61' 
and t.statusofregistration = 'F'
and t.ctry_code = 'E'
and t.sex in ('1')
-- age row is pointless for this cohort
and t.age between 0 and 200 
and t.dedup_flag = 1

;