· 9 years ago · Nov 16, 2016, 08:12 AM
1DROP TABLE IF EXISTS temp_main;
2
3SELECT patient_id,
4 progression_track_id,
5 progression_group_id,
6 age_at_diagnosis,
7 stage,
8 date_of_diagnosis INTO TEMP TABLE temp_main
9FROM (SELECT d.patient_id,
10 d.stage,
11 d.progression_track_id,
12 d.progression_group_id,
13 d.date_of_diagnosis,
14 DATEDIFF(YEAR,p.date_of_birth,d.date_of_diagnosis) AS age_at_diagnosis
15 FROM v2.diagnosis_colon AS d
16 LEFT JOIN v2.phi AS p ON d.patient_id = p.patient_id)
17WHERE age_at_diagnosis <= 79
18-- AND DATEDIFF(year,measuremnet_end_date, date_of_diagnosis) <= 1
19AND age_at_diagnosis >= 18;
20
21DROP TABLE IF EXISTS temp_gr;
22
23SELECT progression_group_id,
24 MIN(date_of_diagnosis) AS min_dod INTO temp_gr
25FROM temp_main
26GROUP BY progression_group_id;
27
28DROP TABLE IF EXISTS temp_hist;
29
30SELECT patient_id,
31 cancer_lymphoma INTO TEMP TABLE temp_hist
32FROM v2.patient_history AS h
33WHERE h.cancer_lymphoma <> TRUE
34AND h.cancer_leukemia <> TRUE
35AND h.cancer_solid_tumor <> TRUE
36AND h.metastatic_solid_tumor_breast <> TRUE
37AND h.metastatic_solid_tumor_colon <> TRUE
38AND h.metastatic_solid_tumor_prostate <> TRUE
39AND h.metastatic_solid_tumor_lung <> TRUE
40AND h.metastatic_solid_tumor_melanoma <> TRUE
41AND h.metastatic_solid_tumor_other <> TRUE;
42
43DROP TABLE IF EXISTS temp_surgery;
44
45SELECT patient_id,
46 progression_group_id,
47 progression_track_id INTO TEMP TABLE temp_surgery
48FROM v2.intervention
49WHERE TYPE = 'surgery'
50AND start_date IS NOT NULL;
51
52DROP TABLE IF EXISTS temp_stage1;
53
54SELECT progression_track_id INTO TEMP TABLE temp_stage1
55FROM v2.diagnosis_colon
56WHERE attributes_histology NOT IN ('not_assessed','not_reported')
57AND BTRIM(stage) NOT IN ('No Match','0','Insufficient Data')
58AND stage IS NOT NULL;
59
60DROP TABLE IF EXISTS temp_stage2;
61
62SELECT progression_track_id INTO TEMP TABLE temp_stage2
63FROM v2.diagnosis_colon
64WHERE (BTRIM(n) NOT IN ('N0','NX') AND n IS NOT NULL)
65OR staging_pathological_regional_lymph_nodes_num_positive_value >= 1
66OR stage IN ('IIIA','IIIB','IIIC');
67
68DROP TABLE IF EXISTS denominator;
69
70SELECT x.progression_track_id,
71 x.progression_group_id,
72 x.patient_id,
73 x.stage,
74 g.min_dod AS date_of_diagnosis,
75 x.age_at_diagnosis,
76 TRUE AS denominator_status INTO TEMP TABLE denominator
77FROM temp_main AS x
78 JOIN temp_stage1 AS a ON x.progression_track_id = a.progression_track_id
79 JOIN temp_stage2 AS b ON x.progression_track_id = b.progression_track_id
80 JOIN temp_gr AS g ON x.progression_group_id = g.progression_group_id;
81
82DROP TABLE IF EXISTS exclusion;
83
84SELECT d.progression_track_id,
85 d.progression_group_id,
86 d.patient_id,
87 d.stage,
88 d.m,
89 o.response_type,
90 o.date_of_outcome,
91 d.date_of_diagnosis,
92 DATEDIFF(DAY,o.date_of_outcome,d.date_of_diagnosis),
93 TRUE AS exclusion_status INTO TEMP TABLE exclusion
94FROM v2.diagnosis_colon AS d
95 JOIN v2.outcome AS o ON o.progression_track_id = d.progression_track_id
96WHERE d.m IN ('M1','M1a,M1','M1b,M1b,M1','M1b,M1a,M1','M1b,M1')
97OR d.stage IN ('IV','IVA','IVB')
98OR d.staging_pathological_tumor_properties_tis IS TRUE
99-- OR (Clinical_trial_flag = TRUE AND new_regimen.start_date <= measurement_period_end_date)
100OR (o.response_type = 5 AND DATEDIFF(DAY,o.date_of_outcome,d.date_of_diagnosis) <= 120);
101
102/*DROP TABLE IF EXISTS denominator;
103
104CREATE TEMP TABLE denominator
105AS
106(SELECT progression_track_id,
107 progression_group_id,
108 patient_id,
109 stage,
110 date_of_diagnosis
111FROM denominator_temp
112MINUS
113SELECT progression_track_id,
114 progression_group_id,
115 patient_id,
116 stage,
117 date_of_diagnosis
118FROM exclusion);
119*/
120DROP TABLE IF EXISTS num_s1;
121
122SELECT d.progression_group_id,
123 d.progression_track_id,
124 d.date_of_diagnosis,
125 d.stage,
126 g.delivered_delivery_day,
127 n.drug_type,
128 DATEDIFF(DAY,d.date_of_diagnosis,g.delivered_delivery_day) AS delivery_delta INTO TEMP TABLE num_s1
129FROM (SELECT progression_track_id,
130 MIN(delivered_delivery_day) AS delivered_delivery_day
131 FROM v2.cycle_delivery
132 GROUP BY progression_track_id) AS g
133 JOIN v2.diagnosis_colon AS d ON g.progression_track_id = d.progression_track_id
134 JOIN v2.new_drug AS n ON n.progression_track_id = d.progression_track_id
135WHERE n.drug_type = 'chemotherapy'
136ORDER BY d.progression_group_id;
137
138DROP TABLE IF EXISTS num_s2;
139
140SELECT d.progression_group_id,
141 d.progression_track_id,
142 d.date_of_diagnosis,
143 d.stage,
144 i.type_declined,
145 DATEDIFF(DAY,d.date_of_diagnosis,i.date_declined) declined_delta INTO TEMP TABLE num_s2
146FROM v2.intervention AS i
147 JOIN v2.diagnosis_colon AS d ON i.progression_track_id = d.progression_track_id
148WHERE i.type_declined = 'chemotherapy'
149AND DATEDIFF(DAY,i.date_declined,d.date_of_diagnosis) <= 120;
150
151DROP TABLE IF EXISTS numerator;
152
153CREATE TEMP TABLE numerator
154AS
155(SELECT x.progression_group_id,
156 x.progression_track_id,
157 x.date_of_diagnosis,
158 x.stage,
159 x.drug_type,
160 y.type_declined,
161 y.declined_delta,
162 x.delivery_delta,
163 x.delivered_delivery_day,
164 TRUE AS numerator_status
165FROM num_s1 AS x
166 FULL JOIN num_s2 AS y ON x.progression_track_id = y.progression_track_id);
167
168SELECT t.progression_track_id,
169 t.patient_id,
170 t.progression_group_id,
171 CASE
172 WHEN numerator_status IS TRUE THEN TRUE
173 ELSE FALSE
174 END AS numerator,
175 CASE
176 WHEN exclusion_status IS TRUE THEN TRUE
177 ELSE FALSE
178 END AS exclusion,
179 CASE
180 WHEN denominator_status IS TRUE THEN TRUE
181 ELSE FALSE
182 END AS denominator,
183 CASE WHEN numerator_status IS TRUE
184 AND exclusion_status IS FALSE
185 AND denominator_status IS TRUE
186 THEN TRUE
187 ELSE FALSE
188 END AS winner
189FROM v2.diagnosis_colon AS t
190 LEFT JOIN numerator AS n ON t.progression_track_id = n.progression_track_id
191 LEFT JOIN exclusion AS e ON t.progression_track_id = e.progression_track_id
192 LEFT JOIN denominator AS d ON t.progression_track_id = d.progression_track_id
193 ORDER BY progression_group_id;