Rewriting Sql files

[jpfairbanks]

We are looking at this file and improving the sql query performance https://github.com/jpfairbanks/FeatureExtraction/blob/master/inst/sql/sql_server/GetCovariates.sql

[jpfairbanks]

Here is a query for @enaga to run through the query planner

/************************************************************************\n@file GetCovariates.sql\n\nCopyright 2016 Observational Health Data Sciences and Informatics\n\nThis file is part of FeatureExtraction\n\nLicensed under the Apache License, Version 2.0 (the \"License\");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n    http://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an \"AS IS\" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n************************************************************************/\n\n \n \n\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\n\nSET search_path TO  cdm;\n\nDROP TABLE IF EXISTS  cov;\n\nDROP TABLE IF EXISTS  cov_ref;\n\nCREATE TEMP TABLE cov_ref  (\n\tcovariate_id BIGINT,\n\tcovariate_name VARCHAR(512),\n\tanalysis_id INT,\n\tconcept_id INT\n\t);\n\t\nDROP TABLE IF EXISTS  dummy;\n\nCREATE TEMP TABLE dummy  (\n\trow_id BIGINT,\n\tcovariate_id BIGINT,\n\tcovariate_value INT\n\t);\n\t\n/**************************\n***************************\nDEMOGRAPHICS\n***************************\n**************************/\n\n\n\n\n/**************************\n***************************\nCONDITION OCCURRENCE\n***************************\n**************************/\n\t\n\n\n\n/**************************\n***************************\nCONDITION ERA\n***************************\n**************************/\n\t\n\n\n\n/**************************\n***************************\nCONDITION GROUP\n***************************\n**************************/\n\n\n\n\n\n/**************************\n***************************\nDRUG EXPOSURE\n***************************\n**************************/\n\t\n/**************************\n***************************\nDRUG ERA\n***************************\n**************************/\n\t\n\n\n\n/**************************\n***************************\nDRUG GROUP\n***************************\n**************************/\n\t\n/**************************\n***************************\nPROCEDURE OCCURRENCE\n***************************\n**************************/\n\t\n\n\n/**************************\n***************************\nPROCEDURE GROUP\n***************************\n**************************/\n\n\n\n/**************************\n***************************\nOBSERVATION\n***************************\n**************************/\n\n\n\n\n\n\n\n/**************************\n***************************\nDATA DENSITY CONCEPT COUNTS\n***************************\n**************************/\n\n\n\n/**************************\n***************************\nRISK SCORES\n***************************\n**************************/\n\n\n\n\n/***********************************\n\nput all temp tables together into one cov table\n\n***********************************/\n\n\nCREATE TEMP TABLE cov_all\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n\nFROM\n\n(\n\nSELECT row_id, covariate_id, covariate_value FROM dummy\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n  \n\n\n\n) all_covariates\n;\n\n\n/**************************\n***************************\nINTERACTION YEAR\n***************************\n**************************/\n\n\n\n/**************************\n***************************\nINTERACTION MONTH\n***************************\n**************************/\n\n\n\n\nDELETE\nFROM cov_ref\nWHERE covariate_id IN (\n  \tSELECT covariate_id\n\t\tFROM cov_all\n\t\tGROUP BY covariate_id\n\t\tHAVING COUNT(row_id) <= 2\n\n\n\n\n);\n\n\nCREATE TEMP TABLE cov\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n  \nFROM\n (\n\tSELECT row_id, covariate_id, covariate_value\n\tFROM cov_all\n\tWHERE covariate_id IN (\n\t\tSELECT covariate_id\n\t\t\tFROM cov_ref\n\t\t)\n\n\n\n\n\n) t1\n;\n\nDROP TABLE IF EXISTS  cov_gender;\nDROP TABLE IF EXISTS  cov_race;\nDROP TABLE IF EXISTS  cov_ethnicity;\nDROP TABLE IF EXISTS  cov_age;\nDROP TABLE IF EXISTS  cov_year;\nDROP TABLE IF EXISTS  cov_month;\nDROP TABLE IF EXISTS  cov_co_365d;\nDROP TABLE IF EXISTS  cov_co_30d;\nDROP TABLE IF EXISTS  cov_co_inpt180d;\nDROP TABLE IF EXISTS  cov_ce_ever;\nDROP TABLE IF EXISTS  cov_ce_overlap;\nDROP TABLE IF EXISTS  cov_cg;\nDROP TABLE IF EXISTS  cov_de_365d;\nDROP TABLE IF EXISTS  cov_de_30d;\nDROP TABLE IF EXISTS  cov_dera_365d;\nDROP TABLE IF EXISTS  cov_dera_30d;\nDROP TABLE IF EXISTS  cov_dera_ever;\nDROP TABLE IF EXISTS  cov_dera_overlap;\nDROP TABLE IF EXISTS  cov_dg;\nDROP TABLE IF EXISTS  cov_dg_count;\nDROP TABLE IF EXISTS  cov_po_365d;\nDROP TABLE IF EXISTS  cov_po_30d;\nDROP TABLE IF EXISTS  cov_pg;\nDROP TABLE IF EXISTS  cov_o_365d;\nDROP TABLE IF EXISTS  cov_o_30d;\nDROP TABLE IF EXISTS  cov_m_below;\nDROP TABLE IF EXISTS  cov_m_above;\nDROP TABLE IF EXISTS  cov_m_count365d;  \nDROP TABLE IF EXISTS  cov_o_count365d;\nDROP TABLE IF EXISTS  cov_dd_cond;\nDROP TABLE IF EXISTS  cov_dd_drug;\nDROP TABLE IF EXISTS  cov_dd_proc;\nDROP TABLE IF EXISTS  cov_dd_obs;\nDROP TABLE IF EXISTS  cov_dd_visit_all;\nDROP TABLE IF EXISTS  cov_dd_visit_inpt;\nDROP TABLE IF EXISTS  cov_dd_visit_er;\nDROP TABLE IF EXISTS  cov_Charlson;\nDROP TABLE IF EXISTS  cov_DCSI;\nDROP TABLE IF EXISTS  cov_CHADS2;\nDROP TABLE IF EXISTS  cov_CHADS2VASc;\n\nDROP TABLE IF EXISTS  cov_int_year;\nDROP TABLE IF EXISTS  cov_int_month;\nDROP TABLE IF EXISTS  cov_all;\nTRUNCATE TABLE dummy;\n  DROP TABLE dummy;\n\n\n\n\n

