Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Query Verification: Steroid Induced Osteonecrosis Cohort #107

Open
callahantiff opened this issue Jul 22, 2019 · 15 comments
Open

Query Verification: Steroid Induced Osteonecrosis Cohort #107

callahantiff opened this issue Jul 22, 2019 · 15 comments
Assignees
@callahantiff
Labels
help wanted Extra attention is needed
Projects
Coding Tasks
Milestone
PheKnowVec - Init...

Comments

@callahantiff
Copy link
Owner

callahantiff commented Jul 22, 2019 •
edited
Loading

@mgkahn - Can you please help me verify the query to select Steroid Induced Osteonecrosis patients?

COHORT CRITERIA
Case Criteria:


Control Criteria:


Cohort Logic Table

COHORT CHUNK LOGICAL OPERATOR
CASE rx_case_inclusion_criteria_1 AND
CASE dx_case_inclusion_criteria_1 OR
CASE all_case_inclusion_criteria_1 AND
CASE dx_case_exclusion_criteria_1 AND
CASE dx_case_exclusion_criteria_2 AND
CASE dx_case_exclusion_criteria_3 AND
CASE all_case_exclusion_criteria_1 AND
CASE all_case_exclusion_criteria_2 AND
CASE all_case_exclusion_criteria_3 ---
CONTROL rx_control_inclusion_criteria_1 AND
CONTROL rx_control_exclusion_criteria_1 AND
CONTROL dx_control_exclusion_criteria_1 AND
CONTROL dx_control_exclusion_criteria_2 AND
CONTROL dx_control_exclusion_criteria_3 AND
CONTROL all_control_exclusion_criteria_1 AND
CONTROL all_control_exclusion_criteria_2 AND
CONTROL all_control_exclusion_criteria_3 ---

NOTE.

  1. Replace all instances of {database} with CHCO_DeID_Oct2018
  2. Comment out all instances of {code_set_group}

Query can be found here and is also included below:

WITH rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE de.route_concept_id IN (4132161, 4171047, 4302612)
  AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 1
  AND cohort.standard_code_set = {code_set_group}
GROUP BY
  de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_start_datetime), day) >= 14
),

dx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
  {database}.drug_exposure de,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort,
(SELECT co.person_id, min(DATETIME(co.condition_start_datetime)) AS cond_start_date
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 8
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, co.condition_concept_id
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1) cont
WHERE
  cont.person_id = de.person_id
  AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 1
  AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime, cont.cond_start_date
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND cont.cond_start_date >= min(DATETIME(de.drug_exposure_start_datetime))
  AND cont.cond_start_date <= DATETIME_ADD(DATETIME(max(de.drug_exposure_end_datetime)), INTERVAL 1 YEAR)
),

all_case_inclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

dx_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 2
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

dx_case_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 3
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

dx_case_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 4
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

all_case_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

all_case_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

all_case_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

rx_control_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE de.route_concept_id IN (4132161, 4171047, 4302612)
  AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 10
  AND cohort.standard_code_set = {code_set_group}
GROUP BY
  de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_start_datetime), day) >= 14
),

rx_control_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 11
  AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dx_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 12
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 13
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

dx_control_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 14
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

all_control_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

all_control_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

all_control_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
))

SELECT * FROM
  (SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CASE' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM rx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      UNION DISTINCT 
     SELECT person_id, code_set FROM all_case_inclusion_criteria_1))
       EXCEPT DISTINCT
     (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_case_exclusion_criteria_3
       UNION DISTINCT
      SELECT person_id, code_set FROM all_case_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM all_case_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id, code_set FROM all_case_exclusion_criteria_3))
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL
  
  (SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM rx_control_inclusion_criteria_1)
       EXCEPT DISTINCT
     (SELECT person_id, code_set FROM rx_control_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_control_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_control_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_control_exclusion_criteria_3
       UNION DISTINCT
      SELECT person_id, code_set FROM all_control_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM all_control_exclusion_criteria_2
      UNION DISTINCT
      SELECT person_id, code_set FROM all_control_exclusion_criteria_3))

-- making sure no control patient is also a case
WHERE person_id NOT IN (
    (SELECT person_id FROM rx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     (SELECT person_id FROM dx_case_inclusion_criteria_1
      UNION DISTINCT 
     SELECT person_id FROM all_case_inclusion_criteria_1))
       EXCEPT DISTINCT
     (SELECT person_id FROM dx_case_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id FROM dx_case_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id FROM dx_case_exclusion_criteria_3
       UNION DISTINCT
      SELECT person_id FROM all_case_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id FROM all_case_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id FROM all_case_exclusion_criteria_3))
   GROUP BY person_id, code_set, cohort_type)
