· 9 years ago · Nov 11, 2016, 08:34 AM
1/* this makes all of the fact tables for patient, track, pgroup, and cancer subtypes.
2it also calcualtes followup times (using event), classification (grades 0 to 4), and track_delta
3TO DO as of Trump+1:
41.) make events not rely on v2 and
52.) make it so it checks that tables are not empty
63.) see if analytics likes it? */
7
8BEGIN TRANSACTION;
9
10DROP TABLE IF EXISTS fact.ecog;
11
12CREATE TABLE fact.ecog
13AS
14(SELECT
15 id AS ecog_id,
16 entity_id,
17 patient_id,
18 DATE AS date_of_ecog,
19 ecog AS ecog_status
20FROM v2.ecog);
21
22GRANT ALL
23 ON TABLE fact.ecog
24 TO GROUP cal_reader;
25
26DROP TABLE IF EXISTS fact.outcome;
27
28CREATE TABLE fact.outcome
29AS
30(SELECT id AS outcome_id,
31 entity_id,
32 patient_id,
33 progression_group_id AS group_id,
34 progression_track_id AS track_id,
35 date_of_outcome,
36 response_type AS response,
37 response_description AS description
38FROM v2.outcome);
39
40GRANT ALL
41 ON TABLE fact.outcome
42 TO GROUP cal_reader;
43
44DROP TABLE IF EXISTS mortality;
45
46SELECT patient_id,
47 TRUE AS mortality INTO TEMP TABLE mortality
48FROM fact.outcome
49WHERE response = '5'
50AND BTRIM(date_of_outcome) IS NOT NULL
51UNION
52SELECT patient_id,
53 TRUE AS mortality
54FROM fact.ecog
55WHERE ecog_status = '5'
56AND BTRIM(date_of_ecog) IS NOT NULL;
57
58DROP TABLE IF EXISTS fact.patient;
59
60CREATE TABLE fact.patient
61AS
62(SELECT x.id AS patient_id,
63 p.date_of_birth,
64 p.date_of_death,
65 p.first_name,
66 p.middle_name,
67 p.last_name,
68 p.insurance_policy_number AS policy_num,
69 p.medical_record_number AS medical_record_num,
70 x.entity_id,
71 h.tobacco,
72 x.sex,
73 x.race,
74 x.race_absent,
75 x.spanish_hispanic_origin,
76 x.spanish_hispanic_origin_absent,
77 x.date_of_initial_visit,
78 x.primary_care_physician,
79 x.hospital_state,
80 x.hospital,
81 x.insurance_carrier,
82 x.insurance_plan,
83 x.insurance_group_number,
84 x.insurance_plan_code,
85 x.results_pending,
86 CASE
87 WHEN m.mortality IS TRUE THEN TRUE
88 ELSE FALSE
89 END AS mortality,
90 CASE
91 WHEN x.id IS NOT NULL THEN TRUE
92 ELSE FALSE
93 END AS grade0
94 FROM v2.patient AS x
95 JOIN v2.phi AS p ON x.id = p.patient_id
96 JOIN v2.patient_history AS h ON h.patient_id = x.id
97 LEFT JOIN mortality AS m ON m.patient_id = x.id);
98
99GRANT ALL
100 ON TABLE fact.patient
101 TO GROUP cal_reader;
102
103
104DROP TABLE IF EXISTS intervention_temp;
105
106SELECT id AS intervention_id,
107 entity_id,
108 patient_id,
109 progression_group_id AS group_id,
110 progression_track_id AS track_id,
111 cna,
112 type AS intervention_type,
113 intent,
114 goal,
115 status,
116 start_date AS intervention_start_date,
117 type_declined,
118 date_declined,
119 optimal_tx_exclusions_advanced_age,
120 optimal_tx_exclusions_cardiac,
121 optimal_tx_exclusions_elevated_bmi,
122 optimal_tx_exclusions_hematopoietic,
123 optimal_tx_exclusions_hepatic,
124 optimal_tx_exclusions_neurologic,
125 optimal_tx_exclusions_pulmonary,
126 optimal_tx_exclusions_renal,
127 optimal_tx_exclusions_other
128 INTO TEMP TABLE intervention_temp
129FROM v2.intervention;
130
131DROP TABLE IF EXISTS ecog_temp;
132
133SELECT e.patient_id,
134 i.intervention_id,
135 i.track_id,
136 e.ecog_id,
137 i.intervention_start_date,
138 e.date_of_ecog,
139 e.ecog_status,
140 DATEDIFF(DAY,i.intervention_start_date,e.date_of_ecog) AS ecog_diff INTO TEMP TABLE ecog_temp
141FROM intervention_temp AS i
142 LEFT JOIN fact.ecog AS e ON i.patient_id = e.patient_id;
143
144DROP TABLE IF EXISTS ecog_closest;
145
146-- this finds the closest ECOG to date_of_diagnosis
147SELECT track_id,
148 MAX(best_ecog) AS best_ecog INTO TEMP TABLE ecog_closest
149FROM (SELECT track_id,
150 ecog_id,
151 CASE
152 WHEN ecog_diff <= 0 THEN ecog_diff
153 ELSE NULL
154 END AS best_ecog
155 FROM ecog_temp)
156WHERE best_ecog IS NOT NULL
157GROUP BY track_id;
158
159DROP TABLE IF EXISTS ecog_best;
160
161-- this joins that search
162SELECT x.track_id,
163 y.patient_id,
164 y.ecog_id,
165 y.date_of_ecog,
166 y.ecog_status,
167 x.ecog_at_intervention INTO TEMP TABLE ecog_best
168FROM (SELECT MIN(ecog_at_intervention) AS ecog_at_intervention,
169 track_id
170 FROM (SELECT t.track_id,
171 t.ecog_id AS ecog_at_intervention
172 FROM ecog_closest AS c
173 JOIN ecog_temp AS t ON t.track_id = c.track_id
174 WHERE t.ecog_diff = c.best_ecog)
175 GROUP BY track_id) AS x
176 JOIN fact.ecog AS y ON x.ecog_at_intervention = y.ecog_id;
177
178DROP TABLE IF EXISTS fact.intervention;
179
180CREATE TABLE fact.intervention
181AS
182(SELECT i.intervention_id,
183 i.entity_id,
184 i.patient_id,
185 i.group_id,
186 i.track_id,
187 e.ecog_id,
188 e.date_of_ecog,
189 e.ecog_status,
190 e.ecog_at_intervention,
191 i.cna,
192 i.intervention_type,
193 i.intent,
194 i.goal,
195 i.status,
196 i.intervention_start_date,
197 i.type_declined,
198 i.date_declined,
199 i.optimal_tx_exclusions_advanced_age,
200 i.optimal_tx_exclusions_cardiac,
201 i.optimal_tx_exclusions_elevated_bmi,
202 i.optimal_tx_exclusions_hematopoietic,
203 i.optimal_tx_exclusions_hepatic,
204 i.optimal_tx_exclusions_neurologic,
205 i.optimal_tx_exclusions_pulmonary,
206 i.optimal_tx_exclusions_renal,
207 i.optimal_tx_exclusions_other
208FROM intervention_temp AS i
209 JOIN ecog_best AS e ON e.track_id = i.track_id);
210
211GRANT ALL
212 ON TABLE fact.intervention
213 TO GROUP cal_reader;
214
215DROP TABLE IF EXISTS fact.breast;
216
217CREATE TABLE fact.breast
218AS
219(SELECT id AS diagnosis_id,
220 patient_id,
221 entity_id,
222 diagnosis_subtype,
223 primary_oncologist,
224 progression_group_id AS group_id,
225 d.progression_track_id AS track_id,
226 attributes_histology AS histology,
227 attributes_histologic_grade AS histologic_grade,
228 stage,
229 --stage_subtype,
230 date_of_diagnosis,
231 icd9,
232 icd10,
233 --progression_number,
234 creation_timestamp,
235 creation_source,
236 creation_user,
237 last_modified_timestamp,
238 last_modified_source,
239 last_modified_user,
240 record_owner,
241 record_status,
242 t,
243 molecular_profiling_oncotype_dx_value AS oncotype_dx,
244 molecular_profiling_estrogen_receptor AS estrogen_receptor,
245 molecular_profiling_progesterone_receptor AS progesterone_receptor,
246 molecular_profiling_her2_neu AS her2_neu,
247 molecular_profiling_ki67 AS ki67,
248 molecular_profiling_p53 AS p53,
249 molecular_profiling_mammaprint AS mammaprint,
250 molecular_profiling_brca_1 AS brca_1,
251 molecular_profiling_brca_2 AS brca_2,
252 molecular_profiling_brca_mutation AS brca_mutation,
253 molecular_profiling_pd_l1 AS pdl1,
254 molecular_profiling_ctla_4 AS clta_4,
255 molecular_profiling_pik3ca AS pik3ca,
256 molecular_profiling_akt_1 AS akt_1,
257 molecular_profiling_pten AS pten,
258 molecular_profiling_fgfr1 AS fgfr1,
259 molecular_profiling_fgfr2 AS fgfr2,
260 molecular_profiling_extended_profiling AS extended_profiling
261FROM v2.diagnosis_breast AS d
262 JOIN cal.genetic_summary_breast AS g ON g.progression_track_id = d.progression_track_id);
263
264GRANT ALL
265 ON TABLE fact.breast
266 TO GROUP cal_reader;
267
268DROP TABLE IF EXISTS fact.lung;
269
270CREATE TABLE fact.lung
271AS
272(SELECT id AS diagnosis_id,
273 patient_id,
274 entity_id,
275 diagnosis_subtype,
276 primary_oncologist,
277 progression_group_id AS group_id,
278 d.progression_track_id AS track_id,
279 attributes_histology AS histology,
280 attributes_histologic_grade AS histologic_grade,
281 stage,
282 --stage_subtype,
283 date_of_diagnosis,
284 icd9,
285 icd10,
286 --progression_number,
287 creation_timestamp,
288 creation_source,
289 creation_user,
290 last_modified_timestamp,
291 last_modified_source,
292 last_modified_user,
293 record_owner,
294 record_status,
295 attributes_pancoast_tumor AS pancoast_tumor,
296 molecular_profiling_egfr AS egfr,
297 molecular_profiling_egfr_exon_18 AS egfr_exon_18,
298 molecular_profiling_egfr_exon_19 AS egfr_exon_19,
299 molecular_profiling_egfr_exon_20 AS egfr_exon_20,
300 molecular_profiling_egfr_exon_21 AS egfr_exon_21,
301 molecular_profiling_egfr_t790m AS egfr_t790m,
302 molecular_profiling_alk AS alk,
303 molecular_profiling_kras AS kras,
304 molecular_profiling_braf_v600e AS braf_v600e,
305 molecular_profiling_braf_mutation AS braf_mutation,
306 molecular_profiling_erbb2_amplification AS erbb2_amplification,
307 molecular_profiling_erbb2_point_mutation_exon_20 AS erbb2_point_mutation_exon_20,
308 molecular_profiling_ros_1 AS ros_1,
309 molecular_profiling_ret AS ret,
310 molecular_profiling_met_exon_14 AS met_exon_14,
311 molecular_profiling_met_amplification AS met_amplification,
312 molecular_profiling_pd_l1 AS pd_l1,
313 molecular_profiling_bcl2 AS bcl2,
314 molecular_profiling_ttf_1 AS ttf_1,
315 molecular_profiling_cox_2 AS cox_2,
316 molecular_profiling_ki67 AS ki67,
317 molecular_profiling_p53 AS p53,
318 molecular_profiling_akt_1 AS akt_1,
319 molecular_profiling_fgfr1 AS fgfr1,
320 molecular_profiling_fgfr3 AS fgfr3,
321 molecular_profiling_nras AS nras,
322 molecular_profiling_pik3ca AS pik3ca,
323 molecular_profiling_pten AS pten,
324 molecular_profiling_mek1 AS mek1,
325 molecular_profiling_ctla_4 AS ctla_4,
326 molecular_profiling_ntrk1 AS ntrk1,
327 molecular_profiling_extended_profiling AS extended_profiling
328FROM v2.diagnosis_lung AS d
329 JOIN cal.genetic_summary_lung AS g ON g.progression_track_id = d.progression_track_id);
330
331GRANT ALL
332 ON TABLE fact.lung
333 TO GROUP cal_reader;
334
335DROP TABLE IF EXISTS fact.colon;
336
337CREATE TABLE fact.colon
338AS
339(SELECT id AS diagnosis_id,
340 patient_id,
341 entity_id,
342 diagnosis_subtype,
343 primary_oncologist,
344 progression_group_id AS group_id,
345 d.progression_track_id AS track_id,
346 attributes_histology AS histology,
347 attributes_histologic_grade AS histologic_grade,
348 stage,
349 --stage_subtype,
350 date_of_diagnosis,
351 icd9,
352 icd10,
353 --progression_number,
354 creation_timestamp,
355 creation_source,
356 creation_user,
357 last_modified_timestamp,
358 last_modified_source,
359 last_modified_user,
360 record_owner,
361 record_status,
362 molecular_profiling_dcc AS dcc,
363 attributes_tumor_resection_circumferential_margin AS circumferential_margin,
364 attributes_obstruction AS obstruction,
365 attributes_perforation AS perforation,
366 attributes_lymphovascular_invasion AS lymphovascular_invasion,
367 attributes_tumor_resection_completeness AS resection_completeness,
368 molecular_profiling_loh AS loh,
369 molecular_profiling_mmr AS mmr,
370 molecular_profiling_p27 AS p27,
371 molecular_profiling_msi_pcr_result AS msi_pcr,
372 molecular_profiling_mlh1 AS mlh1,
373 molecular_profiling_msh2 AS msh2,
374 molecular_profiling_msh6 AS msh6,
375 molecular_profiling_pms2 AS pms2,
376 molecular_profiling_msi AS msi,
377 molecular_profiling_proven_familial_colon_cancer AS proven_familial_colon_cancer,
378 molecular_profiling_kras AS kras,
379 molecular_profiling_nras AS nras,
380 molecular_profiling_braf AS braf,
381 molecular_profiling_pik3ca AS pik3ca,
382 molecular_profiling_smad4 AS smad4,
383 molecular_profiling_apc AS apc,
384 molecular_profiling_akt_1 AS akt_1,
385 molecular_profiling_pten AS pten,
386 molecular_profiling_vegf AS vegf,
387 molecular_profiling_pd_l1 AS pd_l1,
388 molecular_profiling_ctla_4 AS ctla_4,
389 molecular_profiling_extended_profiling AS extended_profiling
390FROM v2.diagnosis_colon AS d
391 JOIN cal.genetic_summary_colon AS g ON g.progression_track_id = d.progression_track_id);
392
393GRANT ALL
394 ON TABLE fact.colon
395 TO GROUP cal_reader;
396
397DROP TABLE IF EXISTS fact.mantle;
398CREATE TABLE fact.mantle
399AS
400(SELECT id AS diagnosis_id,
401 patient_id,
402 entity_id,
403 diagnosis_subtype,
404 primary_oncologist,
405 progression_group_id AS group_id,
406 d.progression_track_id AS track_id,
407 attributes_histology AS histology,
408 stage,
409 --stage_subtype,
410 date_of_diagnosis,
411 icd9,
412 icd10,
413 --progression_number,
414 creation_timestamp,
415 creation_source,
416 creation_user,
417 last_modified_timestamp,
418 last_modified_source,
419 last_modified_user,
420 record_owner,
421 record_status,
422 molecular_profiling_cd5 AS cd5,
423 molecular_profiling_cd10 AS cd10,
424 molecular_profiling_cd19 AS mcd19,
425 molecular_profiling_cd20 AS cd20,
426 molecular_profiling_cd23 AS cd23,
427 molecular_profiling_cyclin_d1 AS cyclin_d1,
428 molecular_profiling_sox_11 AS sox_11,
429 molecular_profiling_atm AS atm,
430 molecular_profiling_p53 AS p53,
431 molecular_profiling_somatic_mutations AS somatic_mutations,
432 molecular_profiling_extended_profiling AS extended_profiling
433FROM v2.diagnosis_mantle AS d
434 JOIN cal.genetic_summary_mantle AS g ON g.progression_track_id = d.progression_track_id);
435
436GRANT ALL
437 ON TABLE fact.mantle
438 TO GROUP cal_reader;
439
440DROP TABLE IF EXISTS fact.dlbc;
441
442CREATE TABLE fact.dlbc
443AS
444(SELECT id AS diagnosis_id,
445 patient_id,
446 entity_id,
447 diagnosis_subtype,
448 primary_oncologist,
449 progression_group_id AS group_id,
450 progression_track_id AS track_id,
451 attributes_histology AS histology,
452 stage,
453 --stage_subtype,
454 date_of_diagnosis,
455 icd9,
456 icd10,
457 --progression_number,
458 creation_timestamp,
459 creation_source,
460 creation_user,
461 last_modified_timestamp,
462 last_modified_source,
463 last_modified_user,
464 record_owner,
465 record_status
466FROM v2.diagnosis_diffuse_large_b_cell);
467
468GRANT ALL
469 ON TABLE fact.dlbc
470 TO GROUP cal_reader;
471
472DROP TABLE IF EXISTS fact.multiple_myeloma;
473
474CREATE TABLE fact.multiple_myeloma
475AS
476(SELECT id AS diagnosis_id,
477 patient_id,
478 entity_id,
479 diagnosis_subtype,
480 primary_oncologist,
481 progression_group_id AS group_id,
482 d.progression_track_id AS track_id,
483 NULL AS histology,
484 staging_durie_salmon_principal_stage AS stage,
485 --stage_subtype,
486 date_of_diagnosis,
487 icd9,
488 icd10,
489 --progression_number,
490 creation_timestamp,
491 creation_source,
492 creation_user,
493 last_modified_timestamp,
494 last_modified_source,
495 last_modified_user,
496 record_owner,
497 record_status,
498 molecular_profiling_del_17_p13 AS del_17_p13,
499 molecular_profiling_t_14_16 AS t_14_16,
500 molecular_profiling_t_14_20 AS t_14_20,
501 molecular_profiling_del_1p AS del_1p,
502 molecular_profiling_amp_1q AS amp_1q,
503 molecular_profiling_t_4_14 AS t_4_14,
504 molecular_profiling_t_11_14 AS t_11_14,
505 molecular_profiling_t_6_14 AS t_6_14,
506 molecular_profiling_del_13 AS del_13,
507 molecular_profiling_hypodiploidy AS hypodiploidy,
508 molecular_profiling_hyperdiploidy AS hyperdiploidy,
509 molecular_profiling_extended_profiling AS extended_profiling
510FROM v2.diagnosis_multiple_myeloma AS d
511 JOIN cal.genetic_summary_multiple_myeloma AS g ON g.progression_track_id = d.progression_track_id);
512
513GRANT ALL
514 ON TABLE fact.multiple_myeloma
515 TO GROUP cal_reader;
516
517DROP TABLE IF EXISTS fact.unresearched;
518
519CREATE TABLE fact.unresearched
520AS
521(SELECT id AS diagnosis_id,
522 patient_id,
523 entity_id,
524 diagnosis_subtype,
525 primary_oncologist,
526 progression_group_id AS group_id,
527 progression_track_id AS track_id,
528 NULL AS histology,
529 NULL AS stage,
530 --stage_subtype,
531 date_of_diagnosis,
532 icd9,
533 icd10,
534 --progression_number,
535 creation_timestamp,
536 creation_source,
537 creation_user,
538 last_modified_timestamp,
539 last_modified_source,
540 last_modified_user,
541 record_owner,
542 record_status
543FROM v2.diagnosis_unresearched);
544
545GRANT ALL
546 ON TABLE fact.unresearched
547 TO GROUP cal_reader;
548
549DROP TABLE IF EXISTS diagnosis_all;
550
551SELECT diagnosis_id,
552 patient_id,
553 entity_id,
554 diagnosis_subtype,
555 primary_oncologist,
556 group_id,
557 track_id,
558 histology,
559 stage,
560 --stage_subtype,
561 date_of_diagnosis,
562 icd9,
563 icd10,
564 --progression_number,
565 creation_timestamp,
566 creation_source,
567 creation_user,
568 last_modified_timestamp,
569 last_modified_source,
570 last_modified_user,
571 record_owner,
572 record_status INTO TEMP TABLE diagnosis_all
573FROM fact.breast
574UNION
575SELECT diagnosis_id,
576 patient_id,
577 entity_id,
578 diagnosis_subtype,
579 primary_oncologist,
580 group_id,
581 track_id,
582 histology,
583 stage,
584 --stage_subtype,
585 date_of_diagnosis,
586 icd9,
587 icd10,
588 --progression_number,
589 creation_timestamp,
590 creation_source,
591 creation_user,
592 last_modified_timestamp,
593 last_modified_source,
594 last_modified_user,
595 record_owner,
596 record_status
597FROM fact.lung
598UNION
599SELECT diagnosis_id,
600 patient_id,
601 entity_id,
602 diagnosis_subtype,
603 primary_oncologist,
604 group_id,
605 track_id,
606 histology,
607 stage,
608 --stage_subtype,
609 date_of_diagnosis,
610 icd9,
611 icd10,
612 --progression_number,
613 creation_timestamp,
614 creation_source,
615 creation_user,
616 last_modified_timestamp,
617 last_modified_source,
618 last_modified_user,
619 record_owner,
620 record_status
621FROM fact.colon
622UNION
623SELECT diagnosis_id,
624 patient_id,
625 entity_id,
626 diagnosis_subtype,
627 primary_oncologist,
628 group_id,
629 track_id,
630 histology,
631 stage,
632 --stage_subtype,
633 date_of_diagnosis,
634 icd9,
635 icd10,
636 --progression_number,
637 creation_timestamp,
638 creation_source,
639 creation_user,
640 last_modified_timestamp,
641 last_modified_source,
642 last_modified_user,
643 record_owner,
644 record_status
645FROM fact.mantle
646UNION
647SELECT diagnosis_id,
648 patient_id,
649 entity_id,
650 diagnosis_subtype,
651 primary_oncologist,
652 group_id,
653 track_id,
654 histology,
655 stage,
656 --stage_subtype,
657 date_of_diagnosis,
658 icd9,
659 icd10,
660 --progression_number,
661 creation_timestamp,
662 creation_source,
663 creation_user,
664 last_modified_timestamp,
665 last_modified_source,
666 last_modified_user,
667 record_owner,
668 record_status
669FROM fact.dlbc
670UNION
671SELECT diagnosis_id,
672 patient_id,
673 entity_id,
674 diagnosis_subtype,
675 primary_oncologist,
676 group_id,
677 track_id,
678 histology,
679 stage,
680 --stage_subtype,
681 date_of_diagnosis,
682 icd9,
683 icd10,
684 --progression_number,
685 creation_timestamp,
686 creation_source,
687 creation_user,
688 last_modified_timestamp,
689 last_modified_source,
690 last_modified_user,
691 record_owner,
692 record_status
693FROM fact.multiple_myeloma
694UNION
695SELECT diagnosis_id,
696 patient_id,
697 entity_id,
698 diagnosis_subtype,
699 primary_oncologist,
700 group_id,
701 track_id,
702 histology,
703 stage,
704 --stage_subtype,
705 date_of_diagnosis,
706 icd9,
707 icd10,
708 --progression_number,
709 creation_timestamp,
710 creation_source,
711 creation_user,
712 last_modified_timestamp,
713 last_modified_source,
714 last_modified_user,
715 record_owner,
716 record_status
717FROM fact.unresearched;
718
719--progression_track fact table
720DROP TABLE IF EXISTS track_temp;
721
722-- gets all the treatement data
723SELECT d.track_id,
724 d.diagnosis_id,
725 d.patient_id,
726 d.group_id,
727 t.progression_number,
728 d.date_of_diagnosis,
729 DATEDIFF(YEAR,p.date_of_birth,d.date_of_diagnosis) AS age_at_diagnosis,
730 d.diagnosis_subtype,
731 d.stage,
732 d.entity_id,
733 d.icd9,
734 d.icd10,
735 d.primary_oncologist,
736 d.histology,
737 ROW_NUMBER() OVER (PARTITION BY d.group_id ORDER BY d.date_of_diagnosis ASC) AS track_ord INTO TEMP TABLE track_temp
738FROM v2.progression_track AS t
739 LEFT JOIN diagnosis_all AS d ON t.id = d.track_id
740 LEFT JOIN v2.phi AS p ON t.patient_id = p.patient_id;
741
742DROP TABLE IF EXISTS ecog_temp;
743
744SELECT e.patient_id,
745 t.track_id,
746 e.ecog_id,
747 t.date_of_diagnosis,
748 e.date_of_ecog,
749 e.ecog_status,
750 DATEDIFF(DAY,t.date_of_diagnosis,e.date_of_ecog) AS ecog_diff INTO TEMP TABLE ecog_temp
751FROM track_temp AS t
752 LEFT JOIN fact.ecog AS e ON t.patient_id = e.patient_id;
753
754DROP TABLE IF EXISTS ecog_closest;
755
756-- this finds the closest ECOG to date_of_diagnosis
757SELECT track_id,
758 MAX(best_ecog) AS best_ecog INTO TEMP TABLE ecog_closest
759FROM (SELECT track_id,
760 ecog_id,
761 CASE
762 WHEN ecog_diff <= 0 THEN ecog_diff
763 ELSE NULL
764 END AS best_ecog
765 FROM ecog_temp)
766WHERE best_ecog IS NOT NULL
767GROUP BY track_id;
768
769DROP TABLE IF EXISTS ecog_best;
770
771-- this joins that search
772SELECT x.track_id,
773 y.patient_id,
774 y.ecog_id,
775 y.date_of_ecog,
776 y.ecog_status,
777 x.ecog_at_diagnosis INTO TEMP TABLE ecog_best
778FROM (SELECT MIN(ecog_at_diagnosis) AS ecog_at_diagnosis,
779 track_id
780 FROM (SELECT t.track_id,
781 t.ecog_id AS ecog_at_diagnosis
782 FROM ecog_closest AS c
783 JOIN ecog_temp AS t ON t.track_id = c.track_id
784 WHERE t.ecog_diff = c.best_ecog)
785 GROUP BY track_id) AS x
786 JOIN fact.ecog AS y ON x.ecog_at_diagnosis = y.ecog_id;
787
788DROP TABLE IF EXISTS track_1;
789
790SELECT t.track_id,
791 t.diagnosis_id,
792 t.patient_id,
793 t.entity_id,
794 t.group_id,
795 t.date_of_diagnosis,
796 t.age_at_diagnosis,
797 d.date_of_death_outcome,
798 t.diagnosis_subtype,
799 t.stage,
800 t.progression_number,
801 t.icd9,
802 t.icd10,
803 t.primary_oncologist,
804 t.histology,
805 CASE
806 WHEN t.track_ord = '1' THEN TRUE
807 ELSE FALSE
808 END AS is_initial,
809 e.date_of_ecog,
810 e.ecog_status,
811 e.ecog_at_diagnosis INTO TEMP TABLE track_1
812FROM track_temp AS t
813 LEFT JOIN ecog_best AS e ON t.track_id = e.track_id
814 LEFT JOIN (SELECT progression_track_id AS track_id,
815 date_of_outcome AS date_of_death_outcome
816 FROM v2.outcome
817 WHERE response_Type = '5') AS d ON t.track_id = d.track_id;
818
819DROP TABLE IF EXISTS track_2;
820
821SELECT t.track_id,
822 t.diagnosis_id,
823 t.patient_id,
824 t.entity_id,
825 t.group_id,
826 t.date_of_diagnosis,
827 t.age_at_diagnosis,
828 t.date_of_death_outcome,
829 t.diagnosis_subtype,
830 t.stage,
831 t.progression_number,
832 t.icd9,
833 t.icd10,
834 t.primary_oncologist,
835 t.histology,
836 t.is_initial,
837 t.date_of_ecog,
838 t.ecog_status,
839 t.ecog_at_diagnosis,
840 CASE
841 WHEN patient_id IS NOT NULL
842 AND (icd9 IS NOT NULL OR icd10 IS NOT NULL) THEN TRUE
843 ELSE FALSE
844 END AS grade1,
845 CASE
846 WHEN patient_id IS NOT NULL
847 AND (icd9 IS NOT NULL OR icd10 IS NOT NULL)
848 AND (LEFT (stage,1) IN ('I',0))
849 AND (stage NOT IN ('Insufficient Data','No Match'))
850 AND histology IS NOT NULL
851 AND stage IS NOT NULL THEN TRUE
852 ELSE FALSE
853 END AS grade2 INTO TEMP TABLE track_2
854FROM track_1 AS t
855WHERE track_id IS NOT NULL;
856
857DROP TABLE IF EXISTS grade3;
858
859SELECT d.track_id,
860 CASE
861 WHEN grade2 IS TRUE
862 AND ecog_at_diagnosis IS NOT NULL
863 AND age_at_diagnosis IS NOT NULL
864 AND histologic_grade IS NOT NULL
865 AND alk IS NOT NULL
866 AND braf_v600e IS NOT NULL
867 AND egfr IS NOT NULL
868 AND kras IS NOT NULL
869 AND pd_l1 IS NOT NULL
870 AND met_amplification IS NOT NULL
871 AND pancoast_tumor IS NOT NULL
872 THEN TRUE
873 ELSE FALSE
874 END AS grade3 INTO TEMP TABLE grade3
875FROM fact.lung AS d
876 JOIN track_2 AS t ON t.track_id = d.track_id
877UNION
878SELECT d.track_id,
879 CASE
880 WHEN grade2 IS TRUE
881 AND ecog_at_diagnosis IS NOT NULL
882 AND age_at_diagnosis IS NOT NULL
883 AND estrogen_receptor IS NOT NULL
884 AND her2_neu IS NOT NULL
885 AND oncotype_dx IS NOT NULL
886 AND progesterone_receptor IS NOT NULL
887 AND t IS NOT NULL
888 THEN TRUE
889 ELSE FALSE
890 END AS grade3
891FROM fact.breast AS d
892 JOIN track_2 AS t ON t.track_id = d.track_id
893UNION
894SELECT d.track_id,
895 CASE
896 WHEN grade2 IS TRUE
897 AND ecog_at_diagnosis IS NOT NULL
898 AND age_at_diagnosis IS NOT NULL
899 AND histologic_grade IS NOT NULL
900 AND braf IS NOT NULL
901 AND kras IS NOT NULL
902 AND loh IS NOT NULL
903 AND mmr IS NOT NULL
904 AND nras IS NOT NULL
905 AND msi_pcr IS NOT NULL
906 AND p27 IS NOT NULL
907 AND pik3ca IS NOT NULL
908 AND dcc IS NOT NULL
909 AND circumferential_margin IS NOT NULL
910 AND obstruction IS NOT NULL
911 AND perforation IS NOT NULL
912 AND lymphovascular_invasion IS NOT NULL
913 AND resection_completeness IS NOT NULL
914 THEN TRUE
915 ELSE FALSE
916 END AS grade3
917FROM fact.colon AS d
918 JOIN track_2 AS t ON t.track_id = d.track_id;
919
920-- join this into fact.track so there is a grade3 BOOL column
921DROP TABLE IF EXISTS grade4;
922
923SELECT track_id,
924 TRUE AS grade4 INTO TEMP TABLE grade4
925FROM (SELECT i.track_id
926 FROM fact.intervention AS i
927 JOIN (SELECT track_id FROM grade3 WHERE grade3.grade3 IS TRUE) AS t ON t.track_id = i.track_id
928 WHERE intervention_start_date IS NOT NULL
929 OR date_declined IS NOT NULL
930 GROUP BY i.track_id);
931
932DROP TABLE IF EXISTS classification;
933
934SELECT t.track_id,
935 CASE WHEN grade1 IS TRUE THEN TRUE ELSE FALSE END AS grade1,
936 CASE WHEN grade2 IS TRUE THEN TRUE ELSE FALSE END AS grade2,
937 CASE WHEN grade3 IS TRUE THEN TRUE ELSE FALSE END AS grade3,
938 CASE WHEN grade4 IS TRUE THEN TRUE ELSE FALSE END AS grade4,
939 CASE WHEN grade4 IS TRUE THEN '4'
940 WHEN grade3 IS TRUE THEN '3'
941 WHEN grade2 IS TRUE THEN '2'
942 WHEN grade1 IS TRUE THEN '1'
943 ELSE '0' END AS max_grade
944 INTO TEMP TABLE classification
945FROM track_2 AS t
946 LEFT JOIN grade3 ON t.track_id = grade3.track_id
947 LEFT JOIN grade4 ON t.track_id = grade4.track_id;
948
949DROP TABLE IF EXISTS fact.track;
950
951CREATE TABLE fact.track
952AS
953(SELECT t.track_id,
954 t.diagnosis_id,
955 t.patient_id,
956 t.entity_id,
957 t.group_id,
958 t.date_of_diagnosis,
959 t.age_at_diagnosis,
960 t.date_of_death_outcome,
961 t.diagnosis_subtype,
962 t.stage,
963 t.progression_number,
964 t.icd9,
965 t.icd10,
966 t.primary_oncologist,
967 t.histology,
968 t.is_initial,
969 t.date_of_ecog,
970 t.ecog_status,
971 t.ecog_at_diagnosis,
972 c.grade1,
973 c.grade2,
974 c.grade3,
975 c.grade4,
976 c.max_grade
977FROM track_2 AS t
978 JOIN classification AS c ON c.track_id = t.track_id);
979
980GRANT ALL
981 ON TABLE fact.track
982 TO GROUP cal_reader;
983DROP TABLE IF EXISTS fact.therapy;
984
985CREATE TABLE fact.therapy
986AS
987(SELECT track_id,
988 intervention_type,
989 patient_id,
990 intervention_id,
991 -- numeric decode makes the other stuff eiser.
992 DECODE(therapy_num,
993 1, 'n',
994 2, 'a'
995 ) AS therapy,
996 CASE
997 WHEN therapy_num = 1 THEN TRUE
998 ELSE FALSE
999 END AS type_n,
1000 CASE
1001 WHEN therapy_num = 2 THEN TRUE
1002 ELSE FALSE
1003 END AS type_a
1004FROM (SELECT track_id,
1005 intervention_type,
1006 patient_id,
1007 intervention_id,
1008 therapy_num
1009 FROM (SELECT track_id,
1010 status,
1011 intervention_type,
1012 patient_id,
1013 intervention_id,
1014 DECODE(status,
1015 'true', '1',
1016 NULL
1017 ) AS therapy_num
1018 FROM fact.intervention
1019 UNION
1020 --filtering for interventions that have specific drug_types
1021 SELECT track_id,
1022 status,
1023 intervention_type,
1024 patient_id,
1025 intervention_id,
1026 DECODE(status,
1027 'true', '2',
1028 NULL
1029 ) AS therapy_num
1030 FROM (SELECT i.*
1031 FROM fact.intervention AS i
1032 JOIN (SELECT intervention_id
1033 FROM v2.new_drug
1034 WHERE drug_type IN ('hormonal','targeted_biologic','chemotherapy','targeted_other')
1035 GROUP BY intervention_id) AS d ON i.intervention_id = d.intervention_id))));
1036
1037GRANT ALL
1038 ON TABLE fact.therapy
1039 TO GROUP cal_reader;
1040
1041DROP TABLE IF EXISTS temp_events;
1042-- chane this so it uses fact tables, not V2
1043SELECT progression_track_id,
1044 patient_id,
1045 id AS event_id,
1046 'adherence' AS event_type,
1047 date AS event_date INTO TEMP TABLE temp_events
1048FROM v2.adherence
1049WHERE event_date IS NOT NULL
1050UNION
1051(SELECT progression_track_id,
1052 patient_id,
1053 id AS event_id,
1054 'intervention' AS event_type,
1055 start_date AS event_date
1056FROM v2.intervention
1057WHERE event_Date IS NOT NULL)
1058UNION
1059(SELECT progression_track_id,
1060 patient_id,
1061 id AS event_id,
1062 'regimen' AS event_type,
1063 start_date AS event_date
1064FROM v2.new_regimen
1065WHERE event_date IS NOT NULL)
1066UNION
1067(SELECT progression_track_id,
1068 patient_id,
1069 id AS event_id,
1070 'outcome' AS event_type,
1071 date_of_outcome AS event_date
1072FROM v2.outcome
1073WHERE event_date IS NOT NULL)
1074UNION
1075(SELECT t.track AS progression_track_id,
1076 e.patient_id,
1077 e.id AS event_id,
1078 'toxicity' AS event_type,
1079 e.date_of_toxicity AS event_date
1080FROM v2.toxicity AS e
1081 JOIN (SELECT t.id AS track,
1082 p.id AS patient
1083 FROM v2.progression_track t
1084 INNER JOIN v2.patient p ON t.patient_id = p.id) t ON t.patient = e.patient_id
1085WHERE event_date IS NOT NULL)
1086UNION
1087(SELECT t.track AS progression_track_id,
1088 e.patient_id,
1089 e.id AS event_id,
1090 'toxicity' AS event_type,
1091 e.date AS event_date
1092FROM v2.ecog e
1093 JOIN (SELECT t.id AS track,
1094 p.id AS patient
1095 FROM v2.progression_track t
1096 INNER JOIN v2.patient p ON t.patient_id = p.id) t ON t.patient = e.patient_id
1097WHERE event_date IS NOT NULL)
1098UNION
1099(SELECT progression_track_id,
1100 patient_id,
1101 id AS event_id,
1102 'cycle_delivery' AS event_type,
1103 delivered_delivery_day AS event_date
1104FROM v2.cycle_delivery
1105WHERE event_date IS NOT NULL)
1106UNION
1107(SELECT progression_track_id,
1108 patient_id,
1109 id AS event_id,
1110 'refil' AS event_type,
1111 date_of_refill AS event_date
1112FROM v2.refill
1113WHERE event_date IS NOT NULL)
1114ORDER BY progression_track_id ASC;
1115
1116DROP TABLE IF EXISTS fact.events;
1117
1118CREATE TABLE fact.events
1119AS
1120(SELECT event_id,
1121 patient_id,
1122 track_id,
1123 event_date,
1124 event_type,
1125 date_of_diagnosis,
1126 DATEDIFF(DAY,date_of_diagnosis,event_date) AS event_delta
1127FROM (SELECT e.patient_id,
1128 e.event_id,
1129 e.progression_track_id as track_id,
1130 e.event_type,
1131 e.event_date,
1132 t.date_of_diagnosis
1133 FROM fact.track AS t
1134 JOIN temp_events AS e ON t.track_id = e.progression_track_id));
1135
1136GRANT ALL
1137 ON TABLE fact.events
1138 TO GROUP cal_reader;
1139
1140DROP TABLE IF EXISTS fact.followup;
1141
1142CREATE TABLE fact.followup
1143AS
1144(SELECT track_id,
1145 BOOL_OR(passed_6mo) AS passed_6mo,
1146 BOOL_OR(passed_1yr) AS passed_1yr,
1147 BOOL_OR(passed_2yr) AS passed_2yr,
1148 BOOL_OR(passed_3yr) AS passed_3yr,
1149 BOOL_OR(passed_4yr) AS passed_4yr,
1150 BOOL_OR(passed_5yr) AS passed_5yr,
1151 MAX(passed) AS max_passed
1152FROM (SELECT track_id,
1153 CASE
1154 WHEN event_delta > 182 AND event_delta < 365 THEN '0.5'
1155 WHEN event_delta > 365 AND event_delta < 730 THEN '1'
1156 WHEN event_delta > 730 AND event_delta < 1095 THEN '2'
1157 WHEN event_delta > 1095 AND event_delta < 1460 THEN '3'
1158 WHEN event_delta > 1460 AND event_delta < 1820 THEN '4'
1159 WHEN event_delta > 1820 THEN '5'
1160 END AS passed,
1161 CASE
1162 WHEN event_delta > 182 AND event_delta < 365 THEN TRUE
1163 ELSE FALSE
1164 END AS passed_6mo,
1165 CASE
1166 WHEN event_delta > 365 AND event_delta < 730 THEN TRUE
1167 ELSE FALSE
1168 END AS passed_1yr,
1169 CASE
1170 WHEN event_delta > 730 AND event_delta < 1095 THEN TRUE
1171 ELSE FALSE
1172 END AS passed_2yr,
1173 CASE
1174 WHEN event_delta > 1095 AND event_delta < 1460 THEN TRUE
1175 ELSE FALSE
1176 END AS passed_3yr,
1177 CASE
1178 WHEN event_delta > 1460 AND event_delta < 1820 THEN TRUE
1179 ELSE FALSE
1180 END AS passed_4yr,
1181 CASE
1182 WHEN event_delta > 1820 THEN TRUE
1183 ELSE FALSE
1184 END AS passed_5yr
1185 FROM fact.events)
1186GROUP BY track_id);
1187
1188GRANT ALL
1189 ON TABLE fact.followup
1190 TO GROUP cal_reader;
1191
1192DROP TABLE IF EXISTS fact.classification_summary;
1193
1194CREATE TABLE fact.classification_summary
1195AS
1196(SELECT t.track_id,
1197 p.patient_id,
1198 p.grade0,
1199 t.grade1,
1200 t.grade2,
1201 t.grade3,
1202 t.grade4,
1203 t.max_grade,
1204 p.mortality,
1205 f.passed_6mo,
1206 f.passed_1yr,
1207 f.passed_2yr,
1208 f.passed_3yr,
1209 f.passed_4yr,
1210 f.passed_5yr,
1211 f.max_passed
1212FROM fact.track AS t
1213 LEFT JOIN fact.patient AS p ON p.patient_id = t.patient_id
1214 JOIN fact.followup AS f ON f.track_id = t.track_id
1215ORDER BY p.patient_id);
1216
1217GRANT ALL
1218 ON TABLE fact.classification_summary
1219 TO GROUP cal_reader;
1220
1221DROP TABLE IF EXISTS fact.pgroup;
1222
1223CREATE TABLE fact.pgroup
1224AS
1225(SELECT g.id AS group_id,
1226 p.id AS patient_id,
1227 g.diagnosis_subtype,
1228 p.entity_id,
1229 g.date_of_visit
1230FROM v2.progression_group AS g
1231 JOIN v2.patient AS p ON g.patient_id = p.id);
1232
1233GRANT ALL
1234 ON TABLE fact.pgroup
1235 TO GROUP cal_reader;
1236
1237DROP TABLE IF EXISTS main;
1238
1239-- gets all the treatement data
1240SELECT x.patient_id,
1241 x.group_id,
1242 x.track_id,
1243 x.progression_number,
1244 x.diagnosis_subtype,
1245 x.date_of_diagnosis,
1246 o.date_of_outcome,
1247 i.intervention_type,
1248 i.intervention_start_date,
1249 o.response INTO TEMP TABLE main
1250FROM fact.track AS x
1251 JOIN fact.outcome AS o ON x.track_id = o.track_id
1252 JOIN fact.intervention AS i ON i.track_id = x.track_id;
1253
1254DROP TABLE IF EXISTS tempy_main;
1255
1256-- treatement data (main) timedeltas and analytical rank
1257SELECT patient_id,
1258 group_id,
1259 track_id,
1260 progression_number,
1261 diagnosis_subtype,
1262 date_of_diagnosis,
1263 date_of_outcome,
1264 response,
1265 intervention_start_date,
1266 intervention_type,
1267 CASE
1268 WHEN response = 0 THEN 4
1269 WHEN response = 1 THEN 3
1270 WHEN response = 2 THEN 1
1271 WHEN response = 3 THEN 2
1272 ELSE NULL
1273 END AS analytical_rank,
1274 CASE
1275 WHEN response = 0 THEN 'Stable'
1276 WHEN response = 1 THEN 'Partial'
1277 WHEN response = 2 THEN 'CR-RA-Pet Negative'
1278 WHEN response = 3 THEN 'CR (Complete Response)'
1279 WHEN response = 4 THEN 'Lost to Follow Up'
1280 WHEN response = 5 THEN 'Death'
1281 ELSE NULL
1282 END AS response_eng,
1283 CASE
1284 WHEN response = 5 THEN TRUE
1285 ELSE FALSE
1286 END AS death,
1287 CASE
1288 WHEN response = 4 THEN TRUE
1289 ELSE FALSE
1290 END AS lost,
1291 DATEDIFF(DAY,date_of_diagnosis,intervention_start_date) AS treatment_delta,
1292 DATEDIFF(DAY,date_of_diagnosis,date_of_outcome) AS outcome_delta INTO TEMP TABLE tempy_main
1293FROM main;
1294
1295
1296DROP TABLE IF EXISTS first_treatment;
1297
1298-- finds time to first treatment and type
1299SELECT tm.track_id,
1300 tm.response_eng AS first_response,
1301 time_to_first_response,
1302 MIN(tm.analytical_rank) AS min_analytical_rank INTO TEMP TABLE first_treatment
1303FROM tempy_main AS tm
1304 INNER JOIN (SELECT track_id,
1305 MIN(outcome_delta) AS time_to_first_response
1306 FROM tempy_main
1307 WHERE analytical_rank IS NOT NULL
1308 GROUP BY track_id) sub
1309 ON sub.track_id = tm.track_id
1310 AND sub.time_to_first_response = tm.outcome_delta
1311GROUP BY tm.track_id,
1312 first_response,
1313 time_to_first_response;
1314
1315DROP TABLE IF EXISTS best_response;
1316
1317-- finds time to best treatment and type
1318SELECT tm.track_id,
1319 tm.response_eng AS best_response,
1320 min_analytical_rank,
1321 MIN(outcome_delta) AS time_to_best_response INTO TEMP TABLE best_response
1322FROM tempy_main AS tm
1323 INNER JOIN (SELECT track_id,
1324 MIN(analytical_rank) AS min_analytical_rank
1325 FROM tempy_main
1326 WHERE analytical_rank IS NOT NULL
1327 GROUP BY track_id) sub
1328 ON sub.track_id = tm.track_id
1329 AND sub.min_analytical_rank = tm.analytical_rank
1330GROUP BY tm.track_id,
1331 min_analytical_rank,
1332 best_response;
1333
1334DROP TABLE IF EXISTS time_to_first_type;
1335
1336-- makes first_x columns based on treatment type
1337SELECT tempy4.track_id,
1338 MIN(tempy4.time_to_chemo) AS time_to_chemo,
1339 MIN(tempy4.time_to_radiation) AS time_to_radiation,
1340 MIN(tempy4.time_to_surgery) AS time_to_surgery INTO TEMP TABLE time_to_first_type
1341FROM (SELECT track_id,
1342 CASE
1343 WHEN intervention_type = 'chemotherapy' THEN time_to_first_treatment
1344 ELSE NULL
1345 END AS time_to_chemo,
1346 CASE
1347 WHEN intervention_type = 'surgery' THEN time_to_first_treatment
1348 ELSE NULL
1349 END AS time_to_surgery,
1350 CASE
1351 WHEN intervention_type = 'radiation' THEN time_to_first_treatment
1352 ELSE NULL
1353 END AS time_to_radiation
1354 FROM (SELECT track_id,
1355 intervention_type,
1356 MIN(treatment_delta) AS time_to_first_treatment
1357 FROM tempy_main
1358 GROUP BY track_id,
1359 intervention_type)) AS tempy4
1360GROUP BY track_id;
1361
1362DROP TABLE IF EXISTS next_progression;
1363
1364-- finds time to next progression
1365SELECT group_id,
1366 progression_number,
1367 track_id,
1368 date_of_diagnosis,
1369 DATEDIFF(day,date_of_diagnosis,LAG(date_of_diagnosis) OVER (PARTITION BY group_id ORDER BY date_of_diagnosis DESC)) AS time_to_next_progression INTO TEMP TABLE next_progression
1370FROM fact.track;
1371
1372DROP TABLE IF EXISTS no_death_track_delta;
1373
1374-- Join everything into one table
1375SELECT x.patient_id,
1376 x.group_id,
1377 x.track_id,
1378 x.date_of_diagnosis,
1379 x.progression_number,
1380 np.time_to_next_progression,
1381 ft.time_to_surgery,
1382 ft.time_to_radiation,
1383 ft.time_to_chemo AS time_to_chemotherapy,
1384 first_treatment.time_to_first_response,
1385 first_treatment.first_response,
1386 br.best_response,
1387 br.time_to_best_response INTO TEMP TABLE no_death_track_delta
1388FROM fact.track AS x
1389 LEFT JOIN (SELECT track_id,
1390 lost,
1391 death
1392 FROM tempy_main
1393 WHERE lost IS TRUE
1394 OR death IS TRUE) AS tm ON tm.track_id = x.track_id
1395 LEFT JOIN first_treatment ON first_treatment.track_id = x.track_id
1396 LEFT JOIN time_to_first_type AS ft ON ft.track_id = x.track_id
1397 LEFT JOIN next_progression AS np ON np.track_id = x.track_id
1398 LEFT JOIN best_response AS br ON br.track_id = x.track_id;
1399
1400DROP TABLE IF EXISTS pos_neg_track_delta;
1401
1402-- calcualte time to lost/death; depending
1403SELECT no_death.patient_id,
1404 group_id,
1405 track_id,
1406 date_of_diagnosis,
1407 progression_number,
1408 time_to_next_progression,
1409 time_to_surgery,
1410 time_to_radiation,
1411 time_to_chemotherapy,
1412 time_to_first_response,
1413 first_response,
1414 best_response,
1415 time_to_best_response,
1416 x.date_of_lost,
1417 x.date_of_death,
1418 DATEDIFF(DAY,date_of_diagnosis,date_of_lost) AS time_to_lost_followup,
1419 DATEDIFF(DAY,date_of_diagnosis,date_of_death) AS time_to_death INTO TEMP TABLE pos_neg_track_delta
1420FROM no_death_track_delta AS no_death
1421 JOIN (SELECT patient_id,
1422 MIN(date_of_lost) AS date_of_lost,
1423 MIN(date_of_death) AS date_of_death
1424 FROM (SELECT patient_id,
1425 CASE
1426 WHEN response = '4' THEN date_of_outcome
1427 ELSE NULL
1428 END AS date_of_lost,
1429 CASE
1430 WHEN response = '5' THEN date_of_outcome
1431 ELSE NULL
1432 END AS date_of_death
1433 FROM main)
1434 --this GROUP BY is very necessary
1435 GROUP BY patient_id) AS x ON x.patient_id = no_death.patient_id;
1436
1437DROP TABLE IF EXISTS negative_track_delta;
1438
1439-- find all negative time detlas and save to a banned list
1440SELECT patient_id,
1441 group_id,
1442 track_id,
1443 date_of_diagnosis,
1444 progression_number,
1445 time_to_next_progression,
1446 time_to_surgery,
1447 time_to_radiation,
1448 time_to_chemotherapy,
1449 time_to_first_response,
1450 first_response,
1451 best_response,
1452 time_to_best_response,
1453 date_of_lost,
1454 date_of_death,
1455 time_to_lost_followup,
1456 time_to_death
1457 INTO TEMP TABLE negative_track_delta
1458FROM pos_neg_track_delta
1459WHERE time_to_next_progression < 0
1460OR time_to_best_response < 0
1461OR time_to_first_response < 0
1462OR time_to_death < 0
1463OR time_to_lost_followup < 0
1464OR time_to_radiation < 0
1465OR time_to_surgery < 0
1466OR time_to_chemotherapy < 0;
1467
1468-- subtract out all neg time deltas
1469DROP TABLE IF EXISTS final_track_delta;
1470
1471-- subtract out all neg time deltas
1472CREATE TEMP TABLE final_track_delta
1473AS
1474(SELECT patient_id,
1475 group_id,
1476 track_id,
1477 date_of_diagnosis,
1478 progression_number,
1479 time_to_next_progression,
1480 time_to_surgery,
1481 time_to_radiation,
1482 time_to_chemotherapy,
1483 time_to_first_response,
1484 first_response,
1485 best_response,
1486 time_to_best_response,
1487 date_of_lost,
1488 date_of_death,
1489 time_to_lost_followup,
1490 time_to_death
1491FROM pos_neg_track_delta
1492MINUS
1493SELECT patient_id,
1494 group_id,
1495 track_id,
1496 date_of_diagnosis,
1497 progression_number,
1498 time_to_next_progression,
1499 time_to_surgery,
1500 time_to_radiation,
1501 time_to_chemotherapy,
1502 time_to_first_response,
1503 first_response,
1504 best_response,
1505 time_to_best_response,
1506 date_of_lost,
1507 date_of_death,
1508 time_to_lost_followup,
1509 time_to_death
1510FROM negative_track_delta);
1511
1512DROP TABLE IF EXISTS fact.track_delta;
1513
1514-- Moves data into the ORM created table
1515CREATE TABLE fact.track_delta
1516AS
1517(SELECT patient_id,
1518 group_id,
1519 track_id,
1520 date_of_diagnosis,
1521 progression_number,
1522 time_to_next_progression,
1523 time_to_surgery,
1524 time_to_radiation,
1525 time_to_chemotherapy,
1526 time_to_first_response,
1527 first_response,
1528 best_response,
1529 time_to_best_response,
1530 date_of_lost,
1531 date_of_death,
1532 time_to_lost_followup,
1533 time_to_death
1534FROM final_track_delta);
1535
1536GRANT ALL
1537 ON TABLE fact.track_delta
1538 TO GROUP cal_reader;
1539
1540END TRANSACTION;