[jpfairbanks]

"/\n@file GetCovariates.sql\n\nCopyright 2016 Observational Health Data Sciences and Informatics\n\nThis file is part of FeatureExtraction\n\nLicensed under the Apache License, Version 2.0 (the "License");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n http://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an "AS IS" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n/\n\n \n \n\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\n\nSET search_path TO cdm;\n\nDROP TABLE IF EXISTS cov;\n\nDROP TABLE IF EXISTS cov_ref;\n\nCREATE TEMP TABLE cov_ref (\n\tcovariate_id BIGINT,\n\tcovariate_name VARCHAR(512),\n\tanalysis_id INT,\n\tconcept_id INT\n\t);\n\t\nDROP TABLE IF EXISTS dummy;\n\nCREATE TEMP TABLE dummy (\n\trow_id BIGINT,\n\tcovariate_id BIGINT,\n\tcovariate_value INT\n\t);\n\t\n/\n*\nDEMOGRAPHICS\n***************************\n**************************/\n\n\n\n\n/\n*\nCONDITION OCCURRENCE\n***************************\n**************************/\n\t\n\n\n\n/\n*\nCONDITION ERA\n***************************\n**************************/\n\t\n\n\n\n/\n*\nCONDITION GROUP\n***************************\n**************************/\n\n\n\n\n\n\nDROP TABLE IF EXISTS condition_group;\n\n\nCREATE TEMP TABLE condition_group\n\nAS\nSELECT\n descendant_concept_id,\n ancestor_concept_id\n \nFROM\n\n(\n\n\nDROP TABLE IF EXISTS condition_group_meddra;\n\nCREATE TEMP TABLE condition_group_meddra\n\nAS\nSELECT\n c1.concept_id\n\nFROM\n cdm.concept c1\n \nWHERE c1.vocabulary_id = 'MedDRA'\n\n\tAND c1.concept_class_id <> 'System Organ Class'\n\tAND c1.concept_id NOT IN (36302170, 36303153, 36313966)\n\n\n\n\n;\n\t\nSELECT DISTINCT ca1.descendant_concept_id,\n\tca1.ancestor_concept_id\nFROM (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\nINNER JOIN cdm.concept_ancestor ca1\n\tON ccr1.concept_id = ca1.descendant_concept_id\nINNER JOIN condition_group_meddra c1\n\tON ca1.ancestor_concept_id = c1.concept_id\n\n\n\n\n) t1\n;\n\nINSERT INTO cov_ref (\n\tcovariate_id,\n\tcovariate_name,\n\tanalysis_id,\n\tconcept_id\n\t)\nSELECT DISTINCT CAST(cg1.ancestor_concept_id AS BIGINT) * 1000 + 50 + ccr1.analysis_id AS covariate_id,\n\tCASE\n\t\tWHEN analysis_id = 101\n\t\t\tTHEN 'Condition occurrence record observed during 365d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 102\n\t\t\tTHEN 'Condition occurrence record observed during 30d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 103\n\t\t\tTHEN 'Condition occurrence record of primary inpatient diagnosis observed during 180d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 201\n\t\t\tTHEN 'Condition era record observed during anytime on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 202\n\t\t\tTHEN 'Condition era record observed concurrent (overlapping) with cohort index within condition group: '\n\t\tELSE 'Other condition group analysis'\n\t\tEND || CAST(cg1.ancestor_concept_id AS varchar) || '-' || c1.concept_name AS covariate_name,\n\tccr1.analysis_id,\n\tcg1.ancestor_concept_id AS concept_id\nFROM (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\nINNER JOIN condition_group cg1\n\tON ccr1.concept_id = cg1.descendant_concept_id\nINNER JOIN cdm.concept c1\n\tON cg1.ancestor_concept_id = c1.concept_id;\n\n\nCREATE TEMP TABLE cov_cg\n\nAS\nSELECT\n DISTINCT cc1.row_id,\n\tCAST(cg1.ancestor_concept_id AS BIGINT) * 1000 + 50 + ccr1.analysis_id AS covariate_id,\n\t1 AS covariate_value\n\nFROM\n (\nSELECT row_id, covariate_id, covariate_value FROM dummy\n\n\n\n\n) cc1\nINNER JOIN (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\n\tON cc1.covariate_id = ccr1.covariate_id\nINNER JOIN condition_group cg1\n\tON ccr1.concept_id = cg1.descendant_concept_id\n;\n\nTRUNCATE TABLE condition_group;\n\nDROP TABLE condition_group;\n\n\n/\n*\nDRUG EXPOSURE\n***************************\n**************************/\n\t\n/\n*\nDRUG ERA\n***************************\n**************************/\n\t\n\n\n\n/\n*\nDRUG GROUP\n***************************\n**************************/\n\t\n/\n*\nPROCEDURE OCCURRENCE\n***************************\n**************************/\n\t\n\n\n/\n*\nPROCEDURE GROUP\n***************************\n**************************/\n\n\n\n/\n*\nOBSERVATION\n***************************\n**************************/\n\n\n\n\n\n\n\n/\n*\nDATA DENSITY CONCEPT COUNTS\n***************************\n**************************/\n\n\n\n/\n*\nRISK SCORES\n***************************\n**************************/\n\n\n\n\n/\n\nput all temp tables together into one cov table\n\n/\n\n\nCREATE TEMP TABLE cov_all\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n\nFROM\n\n(\n\nSELECT row_id, covariate_id, covariate_value FROM dummy\n\n\n\n\n\n\n\n\n\n\nUNION\n\nSELECT row_id, covariate_id, covariate_value\nFROM cov_cg\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n \n\n\n\n) all_covariates\n;\n\n\n/\n*\nINTERACTION YEAR\n***************************\n**************************/\n\n\n\n/\n*\nINTERACTION MONTH\n***************************\n**************************/\n\n\n\n\nDELETE\nFROM cov_ref\nWHERE covariate_id IN (\n \tSELECT covariate_id\n\t\tFROM cov_all\n\t\tGROUP BY covariate_id\n\t\tHAVING COUNT(row_id) <= 2\n\n\n\n\n);\n\n\nCREATE TEMP TABLE cov\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n \nFROM\n (\n\tSELECT row_id, covariate_id, covariate_value\n\tFROM cov_all\n\tWHERE covariate_id IN (\n\t\tSELECT covariate_id\n\t\t\tFROM cov_ref\n\t\t)\n\n\n\n\n\n) t1\n;\n\nDROP TABLE IF EXISTS cov_gender;\nDROP TABLE IF EXISTS cov_race;\nDROP TABLE IF EXISTS cov_ethnicity;\nDROP TABLE IF EXISTS cov_age;\nDROP TABLE IF EXISTS cov_year;\nDROP TABLE IF EXISTS cov_month;\nDROP TABLE IF EXISTS cov_co_365d;\nDROP TABLE IF EXISTS cov_co_30d;\nDROP TABLE IF EXISTS cov_co_inpt180d;\nDROP TABLE IF EXISTS cov_ce_ever;\nDROP TABLE IF EXISTS cov_ce_overlap;\nDROP TABLE IF EXISTS cov_cg;\nDROP TABLE IF EXISTS cov_de_365d;\nDROP TABLE IF EXISTS cov_de_30d;\nDROP TABLE IF EXISTS cov_dera_365d;\nDROP TABLE IF EXISTS cov_dera_30d;\nDROP TABLE IF EXISTS cov_dera_ever;\nDROP TABLE IF EXISTS cov_dera_overlap;\nDROP TABLE IF EXISTS cov_dg;\nDROP TABLE IF EXISTS cov_dg_count;\nDROP TABLE IF EXISTS cov_po_365d;\nDROP TABLE IF EXISTS cov_po_30d;\nDROP TABLE IF EXISTS cov_pg;\nDROP TABLE IF EXISTS cov_o_365d;\nDROP TABLE IF EXISTS cov_o_30d;\nDROP TABLE IF EXISTS cov_m_below;\nDROP TABLE IF EXISTS cov_m_above;\nDROP TABLE IF EXISTS cov_m_count365d; \nDROP TABLE IF EXISTS cov_o_count365d;\nDROP TABLE IF EXISTS cov_dd_cond;\nDROP TABLE IF EXISTS cov_dd_drug;\nDROP TABLE IF EXISTS cov_dd_proc;\nDROP TABLE IF EXISTS cov_dd_obs;\nDROP TABLE IF EXISTS cov_dd_visit_all;\nDROP TABLE IF EXISTS cov_dd_visit_inpt;\nDROP TABLE IF EXISTS cov_dd_visit_er;\nDROP TABLE IF EXISTS cov_Charlson;\nDROP TABLE IF EXISTS cov_DCSI;\nDROP TABLE IF EXISTS cov_CHADS2;\nDROP TABLE IF EXISTS cov_CHADS2VASc;\n\nDROP TABLE IF EXISTS cov_int_year;\nDROP TABLE IF EXISTS cov_int_month;\nDROP TABLE IF EXISTS cov_all;\nTRUNCATE TABLE dummy;\n DROP TABLE dummy;\n\n\n\n\n"

