Query Verification: Appendicitis Cohort

@mgkahn - Can you please help me verify the query to select Appendicitis patients?

COHORT CRITERIA Case Criteria:
~~CASE 1: Pathology report positive for appendicitis~~ (We don’t have this data)

CASE TYPE 2A: No pathology report, treatment is systemic antibiotics

dx_case_inclusion_criteria_1: Presence of an appendicitis diagnosis (ICD9 codes - criteria # 1)
rx_case_inclusion_criteria_1: Presence of systemic antibiotics (>2 days treatment, starting on encounter of appendicitis diagnosis) (drug strings - criteria # 2)
dx_case_exclusion_criteria_1: NOT presence of an appendicitis comorbidity in same encounter as appendicitis diagnosis (ICD9 codes - criteria # 3)
px_case_exclusion_criteria_1: NOT presence of an interventional Radiology code (CPT codes - criteria # 4)

CASE TYPE 2B: No pathology report, treatment is interventional radiology

dx_case_inclusion_criteria_1: Presence of an appendicitis diagnosis (ICD9 codes - criteria # 1)
px_case_inclusion_criteria_1: Presence of an interventional Radiology code (CPT codes - criteria # 5)
dx_case_exclusion_criteria_1: NOT presence of an appendicitis comorbidity in same encounter as appendicitis diagnosis (ICD9 codes - criteria # 3)
rx_case_exclusion_criteria_1: NOT Presence of systemic antibiotics (>2 days treatment, starting on encounter of appendicitis diagnosis) (drug strings - criteria # 6)

CASE TYPE 3: Reported history of appendicitis/appendectomy, without history of incidental appendectomy

all_case_inclusion_criteria_1: Presence of history of appendicitis reported (condition strings; drug strings;measurement strings; observation strings;procedure strings - criteria # 7)
px_case_exclusion_criteria_2: NOT presence of an incidental appendectomy (CPT codes/ICD9 codes - criteria # 8)

Control Criteria:
CONTROL TYPE 1: Pediatric patients

all_control_exclusion_criteria_1: NOT presence of history of appendicitis reported (condition strings; drug strings; measurement strings; observation strings; procedure strings - criteria # 9)
dx_control_exclusion_criteria_1: NOT presence of exclusionary codes (ICD9 codes - criteria # 10)

CONTROL TYPE 2: Adult patients

visit_criteria_1: Presence of at least 2 primary care/medical home visits between ages 20-40 years (inclusive)
- Visit_occurrence (visit_admitting_type)
- Home/Self-Care (44814675)
- Ambulatory visit (44814672)
- Other (44814649)
all_control_exclusion_criteria_1: NOT presence of history of appendicitis reported (condition strings; drug strings; measurement strings; observation strings; procedure strings - criteria # 9)
dx_control_exclusion_criteria_1: NOT presence of exclusionary codes (ICD9 codes - criteria # 10)

Cohort Logic Table

COHORT	CHUNK	LOGICAL OPERATOR
CASE TYPE 2A	dx_case_inclusion_criteria_1	AND
CASE TYPE 2A	rx_case_inclusion_criteria_1	AND
CASE TYPE 2A	dx_case_exclusion_criteria_1	AND
CASE TYPE 2A	px_case_exclusion_criteria_1	---

CASE TYPE 2B	dx_case_inclusion_criteria_1	AND
CASE TYPE 2B	px_case_inclusion_criteria_1	AND
CASE TYPE 2B	dx_case_exclusion_criteria_1	AND
CASE TYPE 2B	rx_case_exclusion_criteria_1	---

CASE TYPE 3	all_case_inclusion_criteria_1	AND
CASE TYPE 3	px_case_exclusion_criteria_2	---

CONTROL_TYPE1	all_control_exclusion_criteria_1	AND
CONTROL_TYPE1	px_control_exclusion_criteria_1	---

CONTROL_TYPE2	visit_criteria_1	AND
CONTROL_TYPE2	all_control_exclusion_criteria_1	AND
CONTROL_TYPE2	dx_control_exclusion_criteria_1	---

SQL Query

WITH dx_case_inclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.APPENDICITIS_COHORT_VARS cohort 
WHERE 
  co.condition_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 
  co.person_id, cohort.standard_code_set 
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.APPENDICITIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 2 
  AND cohort.standard_code_set = {code_set_group}
  AND de.visit_occurrence_id IN (
      SELECT visit_occurrence_id FROM
      (SELECT co.visit_occurrence_id, min(co.condition_start_date)
    FROM 
      {database}.condition_occurrence co, 
      {database}.APPENDICITIS_COHORT_VARS cohort 
    WHERE 
      co.condition_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 
      co.visit_occurrence_id
    HAVING 
      COUNT(DISTINCT co.condition_concept_id) >= 1))
GROUP BY 
  de.person_id, cohort.standard_code_set, de.drug_exposure_end_datetime, de.drug_exposure_order_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) > 2
),

dx_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.APPENDICITIS_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
),

px_case_exclusion_criteria_1 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr,
  {database}.APPENDICITIS_COHORT_VARS cohort
WHERE 
  pr.procedure_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 pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

px_case_inclusion_criteria_1 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr,
  {database}.APPENDICITIS_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
),

rx_case_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.APPENDICITIS_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
),

all_case_inclusion_criteria_1 AS (
SELECT person_id, {code_set_group} AS code_set FROM
(SELECT co.person_id
FROM 
  {database}.condition_occurrence co, 
  {database}.APPENDICITIS_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
FROM 
  {database}.drug_exposure de, 
  {database}.APPENDICITIS_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
FROM 
  {database}.measurement m, 
  {database}.APPENDICITIS_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
FROM 
  {database}.observation o, 
  {database}.APPENDICITIS_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
FROM 
  {database}.procedure_occurrence pr, 
  {database}.APPENDICITIS_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
)),

px_case_exclusion_criteria_2 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr,
  {database}.APPENDICITIS_COHORT_VARS cohort
WHERE 
  pr.procedure_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 pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

all_control_exclusion_criteria_1 AS (
SELECT person_id, {code_set_group} AS code_set FROM
(SELECT co.person_id
FROM 
  {database}.condition_occurrence co, 
  {database}.APPENDICITIS_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
FROM 
  {database}.drug_exposure de, 
  {database}.APPENDICITIS_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
FROM 
  {database}.measurement m, 
  {database}.APPENDICITIS_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
FROM 
  {database}.observation o, 
  {database}.APPENDICITIS_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
FROM 
  {database}.procedure_occurrence pr, 
  {database}.APPENDICITIS_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_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.APPENDICITIS_COHORT_VARS cohort
WHERE 
  co.condition_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 co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

visit_criteria_1 AS (
SELECT v.person_id, {code_set_group} AS code_set 
FROM 
  {database}.visit_occurrence v, 
  {database}.person p 
WHERE 
  v.person_id = p.person_id 
  AND v.admitting_source_concept_id IN (44814675, 44814672, 44814649) 
GROUP BY 
  v.person_id, v.visit_start_datetime, p.birth_datetime
HAVING 
  COUNT(DISTINCT v.visit_start_date) >= 2
  AND DATETIME_DIFF(DATETIME(v.visit_start_datetime), DATETIME(p.birth_datetime), YEAR) >= 20
  AND DATETIME_DIFF(DATETIME(v.visit_start_datetime), DATETIME(p.birth_datetime), YEAR) <= 40
)

SELECT * FROM
  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2A' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM rx_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 px_case_exclusion_criteria_1))
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2B' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_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 rx_case_exclusion_criteria_1))
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE3' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM all_case_inclusion_criteria_1)
      EXCEPT DISTINCT
     (SELECT person_id, code_set FROM px_case_exclusion_criteria_2))
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE1' AS cohort_type
      FROM {database}.person p
      WHERE p.person_id NOT IN (
        SELECT person_id FROM all_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM dx_control_exclusion_criteria_1)
    GROUP BY p.person_id, code_set, cohort_type)

  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE2' AS cohort_type
    FROM {database}.person p
    WHERE p.person_id NOT IN (
         SELECT person_id FROM all_control_exclusion_criteria_1
          INTERSECT DISTINCT
         SELECT person_id FROM dx_control_exclusion_criteria_1)
    AND p.person_id IN (SELECT person_id FROM visit_criteria_1)
  GROUP BY p.person_id, code_set, cohort_type)
;

callahantiff / PheKnowVec

Query Verification: Appendicitis Cohort #101

SQL Query