PheKnowVec Cohort Query: https://github.com/callahantiff/PheKnowVec/wiki/Steroid-Induced-Osteonecrosis

STEROIDINDUCEDOSTEONECROSIS_COHORT.sql

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

STEROIDINDUCEDOSTEONECROSIS_COHORT_CODES.sql

-- CODES + ALL CLINICAL DOMAINS vs. CONDITIONS ONLY

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.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
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dx_case_inclusion_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 = 8
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, code_set
HAVING
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

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

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


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

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

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

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

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.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
HAVING
  COUNT(DISTINCT de.drug_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}.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
)),

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

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

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

SELECT * FROM

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

  UNION ALL
  
  (SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
    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))
   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, clinical_data_type, cohort_assignment)
   
  UNION ALL
  
  # only conditions + codes
  (SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CASE' 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
      UNION DISTINCT 
     SELECT person_id, code_set FROM all_case_inclusion_criteria_1A))
       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_1A
       UNION DISTINCT 
      SELECT person_id, code_set FROM all_case_exclusion_criteria_2A
       UNION DISTINCT 
      SELECT person_id, code_set FROM all_case_exclusion_criteria_3A))
   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, 'STEROIDINDUCEDOSTEONECROSIS_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 all_control_exclusion_criteria_1A
       INTERSECT DISTINCT
      SELECT person_id FROM all_control_exclusion_criteria_2A
       INTERSECT DISTINCT
      SELECT person_id FROM all_control_exclusion_criteria_3A)
  AND person_id NOT IN (
    (((SELECT person_id FROM dx_case_inclusion_criteria_1
      UNION DISTINCT 
     SELECT person_id FROM all_case_inclusion_criteria_1A))
       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_1A
       UNION DISTINCT 
      SELECT person_id FROM all_case_exclusion_criteria_2A
       UNION DISTINCT 
      SELECT person_id FROM all_case_exclusion_criteria_3A)))
 GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
 ;

STEROIDINDUCEDOSTEONECROSIS_COHORT_DEFINITIONS.sql

-- DEFINITIONS + ALL CLINICAL DOMAINS vs. CONDITIONS ONLY

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

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


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

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

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

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

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

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

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

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

SELECT * FROM

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

  UNION ALL
  
  (SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    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))
    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, clinical_data_type, cohort_assignment)
   
  UNION ALL
  
  # only conditions + definitions
  (SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CASE' 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
      UNION DISTINCT 
     SELECT person_id, code_set FROM all_case_inclusion_criteria_1A))
       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_1A
       UNION DISTINCT 
      SELECT person_id, code_set FROM all_case_exclusion_criteria_2A
       UNION DISTINCT 
      SELECT person_id, code_set FROM all_case_exclusion_criteria_3A))
   GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)

  UNION ALL
  
    (SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
    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))
    WHERE person_id NOT IN (
    ((SELECT person_id FROM dx_case_inclusion_criteria_1
      UNION DISTINCT 
     SELECT person_id FROM all_case_inclusion_criteria_1A))
       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_1A
       UNION DISTINCT 
      SELECT person_id FROM all_case_exclusion_criteria_2A
       UNION DISTINCT 
      SELECT person_id FROM all_case_exclusion_criteria_3A))
   GROUP BY person_id, code_set, cohort_type)
;