[jpfairbanks]

"/\n@file GetCovariates.sql\n\nCopyright 2016 Observational Health Data Sciences and Informatics\n\nThis file is part of FeatureExtraction\n\nLicensed under the Apache License, Version 2.0 (the "License");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n http://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an "AS IS" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n/\n\n \n \n\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\n\nSET search_path TO cdm;\n\nDROP TABLE IF EXISTS cov;\n\nDROP TABLE IF EXISTS cov_ref;\n\nCREATE TEMP TABLE cov_ref (\n\tcovariate_id BIGINT,\n\tcovariate_name VARCHAR(512),\n\tanalysis_id INT,\n\tconcept_id INT\n\t);\n\t\nDROP TABLE IF EXISTS dummy;\n\nCREATE TEMP TABLE dummy (\n\trow_id BIGINT,\n\tcovariate_id BIGINT,\n\tcovariate_value INT\n\t);\n\t\n/\n*\nDEMOGRAPHICS\n***************************\n**************************/\n\n\n\n\n\n--gender\nCREATE TEMP TABLE cov_gender\n\nAS\nSELECT\n cp1.subject_id AS row_id,\n\tgender_concept_id AS covariate_id,\n\t1 AS covariate_value\n\nFROM\n scratch.ftf_cohort cp1\nINNER JOIN cdm.person p1\n\tON cp1.subject_id = p1.person_id\nWHERE p1.gender_concept_id IN (\n\t\tSELECT concept_id\n\t\tFROM cdm.concept\n\t\tWHERE LOWER(concept_class_id) = 'gender'\n\t\t);\n\n\nINSERT INTO cov_ref (\n covariate_id,\n\tcovariate_name,\n\tanalysis_id,\n\tconcept_id\n\t)\nSELECT p1.covariate_id,\n\t'Gender = ' ||\n CASE WHEN v1.concept_name IS NOT NULL\n\t\t\tTHEN v1.concept_name\n\t\tELSE 'Unknown invalid concept'\n\t\tEND AS covariate_name,\n\t2 AS analysis_id,\n\tp1.covariate_id AS concept_id\nFROM (SELECT distinct covariate_id FROM cov_gender) p1\nLEFT JOIN (\n\tSELECT concept_id,\n\t\tconcept_name\n\tFROM cdm.concept\n\tWHERE LOWER(concept_class_id) = 'gender'\n\t) v1\n\tON p1.covariate_id = v1.concept_id;\n\n\n\n\n\n--race\nCREATE TEMP TABLE cov_race\n\nAS\nSELECT\n cp1.subject_id AS row_id,\n\trace_concept_id AS covariate_id,\n\t1 AS covariate_value\n \nFROM\n scratch.ftf_cohort cp1\nINNER JOIN cdm.person p1\n\tON cp1.subject_id = p1.person_id\nWHERE p1.race_concept_id IN (\n\t\tSELECT concept_id\n\t\tFROM cdm.concept\n\t\tWHERE LOWER(concept_class_id) = 'race'\n\t\t);\n\n\nINSERT INTO cov_ref (\n covariate_id,\n covariate_name,\n\tanalysis_id,\n\tconcept_id\n\t)\nSELECT p1.covariate_id,\n\t'Race = ' || CASE WHEN v1.concept_name IS NOT NULL\n \t\tTHEN v1.concept_name\n\t\tELSE 'Unknown invalid concept'\n\t\tEND AS covariate_name,\n\t3 AS analysis_id,\n\tp1.covariate_id AS concept_id\nFROM (SELECT distinct covariate_id FROM cov_race) p1\nLEFT JOIN (\n\tSELECT concept_id,\n\t\tconcept_name\n\tFROM cdm.concept\n\tWHERE LOWER(concept_class_id) = 'race'\n\t) v1\n\tON p1.covariate_id = v1.concept_id;\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n/\n*\nCONDITION OCCURRENCE\n***************************\n**************************/\n\t\n\n\n\n/\n*\nCONDITION ERA\n***************************\n**************************/\n\t\n\n\n\n/\n*\nCONDITION GROUP\n***************************\n**************************/\n\n\n\n\n\n\nDROP TABLE IF EXISTS condition_group;\n\n\nCREATE TEMP TABLE condition_group\n\nAS\nSELECT\n descendant_concept_id,\n ancestor_concept_id\n \nFROM\n\n(\n\n\nSELECT DISTINCT ca1.descendant_concept_id,\n\tca1.ancestor_concept_id\nFROM (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\nINNER JOIN cdm.concept_ancestor ca1\n\tON ccr1.concept_id = ca1.descendant_concept_id\nINNER JOIN cdm.concept c1\n\tON ca1.ancestor_concept_id = c1.concept_id\n \nWHERE c1.vocabulary_id = 'MedDRA'\n\n\tAND c1.concept_class_id <> 'System Organ Class'\n\tAND c1.concept_id NOT IN (36302170, 36303153, 36313966)\n\n\n\n\n\n\n\n\n) t1\n;\n\nINSERT INTO cov_ref (\n\tcovariate_id,\n\tcovariate_name,\n\tanalysis_id,\n\tconcept_id\n\t)\nSELECT DISTINCT CAST(cg1.ancestor_concept_id AS BIGINT) * 1000 + 50 + ccr1.analysis_id AS covariate_id,\n\tCASE\n\t\tWHEN analysis_id = 101\n\t\t\tTHEN 'Condition occurrence record observed during 365d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 102\n\t\t\tTHEN 'Condition occurrence record observed during 30d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 103\n\t\t\tTHEN 'Condition occurrence record of primary inpatient diagnosis observed during 180d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 201\n\t\t\tTHEN 'Condition era record observed during anytime on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 202\n\t\t\tTHEN 'Condition era record observed concurrent (overlapping) with cohort index within condition group: '\n\t\tELSE 'Other condition group analysis'\n\t\tEND || CAST(cg1.ancestor_concept_id AS varchar) || '-' || c1.concept_name AS covariate_name,\n\tccr1.analysis_id,\n\tcg1.ancestor_concept_id AS concept_id\nFROM (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\nINNER JOIN condition_group cg1\n\tON ccr1.concept_id = cg1.descendant_concept_id\nINNER JOIN cdm.concept c1\n\tON cg1.ancestor_concept_id = c1.concept_id;\n\n\nCREATE TEMP TABLE cov_cg\n\nAS\nSELECT\n DISTINCT cc1.row_id,\n\tCAST(cg1.ancestor_concept_id AS BIGINT) * 1000 + 50 + ccr1.analysis_id AS covariate_id,\n\t1 AS covariate_value\n\nFROM\n (\nSELECT row_id, covariate_id, covariate_value FROM dummy\n\n\n\n\n) cc1\nINNER JOIN (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\n\tON cc1.covariate_id = ccr1.covariate_id\nINNER JOIN condition_group cg1\n\tON ccr1.concept_id = cg1.descendant_concept_id\n;\n\nTRUNCATE TABLE condition_group;\n\nDROP TABLE condition_group;\n\n\n/\n*\nDRUG EXPOSURE\n***************************\n**************************/\n\t\n/\n*\nDRUG ERA\n***************************\n**************************/\n\t\n\n\n\n/\n*\nDRUG GROUP\n***************************\n**************************/\n\t\n/\n*\nPROCEDURE OCCURRENCE\n***************************\n**************************/\n\t\n\n\n/\n*\nPROCEDURE GROUP\n***************************\n**************************/\n\n\n\n/\n*\nOBSERVATION\n***************************\n**************************/\n\n\n\n\n\n\n\n/\n*\nDATA DENSITY CONCEPT COUNTS\n***************************\n**************************/\n\n\n\n/\n*\nRISK SCORES\n***************************\n**************************/\n\n\n\n\n/\n\nput all temp tables together into one cov table\n\n/\n\n\nCREATE TEMP TABLE cov_all\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n\nFROM\n\n(\n\nSELECT row_id, covariate_id, covariate_value FROM dummy\n\n\n\n\nUNION\n\nSELECT row_id, covariate_id, covariate_value\nFROM cov_gender\n\n\n\n\nUNION\n\nSELECT row_id, covariate_id, covariate_value\nFROM cov_race\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\nUNION\n\nSELECT row_id, covariate_id, covariate_value\nFROM cov_cg\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n \n\n\n\n) all_covariates\n;\n\n\n/\n*\nINTERACTION YEAR\n***************************\n**************************/\n\n\n\n/\n*\nINTERACTION MONTH\n***************************\n**************************/\n\n\n\n\nDELETE\nFROM cov_ref\nWHERE covariate_id IN (\n \tSELECT covariate_id\n\t\tFROM cov_all\n\t\tGROUP BY covariate_id\n\t\tHAVING COUNT(row_id) <= 2\n\n\n\n\n);\n\n\nCREATE TEMP TABLE cov\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n \nFROM\n (\n\tSELECT row_id, covariate_id, covariate_value\n\tFROM cov_all\n\tWHERE covariate_id IN (\n\t\tSELECT covariate_id\n\t\t\tFROM cov_ref\n\t\t)\n\n\n\n\n\n) t1\n;\n\nDROP TABLE IF EXISTS cov_gender;\nDROP TABLE IF EXISTS cov_race;\nDROP TABLE IF EXISTS cov_ethnicity;\nDROP TABLE IF EXISTS cov_age;\nDROP TABLE IF EXISTS cov_year;\nDROP TABLE IF EXISTS cov_month;\nDROP TABLE IF EXISTS cov_co_365d;\nDROP TABLE IF EXISTS cov_co_30d;\nDROP TABLE IF EXISTS cov_co_inpt180d;\nDROP TABLE IF EXISTS cov_ce_ever;\nDROP TABLE IF EXISTS cov_ce_overlap;\nDROP TABLE IF EXISTS cov_cg;\nDROP TABLE IF EXISTS cov_de_365d;\nDROP TABLE IF EXISTS cov_de_30d;\nDROP TABLE IF EXISTS cov_dera_365d;\nDROP TABLE IF EXISTS cov_dera_30d;\nDROP TABLE IF EXISTS cov_dera_ever;\nDROP TABLE IF EXISTS cov_dera_overlap;\nDROP TABLE IF EXISTS cov_dg;\nDROP TABLE IF EXISTS cov_dg_count;\nDROP TABLE IF EXISTS cov_po_365d;\nDROP TABLE IF EXISTS cov_po_30d;\nDROP TABLE IF EXISTS cov_pg;\nDROP TABLE IF EXISTS cov_o_365d;\nDROP TABLE IF EXISTS cov_o_30d;\nDROP TABLE IF EXISTS cov_m_below;\nDROP TABLE IF EXISTS cov_m_above;\nDROP TABLE IF EXISTS cov_m_count365d; \nDROP TABLE IF EXISTS cov_o_count365d;\nDROP TABLE IF EXISTS cov_dd_cond;\nDROP TABLE IF EXISTS cov_dd_drug;\nDROP TABLE IF EXISTS cov_dd_proc;\nDROP TABLE IF EXISTS cov_dd_obs;\nDROP TABLE IF EXISTS cov_dd_visit_all;\nDROP TABLE IF EXISTS cov_dd_visit_inpt;\nDROP TABLE IF EXISTS cov_dd_visit_er;\nDROP TABLE IF EXISTS cov_Charlson;\nDROP TABLE IF EXISTS cov_DCSI;\nDROP TABLE IF EXISTS cov_CHADS2;\nDROP TABLE IF EXISTS cov_CHADS2VASc;\n\nDROP TABLE IF EXISTS cov_int_year;\nDROP TABLE IF EXISTS cov_int_month;\nDROP TABLE IF EXISTS cov_all;\nTRUNCATE TABLE dummy;\n DROP TABLE dummy;\n\n\n\n\n"
|=====================================================================================