;
;
@callahantiff callahantiff added the help wanted Extra attention is needed label Jul 22, 2019
@callahantiff callahantiff self-assigned this Jul 22, 2019
@callahantiff callahantiff added this to Needed Scripts in Coding Tasks via automation Jul 22, 2019
@callahantiff callahantiff mentioned this issue Jul 29, 2019
Closed
@mgkahn
Copy link
Collaborator

mgkahn commented Aug 11, 2019

rx_case_inclusion_criteria_1:

  • You don't need "de.route_concept_id = c.concept_id" because you never use anything from the concept table for the route. You've already decoded the routes that you want into concept_ids.
  • While it doesn't change anything analytically, it probably would be easier to debug if you did your group by order_date, end_date (rather than currently end_date, order_date)so that the list would be sorted first by order date.
  • You are using "raw" drug orders. If you created and used drug_eras instead, you may get more intervals that are longer than 14 days because eras "merge" shorter intervals together. So, while an individual order may not meet 14 days, a drug era might. You would need to run the OHDSI provided algorithm for drug era. Jonathan Derkermajian can help you navigate this code if you wish to try it out.

@mgkahn
Copy link
Collaborator

mgkahn commented Aug 11, 2019

dx_case_inclusion_criteria_1:
Probably correct but checking -- your query only looks at the FIRST Dx [min(condition_starttime)], which may have occurred before the steroid time interval. You would not pick up instances of the condition that did occur within the steroid time interval. Thinking clinically, I think this is right -- if there is a Dx of necrosis BEFORE the steroid, then it is hard to call this steroid induced necrosis. So I think the logic you have here is right but just wanted to be explicit that this is what your logic is doing.

@mgkahn
Copy link
Collaborator

mgkahn commented Aug 11, 2019 •
edited
Loading

all_case_inclusion_criteria_1

  • Does this run successfully? Your UNIONS return only person_ids but your outer SELECT returns person_id and code_set_group. I just don't know if this runs. If not, you may need to add {code_set_group} to each of your subqueries so they return person_id, {code_set_group}.
  • You use cohort.phenotype_definition_number =9 for all subqueries, which use concept_codes from many different OMOP tables/domains. This should be fine since you will only match on concept_codes for the domain that matches the table domain. In other queries, you used different cohort.phenotype_definition_numbers for each data domain. I think what you have here is fine since the right domain codes will be found in the right tables (and you'll pick up any codes that are being stored in the wrong table!).
  • BTW: you do the same in all_case_exclusion_criteria_1 and many others. Just check that the query runs correctly or if it needs to be adjusted as I mention in the first bullet. It's an easy fix if needed.

@mgkahn
Copy link
Collaborator

mgkahn commented Aug 11, 2019

Remaining all_case_inclusion -- I see you used the same "shell" that looks at conditions, medications, observations, procedures even when the code set probably is "looking" only at a single domain (e.g. conditions). This is fine even if inefficient and more expensive. I suspect folks not using GBQ would object to such brute force queries. But it does work since the concept codes will only match in the right domains.

@mgkahn
Copy link
Collaborator

mgkahn commented Aug 11, 2019

rx_control_inclusion_criteria_1

  • same comment as previously about possibly using drug_era rather than drug_exposure events for 14d exposure duration.
  • same comment about not needs de.route_concept_id = c.concept_id JOIN since you don't use anything from the concept table for this field.

@mgkahn
Copy link
Collaborator

mgkahn commented Aug 11, 2019

Overall logic: I think you have the logic for exclusions wrong for both cases and controls. Any exclusion removes a patient. So you need to "OR" (= UNION DISTINCT) all of the exclusions so that a patient_id that appears in ANY exclusion is included in your "EXCEPT DISTINCT" subquery. As currently written, a patient would only be excluded if they met ALL exclusion criteria.

@mgkahn
Copy link
Collaborator

mgkahn commented Aug 11, 2019

End of mgk comments on steroid induced osteonecrosis

@callahantiff callahantiff mentioned this issue Aug 11, 2019
Open
2 tasks
@callahantiff
Copy link
Owner Author

callahantiff commented Aug 11, 2019 •
edited
Loading

rx_case_inclusion_criteria_1:

  • You don't need "de.route_concept_id = c.concept_id" because you never use anything from the concept table for the route. You've already decoded the routes that you want into concept_ids.

Good point, I will remove that.

  • While it doesn't change anything analytically, it probably would be easier to debug if you did your group by order_date, end_date (rather than currently end_date, order_date)so that the list would be sorted first by order date.

OK, I will update accordingly.

  • You are using "raw" drug orders. If you created and used drug_eras instead, you may get more intervals that are longer than 14 days because eras "merge" shorter intervals together. So, while an individual order may not meet 14 days, a drug era might. You would need to run the OHDSI provided algorithm for drug era. Jonathan Derkermajian can help you navigate this code if you wish to try it out.

That's a great idea! I wonder if it also makes sense to do that for conditions too? I won't have that in time for our AMIA Informatics Summit, but I can adjust the queries and implement this afterwards. I created issue #114

For rx_case_inclusion_criteria_1 and rx_control_inclusion_criteria_1:

WITH rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.concept c, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE  de.route_concept_id IN (4132161, 4171047, 4302612)
  AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 1
  AND cohort.standard_code_set = {code_set_group}
GROUP BY
  de.person_id, code_set, de.drug_exposure_order_datetime, de.drug_exposure_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_order_datetime), day) >= 14
),

