/* Updated on 29th August 2018 by Charlie Turner To remove all patients whose sex was Male from the Ovarian cohort table(s). */ /* Collecting two groups of ovaries: the C-codes (c_ovaries) and the D-codes (d_ovaries) */ CREATE TABLE c_ovaries as (select tumourid, diagnosisyear, site_icd10_o2, morph_icd10_o2, age, stage_best from av2016.av_tumour@CASREF01 --- C codes only, ovary filter where (SITE_ICD10_O2_3CHAR in ('C56', 'C57') OR -- for C48, restrict to women and remove sarcomas (follows CAS-SOP 4.2 and Becca Elleray's code) (SITE_ICD10_O2_3CHAR in ('C48') and (morph_icd10_o2 not in (8693, 8800, 8801, 8802, 8803, 8804, 8805, 8806, 8810, 8963, 8990, 8991, 9040, 9041, 9042, 9043, 9044, 9490, 9500) and (morph_icd10_o2 not between 8811 and 8921) and (morph_icd10_o2 not between 9120 and 9373) and (morph_icd10_o2 not between 9530 and 9582) and sex=2))) -- standard filters and statusofregistration like 'F' and ctry_code like 'E' and dedup_flag=1 -- Excluding duplicates and age between 0 and 200 -- Sensible age and sex = 2 -- Sex is female for ovarian -- diagnosis year in initial range and diagnosisyear between 2013 and 2016); CREATE TABLE d_ovaries AS ( select tumourid, diagnosisyear, site_icd10_o2, morph_icd10_o2, age, stage_best from av2016.av_tumour@CASREF01 --- D codes only ovaries where SITE_ICD10_O2 in ('D391') -- standard filters and statusofregistration like 'F' and ctry_code like 'E' and dedup_flag=1 -- Excluding duplicates and age between 0 and 200 -- Sensible age and sex = 2 -- Sex is female for ovarian -- diagnosis year in our initial range and diagnosisyear between 2013 and 2016); /* Now to add the lowest level category_1 to these tables Level 1 categories (morph_cat_1) are Non-specific site Borderline Serous carcinoma Mucinous carcinoma Endometrioid carcinoma Clear Cell carcinoma Other classified malignant epithelial-stromal tumours Unclassified carcinoma Sex cord-stromal and germ cell Miscellaneous and unspecified Will need to do this separately for C and D codes. Let's do D codes first, that's simpler. */ -- join the morph_categories on the morphology code from the dcode tumour type table create table d_ova_with_type as select d_ovaries.*, ty.morph_cat_1, ty.morph_cat_2, ty.morph_cat_3, ty.MORPH_CAT_4 from d_ovaries left join ANALYSISCHARLIETURNER.DCODE_OVA_TUM_TYPES ty on d_ovaries.morph_icd10_o2 = ty.MORPH_ICD10_O2 ; /* C-codes are more complex I'm going to do several steps whose order matters because they'll overwrite one another. 1. Join on CCODE_OVA_TUM_TYPES and morphologies 2. Overwrite C577, C578, C579 */ -- join the morph_categories on the morphology code create table c_ova_with_type as select c_ovaries.*, ty.morph_cat_1, ty.morph_cat_2, ty.morph_cat_3, ty.MORPH_CAT_4 from c_ovaries left join ANALYSISCHARLIETURNER.CCODE_OVA_TUM_TYPES ty on ty.MORPH_ICD10_O2 = c_ovaries.MORPH_ICD10_O2; --overwriting for non-specific C-codes update c_ova_with_type set morph_cat_1 = 'Non-specific site', morph_cat_2 = 'Non-specific site', morph_cat_3 = 'Non-specific site', morph_cat_4 = 'Non-specific site' where site_icd10_o2 in ('C577','C578','C579'); -- put the c-code table and the d-code table together CREATE TABLE ovarian_all as select * from c_ova_with_type union all select * from d_ova_with_type ; --- Tidy up drop table c_ovaries; drop table d_ovaries; drop table c_ova_with_type; drop table d_ova_with_type;