[jpfairbanks]

[1] "This is a test of accessing the database"
Connecting using Redshift driver
[1] "done with query"
Constructing default covariates
[1] "/\n@file GetCovariates.sql\n\nCopyright 2016 Observational Health Data Sciences and Informatics\n\nThis file is part of FeatureExtraction\n\nLicensed under the Apache License, Version 2.0 (the "License");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n http://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an "AS IS" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n/\n\n \n \n\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\n\nSET search_path TO cdm;\n\nDROP TABLE IF EXISTS cov;\n\nDROP TABLE IF EXISTS cov_ref;\n\nCREATE TEMP TABLE cov_ref (\n\tcovariate_id BIGINT,\n\tcovariate_name VARCHAR(512),\n\tanalysis_id INT,\n\tconcept_id INT\n\t);\n\t\nDROP TABLE IF EXISTS dummy;\n\nCREATE TEMP TABLE dummy (\n\trow_id BIGINT,\n\tcovariate_id BIGINT,\n\tcovariate_value INT\n\t);\n\t\n/\n*\nDEMOGRAPHICS\n***************************\n**************************/\n\n\n\n\n/\n*\nCONDITION OCCURRENCE\n***************************\n**************************/\n\t\n\n\n\n/\n*\nCONDITION ERA\n***************************\n**************************/\n\t\n\n\n\n/\n*\nCONDITION GROUP\n***************************\n**************************/\n\n\n\n\n\n\nDROP TABLE IF EXISTS condition_group;\n\n\nCREATE TEMP TABLE condition_group\n\nAS\nSELECT\n descendant_concept_id,\n ancestor_concept_id\n \nFROM\n\n(\n\n\nSELECT DISTINCT ca1.descendant_concept_id,\n\tca1.ancestor_concept_id\nFROM (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\nINNER JOIN cdm.concept_ancestor ca1\n\tON ccr1.concept_id = ca1.descendant_concept_id\nINNER JOIN cdm.concept c1\n\tON ca1.ancestor_concept_id = c1.concept_id\n \nWHERE c1.vocabulary_id = 'MedDRA'\n\n\tAND c1.concept_class_id <> 'System Organ Class'\n\tAND c1.concept_id NOT IN (36302170, 36303153, 36313966)\n\n\n\n\n\n\n\n\n) t1\n;\n\nINSERT INTO cov_ref (\n\tcovariate_id,\n\tcovariate_name,\n\tanalysis_id,\n\tconcept_id\n\t)\nSELECT DISTINCT CAST(cg1.ancestor_concept_id AS BIGINT) * 1000 + 50 + ccr1.analysis_id AS covariate_id,\n\tCASE\n\t\tWHEN analysis_id = 101\n\t\t\tTHEN 'Condition occurrence record observed during 365d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 102\n\t\t\tTHEN 'Condition occurrence record observed during 30d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 103\n\t\t\tTHEN 'Condition occurrence record of primary inpatient diagnosis observed during 180d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 201\n\t\t\tTHEN 'Condition era record observed during anytime on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 202\n\t\t\tTHEN 'Condition era record observed concurrent (overlapping) with cohort index within condition group: '\n\t\tELSE 'Other condition group analysis'\n\t\tEND || CAST(cg1.ancestor_concept_id AS varchar) || '-' || c1.concept_name AS covariate_name,\n\tccr1.analysis_id,\n\tcg1.ancestor_concept_id AS concept_id\nFROM (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\nINNER JOIN condition_group cg1\n\tON ccr1.concept_id = cg1.descendant_concept_id\nINNER JOIN cdm.concept c1\n\tON cg1.ancestor_concept_id = c1.concept_id;\n\n\nCREATE TEMP TABLE cov_cg\n\nAS\nSELECT\n DISTINCT cc1.row_id,\n\tCAST(cg1.ancestor_concept_id AS BIGINT) * 1000 + 50 + ccr1.analysis_id AS covariate_id,\n\t1 AS covariate_value\n\nFROM\n (\nSELECT row_id, covariate_id, covariate_value FROM dummy\n\n\n\n\n) cc1\nINNER JOIN (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\n\tON cc1.covariate_id = ccr1.covariate_id\nINNER JOIN condition_group cg1\n\tON ccr1.concept_id = cg1.descendant_concept_id\n;\n\nTRUNCATE TABLE condition_group;\n\nDROP TABLE condition_group;\n\n\n/\n*\nDRUG EXPOSURE\n***************************\n**************************/\n\t\n/\n*\nDRUG ERA\n***************************\n**************************/\n\t\n\n\n\n/\n*\nDRUG GROUP\n***************************\n**************************/\n\t\n/\n*\nPROCEDURE OCCURRENCE\n***************************\n**************************/\n\t\n\n\n/\n*\nPROCEDURE GROUP\n***************************\n**************************/\n\n\n\n/\n*\nOBSERVATION\n***************************\n**************************/\n\n\n\n\n\n\n\n/\n*\nDATA DENSITY CONCEPT COUNTS\n***************************\n**************************/\n\n\n\n/\n*\nRISK SCORES\n***************************\n**************************/\n\n\n\n\n/\n\nput all temp tables together into one cov table\n\n/\n\n\nCREATE TEMP TABLE cov_all\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n\nFROM\n\n(\n\nSELECT row_id, covariate_id, covariate_value FROM dummy\n\n\n\n\n\n\n\n\n\n\nUNION\n\nSELECT row_id, covariate_id, covariate_value\nFROM cov_cg\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n \n\n\n\n) all_covariates\n;\n\n\n/\n*\nINTERACTION YEAR\n***************************\n**************************/\n\n\n\n/\n*\nINTERACTION MONTH\n***************************\n**************************/\n\n\n\n\nDELETE\nFROM cov_ref\nWHERE covariate_id IN (\n \tSELECT covariate_id\n\t\tFROM cov_all\n\t\tGROUP BY covariate_id\n\t\tHAVING COUNT(row_id) <= 2\n\n\n\n\n);\n\n\nCREATE TEMP TABLE cov\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n \nFROM\n (\n\tSELECT row_id, covariate_id, covariate_value\n\tFROM cov_all\n\tWHERE covariate_id IN (\n\t\tSELECT covariate_id\n\t\t\tFROM cov_ref\n\t\t)\n\n\n\n\n\n) t1\n;\n\nDROP TABLE IF EXISTS cov_gender;\nDROP TABLE IF EXISTS cov_race;\nDROP TABLE IF EXISTS cov_ethnicity;\nDROP TABLE IF EXISTS cov_age;\nDROP TABLE IF EXISTS cov_year;\nDROP TABLE IF EXISTS cov_month;\nDROP TABLE IF EXISTS cov_co_365d;\nDROP TABLE IF EXISTS cov_co_30d;\nDROP TABLE IF EXISTS cov_co_inpt180d;\nDROP TABLE IF EXISTS cov_ce_ever;\nDROP TABLE IF EXISTS cov_ce_overlap;\nDROP TABLE IF EXISTS cov_cg;\nDROP TABLE IF EXISTS cov_de_365d;\nDROP TABLE IF EXISTS cov_de_30d;\nDROP TABLE IF EXISTS cov_dera_365d;\nDROP TABLE IF EXISTS cov_dera_30d;\nDROP TABLE IF EXISTS cov_dera_ever;\nDROP TABLE IF EXISTS cov_dera_overlap;\nDROP TABLE IF EXISTS cov_dg;\nDROP TABLE IF EXISTS cov_dg_count;\nDROP TABLE IF EXISTS cov_po_365d;\nDROP TABLE IF EXISTS cov_po_30d;\nDROP TABLE IF EXISTS cov_pg;\nDROP TABLE IF EXISTS cov_o_365d;\nDROP TABLE IF EXISTS cov_o_30d;\nDROP TABLE IF EXISTS cov_m_below;\nDROP TABLE IF EXISTS cov_m_above;\nDROP TABLE IF EXISTS cov_m_count365d; \nDROP TABLE IF EXISTS cov_o_count365d;\nDROP TABLE IF EXISTS cov_dd_cond;\nDROP TABLE IF EXISTS cov_dd_drug;\nDROP TABLE IF EXISTS cov_dd_proc;\nDROP TABLE IF EXISTS cov_dd_obs;\nDROP TABLE IF EXISTS cov_dd_visit_all;\nDROP TABLE IF EXISTS cov_dd_visit_inpt;\nDROP TABLE IF EXISTS cov_dd_visit_er;\nDROP TABLE IF EXISTS cov_Charlson;\nDROP TABLE IF EXISTS cov_DCSI;\nDROP TABLE IF EXISTS cov_CHADS2;\nDROP TABLE IF EXISTS cov_CHADS2VASc;\n\nDROP TABLE IF EXISTS cov_int_year;\nDROP TABLE IF EXISTS cov_int_month;\nDROP TABLE IF EXISTS cov_all;\nTRUNCATE TABLE dummy;\n DROP TABLE dummy;\n\n\n\n\n"