@callahantiff
Copy link
Owner Author

dx_case_inclusion_criteria_1:
Probably correct but checking -- your query only looks at the FIRST Dx [min(condition_starttime)], which may have occurred before the steroid time interval. You would not pick up instances of the condition that did occur within the steroid time interval. Thinking clinically, I think this is right -- if there is a Dx of necrosis BEFORE the steroid, then it is hard to call this steroid induced necrosis. So I think the logic you have here is right but just wanted to be explicit that this is what your logic is doing.

Thanks for thinking about this so critically, it sounds like we are in the same page about this. I was intending for the query to run this way 😄.

@callahantiff
Copy link
Owner Author

all_case_inclusion_criteria_1

  • Does this run successfully? Your UNIONS return only person_ids but your outer SELECT returns person_id and code_set_group. I just don't know if this runs. If not, you may need to add {code_set_group} to each of your subqueries so they return person_id, {code_set_group}.
  • You use cohort.phenotype_definition_number =9 for all subqueries, which use concept_codes from many different OMOP tables/domains. This should be fine since you will only match on concept_codes for the domain that matches the table domain. In other queries, you used different cohort.phenotype_definition_numbers for each data domain. I think what you have here is fine since the right domain codes will be found in the right tables (and you'll pick up any codes that are being stored in the wrong table!).
  • BTW: you do the same in all_case_exclusion_criteria_1 and many others. Just check that the query runs correctly or if it needs to be adjusted as I mention in the first bullet. It's an easy fix if needed.

Bullets 1 and 3: Thanks for pointing this out. I did run the query confirm that it returns the same rows as when adding in the missing argument to the subqueries. That said, I agree that this seems sketchy so I updated all all_... queries that use the same template to be the same. This should be good to go!

Bullet 2: I agree that this is a brute-force approach and not the most elegant, but it works 😄! I am happy to change if there is a different way that you would like me to write this. Just let me know.

@callahantiff
Copy link
Owner Author

Remaining all_case_inclusion -- I see you used the same "shell" that looks at conditions, medications, observations, procedures even when the code set probably is "looking" only at a single domain (e.g. conditions). This is fine even if inefficient and more expensive. I suspect folks not using GBQ would object to such brute force queries. But it does work since the concept codes will only match in the right domains.

Consistent with response above, I agree that this is a brute-force approach and not the most elegant, but it works 😄! I am happy to change if there is a different way that you would like me to write this. Just let me know.

@callahantiff
Copy link
Owner Author

Overall logic: I think you have the logic for exclusions wrong for both cases and controls. Any exclusion removes a patient. So you need to "OR" (= UNION DISTINCT) all of the exclusions so that a patient_id that appears in ANY exclusion is included in your "EXCEPT DISTINCT" subquery. As currently written, a patient would only be excluded if they met ALL exclusion criteria.

This might be a bigger question on my end then. I take it that unless you commented, the other queries looked OK? I am only asking so I know whether or not I need to go back the other queries.

So, if the logical definitions says: "NOT presence of ANY A AND NOT presence of ANY B AND NOT presence of ANY C"

You would write this like?

EXCEPT DISTINCT
(A UNION DISTINCT B UNION DISTINCT C)

@callahantiff
Copy link
Owner Author

It looks like MIMIC-OMOP does not have de.drug_exposure_order_datetime in the drug_exposure table. Should I substitute it for de.drug_exposure_start_datetime for both CHCO and MIMIC-OMOP?

@mgkahn
Copy link
Collaborator

mgkahn commented Aug 12, 2019 via email

@callahantiff
Copy link
Owner Author

TODO FOR ME:

  • Note to update the logic table here to make it clear where you are using OR with exclusion criteria! No need to re-check all of the other phenotypes.
    • Here the query is right, but the truth table needs to be fixed!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@callahantiff callahantiff

Labels
help wanted Extra attention is needed
Projects
Coding Tasks
  
Needed Scripts
Milestone
PheKnowVec - Initial Manuscript Submi...
Development

No branches or pull requests
2 participants
@mgkahn @callahantiff