PheKnowVec Cohort Query: https://github.com/callahantiff/PheKnowVec/wiki/Appendicitis

APPENDICITIS_COHORT.sql

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, 'AAPPENDICITIS_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)
;

APPENDICITIS_COHORT_CODES.sql

-- CODES + ALL CLINICAL DOMAINS vs. CONDITIONS ONLY
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
HAVING 
  COUNT(DISTINCT de.drug_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}.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
)),

all_case_inclusion_criteria_2 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
)),

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
)),

all_control_exclusion_criteria_2 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
)),

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
)

SELECT * FROM

  # all clinical domains + codes
  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2A' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
    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, clinical_data_type, cohort_assignment)

  UNION ALL

   (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2B' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
    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, clinical_data_type, cohort_assignment)

  UNION ALL

   (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE3' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
    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, clinical_data_type, cohort_assignment)

  UNION ALL

   (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE1' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
      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, clinical_data_type, cohort_assignment)
  
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE2' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
    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, clinical_data_type, cohort_assignment)
 
 UNION ALL
 
 # only conditions + codes
 (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2A' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment
   FROM (
   (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1)
     EXCEPT DISTINCT
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1))
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

 UNION ALL

  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2B' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment
   FROM (
   (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1)
     EXCEPT DISTINCT
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1))
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

 UNION ALL

  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE3' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment
   FROM (
   (SELECT person_id, code_set FROM all_case_inclusion_criteria_2))
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

 UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE1' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment
     FROM {database}.person p
     WHERE p.person_id NOT IN (
       SELECT person_id FROM all_control_exclusion_criteria_2
         INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_1)
   GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
 
 UNION ALL

 (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE2' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment
   FROM {database}.person p
   WHERE p.person_id NOT IN (
     SELECT person_id FROM all_control_exclusion_criteria_2
         INTERSECT DISTINCT
     SELECT person_id FROM dx_control_exclusion_criteria_1)
 GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
;

APPENDICITIS_COHORT_DEFINITIONS.sql

-- DEFINITIONS + ALL CLINICAL DOMAINS vs. CONDITIONS ONLY
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
  de.person_id, cohort.standard_code_set, de.drug_exposure_end_datetime, de.drug_exposure_start_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
  AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_start_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
)),

all_case_inclusion_criteria_2 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
)),

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
)),

all_control_exclusion_criteria_2 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
)),

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

  # all clinical domains + definitions
  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2A' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    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, clinical_data_type, cohort_assignment)

  UNION ALL

   (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2B' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    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, clinical_data_type, cohort_assignment)

  UNION ALL

   (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE3' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    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, clinical_data_type, cohort_assignment)

  UNION ALL

   (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE1' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
      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, clinical_data_type, cohort_assignment)
  
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE2' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    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, clinical_data_type, cohort_assignment)
 
 UNION ALL
 
 # only conditions + definitions
 (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2A' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
   FROM (
   (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1)
     EXCEPT DISTINCT
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1))
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

 UNION ALL

  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2B' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
   FROM (
   (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1)
     EXCEPT DISTINCT
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1))
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

 UNION ALL

  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE3' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
   FROM (
   (SELECT person_id, code_set FROM all_case_inclusion_criteria_2))
  GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

 UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE1' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
     FROM {database}.person p
     WHERE p.person_id NOT IN (
       SELECT person_id FROM all_control_exclusion_criteria_2
         INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_1)
   GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
 
 UNION ALL

 (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE2' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
   FROM {database}.person p
   WHERE p.person_id NOT IN (
     SELECT person_id FROM all_control_exclusion_criteria_2
         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, clinical_data_type, cohort_assignment)
;