[jpfairbanks]

onstructing default covariates
[1] "/\n@file GetCovariates.sql\n\nCopyright 2016 Observational Health Data Sciences and Informatics\n\nThis file is part of FeatureExtraction\n\nLicensed under the Apache License, Version 2.0 (the "License");\nyou may not use this file except in compliance with the License.\nYou may obtain a copy of the License at\n\n http://www.apache.org/licenses/LICENSE-2.0\n\nUnless required by applicable law or agreed to in writing, software\ndistributed under the License is distributed on an "AS IS" BASIS,\nWITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\nSee the License for the specific language governing permissions and\nlimitations under the License.\n/\n\n \n \n\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\n\nSET search_path TO cdm;\n\nDROP TABLE IF EXISTS cov;\n\nDROP TABLE IF EXISTS cov_ref;\n\nCREATE TEMP TABLE cov_ref (\n\tcovariate_id BIGINT,\n\tcovariate_name VARCHAR(512),\n\tanalysis_id INT,\n\tconcept_id INT\n\t);\n\t\nDROP TABLE IF EXISTS dummy;\n\nCREATE TEMP TABLE dummy (\n\trow_id BIGINT,\n\tcovariate_id BIGINT,\n\tcovariate_value INT\n\t);\n\t\n/\n*\nDEMOGRAPHICS\n***************************\n**************************/\n\n\n\n\n/\n*\nCONDITION OCCURRENCE\n***************************\n**************************/\n\t\n\n\n\n/\n*\nCONDITION ERA\n***************************\n**************************/\n\t\n\n\n\n/\n*\nCONDITION GROUP\n***************************\n**************************/\n\n\n\n\n\n\nDROP TABLE IF EXISTS condition_group;\n\n\nCREATE TEMP TABLE condition_group\n\nAS\nSELECT\n descendant_concept_id,\n ancestor_concept_id\n \nFROM\n\n(\n\n\nDROP TABLE IF EXISTS condition_group_meddra;\n\nSELECT c1.concept_id\nINTO condition_group_meddra\nFROM cdm.concept c1\n \nWHERE c1.vocabulary_id = 'MedDRA'\n\n\tAND c1.concept_class_id <> 'System Organ Class'\n\tAND c1.concept_id NOT IN (36302170, 36303153, 36313966)\n\n\n\n\n\nSELECT DISTINCT ca1.descendant_concept_id,\n\tca1.ancestor_concept_id\nFROM (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\nINNER JOIN cdm.concept_ancestor ca1\n\tON ccr1.concept_id = ca1.descendant_concept_id\nINNER JOIN condition_group_meddra c1\n\tON ca1.ancestor_concept_id = c1.concept_id\n\n\n\n\n) t1\n;\n\nINSERT INTO cov_ref (\n\tcovariate_id,\n\tcovariate_name,\n\tanalysis_id,\n\tconcept_id\n\t)\nSELECT DISTINCT CAST(cg1.ancestor_concept_id AS BIGINT) * 1000 + 50 + ccr1.analysis_id AS covariate_id,\n\tCASE\n\t\tWHEN analysis_id = 101\n\t\t\tTHEN 'Condition occurrence record observed during 365d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 102\n\t\t\tTHEN 'Condition occurrence record observed during 30d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 103\n\t\t\tTHEN 'Condition occurrence record of primary inpatient diagnosis observed during 180d on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 201\n\t\t\tTHEN 'Condition era record observed during anytime on or prior to cohort index within condition group: '\n\t\tWHEN analysis_id = 202\n\t\t\tTHEN 'Condition era record observed concurrent (overlapping) with cohort index within condition group: '\n\t\tELSE 'Other condition group analysis'\n\t\tEND || CAST(cg1.ancestor_concept_id AS varchar) || '-' || c1.concept_name AS covariate_name,\n\tccr1.analysis_id,\n\tcg1.ancestor_concept_id AS concept_id\nFROM (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\nINNER JOIN condition_group cg1\n\tON ccr1.concept_id = cg1.descendant_concept_id\nINNER JOIN cdm.concept c1\n\tON cg1.ancestor_concept_id = c1.concept_id;\n\n\nCREATE TEMP TABLE cov_cg\n\nAS\nSELECT\n DISTINCT cc1.row_id,\n\tCAST(cg1.ancestor_concept_id AS BIGINT) * 1000 + 50 + ccr1.analysis_id AS covariate_id,\n\t1 AS covariate_value\n\nFROM\n (\nSELECT row_id, covariate_id, covariate_value FROM dummy\n\n\n\n\n) cc1\nINNER JOIN (\n\tSELECT covariate_id,\n\t\tcovariate_name,\n\t\tanalysis_id,\n\t\tconcept_id\n\tFROM cov_ref\n\tWHERE analysis_id > 100\n\t\tAND analysis_id < 300\n\t) ccr1\n\tON cc1.covariate_id = ccr1.covariate_id\nINNER JOIN condition_group cg1\n\tON ccr1.concept_id = cg1.descendant_concept_id\n;\n\nTRUNCATE TABLE condition_group;\n\nDROP TABLE condition_group;\n\n\n/\n*\nDRUG EXPOSURE\n***************************\n**************************/\n\t\n/\n*\nDRUG ERA\n***************************\n**************************/\n\t\n\n\n\n/\n*\nDRUG GROUP\n***************************\n**************************/\n\t\n/\n*\nPROCEDURE OCCURRENCE\n***************************\n**************************/\n\t\n\n\n/\n*\nPROCEDURE GROUP\n***************************\n**************************/\n\n\n\n/\n*\nOBSERVATION\n***************************\n**************************/\n\n\n\n\n\n\n\n/\n*\nDATA DENSITY CONCEPT COUNTS\n***************************\n**************************/\n\n\n\n/\n*\nRISK SCORES\n***************************\n**************************/\n\n\n\n\n/\n\nput all temp tables together into one cov table\n\n/\n\n\nCREATE TEMP TABLE cov_all\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n\nFROM\n\n(\n\nSELECT row_id, covariate_id, covariate_value FROM dummy\n\n\n\n\n\n\n\n\n\n\nUNION\n\nSELECT row_id, covariate_id, covariate_value\nFROM cov_cg\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n \n\n\n\n) all_covariates\n;\n\n\n/\n*\nINTERACTION YEAR\n***************************\n**************************/\n\n\n\n/\n*\nINTERACTION MONTH\n***************************\n**************************/\n\n\n\n\nDELETE\nFROM cov_ref\nWHERE covariate_id IN (\n \tSELECT covariate_id\n\t\tFROM cov_all\n\t\tGROUP BY covariate_id\n\t\tHAVING COUNT(row_id) <= 2\n\n\n\n\n);\n\n\nCREATE TEMP TABLE cov\n\nAS\nSELECT\n row_id, covariate_id, covariate_value\n \nFROM\n (\n\tSELECT row_id, covariate_id, covariate_value\n\tFROM cov_all\n\tWHERE covariate_id IN (\n\t\tSELECT covariate_id\n\t\t\tFROM cov_ref\n\t\t)\n\n\n\n\n\n) t1\n;\n\nDROP TABLE IF EXISTS cov_gender;\nDROP TABLE IF EXISTS cov_race;\nDROP TABLE IF EXISTS cov_ethnicity;\nDROP TABLE IF EXISTS cov_age;\nDROP TABLE IF EXISTS cov_year;\nDROP TABLE IF EXISTS cov_month;\nDROP TABLE IF EXISTS cov_co_365d;\nDROP TABLE IF EXISTS cov_co_30d;\nDROP TABLE IF EXISTS cov_co_inpt180d;\nDROP TABLE IF EXISTS cov_ce_ever;\nDROP TABLE IF EXISTS cov_ce_overlap;\nDROP TABLE IF EXISTS cov_cg;\nDROP TABLE IF EXISTS cov_de_365d;\nDROP TABLE IF EXISTS cov_de_30d;\nDROP TABLE IF EXISTS cov_dera_365d;\nDROP TABLE IF EXISTS cov_dera_30d;\nDROP TABLE IF EXISTS cov_dera_ever;\nDROP TABLE IF EXISTS cov_dera_overlap;\nDROP TABLE IF EXISTS cov_dg;\nDROP TABLE IF EXISTS cov_dg_count;\nDROP TABLE IF EXISTS cov_po_365d;\nDROP TABLE IF EXISTS cov_po_30d;\nDROP TABLE IF EXISTS cov_pg;\nDROP TABLE IF EXISTS cov_o_365d;\nDROP TABLE IF EXISTS cov_o_30d;\nDROP TABLE IF EXISTS cov_m_below;\nDROP TABLE IF EXISTS cov_m_above;\nDROP TABLE IF EXISTS cov_m_count365d; \nDROP TABLE IF EXISTS cov_o_count365d;\nDROP TABLE IF EXISTS cov_dd_cond;\nDROP TABLE IF EXISTS cov_dd_drug;\nDROP TABLE IF EXISTS cov_dd_proc;\nDROP TABLE IF EXISTS cov_dd_obs;\nDROP TABLE IF EXISTS cov_dd_visit_all;\nDROP TABLE IF EXISTS cov_dd_visit_inpt;\nDROP TABLE IF EXISTS cov_dd_visit_er;\nDROP TABLE IF EXISTS cov_Charlson;\nDROP TABLE IF EXISTS cov_DCSI;\nDROP TABLE IF EXISTS cov_CHADS2;\nDROP TABLE IF EXISTS cov_CHADS2VASc;\n\nDROP TABLE IF EXISTS cov_int_year;\nDROP TABLE IF EXISTS cov_int_month;\nDROP TABLE IF EXISTS cov_all;\nTRUNCATE TABLE dummy;\n DROP TABLE dummy;\n\n\n\n\n"
|================