PheKnowVec Cohort Query: https://github.com/callahantiff/PheKnowVec/wiki/Crohn%27s-Disease

CROHNSDISEASE_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}.CROHNSDISEASE_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) > 2
),

rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.CROHNSDISEASE_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}
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}.CROHNSDISEASE_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_inclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.CROHNSDISEASE_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) > 2
),

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

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

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

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

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

SELECT * FROM
  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE1' 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)
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE2' 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
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM dx_case_inclusion_criteria_2)
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL
  (SELECT p.person_id, {code_set_group} AS code_set, 'CROHNSDISEASE_CONTROL' AS cohort_type
    FROM {database}.person p
    WHERE p.person_id NOT IN (
      SELECT person_id FROM dx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM rx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_2
        INTERSECT DISTINCT
       SELECT person_id  FROM dx_control_exclusion_criteria_3
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_4)
  GROUP BY p.person_id, code_set, cohort_type)
;

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

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

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

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

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

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

SELECT * FROM

  # all clinical domains + codes
  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE1' 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)
   GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

  UNION ALL

  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE2' 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
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM dx_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, 'CROHNSDISEASE_CONTROL' 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 dx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM rx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_2
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_3
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_4)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

UNION ALL

  # only conditions + codes
  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE1' 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, 'CROHNSDISEASE_CASE_TYPE2' 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
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM dx_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, 'CROHNSDISEASE_CONTROL' 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 dx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_2
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_3
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_4)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
;

CROHNSDISEASE_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}.CROHNSDISEASE_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) > 2
),

rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.CROHNSDISEASE_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}
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}.CROHNSDISEASE_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_inclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.CROHNSDISEASE_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) > 2
),

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

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

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

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

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

SELECT * FROM
  
  # all clinical domains + definitions
  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE1' 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)
   GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

  UNION ALL

  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE2' 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
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM dx_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, 'CROHNSDISEASE_CONTROL' 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 dx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM rx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_2
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_3
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_4)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

UNION ALL

  # only conditions + definitions
  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE1' 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, 'CROHNSDISEASE_CASE_TYPE2' 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
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM dx_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, 'CROHNSDISEASE_CONTROL' 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 dx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_2
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_3
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_4)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
;