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

HYPOTHYROIDISM_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}.HYPOTHYROIDISM_COHORT_VARS cohort
  WHERE 
    co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
    AND cohort.phenotype_definition_number = 0
    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
),

mx_case_inclusion_criteria_1 AS (
  SELECT * FROM (
    SELECT m.person_id, cohort.standard_code_set AS code_set 
    FROM 
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
    WHERE 
      m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
      AND cohort.phenotype_definition_number = 1 
      AND cohort.standard_code_set = {code_set_group}
      AND m.value_as_number > m.range_high
    GROUP BY 
      m.person_id, cohort.standard_code_set
    HAVING 
      COUNT(DISTINCT m.measurement_concept_id) >= 1
   UNION ALL
    SELECT m.person_id, cohort.standard_code_set AS code_set 
    FROM 
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
    WHERE 
      m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
      AND cohort.phenotype_definition_number = 2 
      AND cohort.standard_code_set = {code_set_group}
      AND m.value_as_number < m.range_high
    GROUP BY 
      m.person_id, cohort.standard_code_set
    HAVING 
      COUNT(DISTINCT m.measurement_concept_id) >= 1   
)),

mxrx_case_inclusion_criteria_1 AS (
SELECT * FROM 
(SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.drug_exposure de, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE de.visit_occurrence_id = v.visit_occurrence_id  
      AND de.drug_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT de.drug_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_id) >= 2
  UNION ALL
  SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE m.visit_occurrence_id = v.visit_occurrence_id  
      AND m.measurement_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT m.measurement_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_id) >= 2)
UNION ALL
(SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.drug_exposure de, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE de.visit_occurrence_id = v.visit_occurrence_id  
      AND de.drug_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT de.drug_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_id) >= 1
  INTERSECT DISTINCT
  SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE m.visit_occurrence_id = v.visit_occurrence_id  
      AND m.measurement_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT m.measurement_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_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}.HYPOTHYROIDISM_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
),

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

px_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set FROM
(SELECT pr.person_id, v.visit_end_date as procedure_date, cohort.standard_code_set AS code_set
  FROM 
    {database}.procedure_occurrence pr,
    {database}.visit_occurrence v,
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort
  WHERE 
    pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
    AND v.visit_occurrence_id = pr.visit_occurrence_id
    AND cohort.phenotype_definition_number = 11
    AND cohort.standard_code_set = {code_set_group}
  GROUP BY pr.person_id, cohort.standard_code_set, v.visit_end_date
  HAVING
    COUNT(DISTINCT pr.procedure_concept_id) >= 1) cont1,
  {database}.condition_occurrence co,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = co.person_id
  AND co.visit_occurrence_id = v.visit_occurrence_id
  AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 0
  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
  AND max(cont1.procedure_date) < max(v.visit_end_date)
),

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

rx_case_exclusion_criteria_2 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  de.drug_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 de.person_id, cohort.standard_code_set
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dxmx_case_exclusion_criteria_1 AS (
SELECT * FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 1 
    AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number > m.range_high
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.condition_occurrence co,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = co.person_id
  AND co.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT co.condition_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12
  UNION ALL
  SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 2 
    AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number < m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.condition_occurrence co,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = co.person_id
  AND co.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT co.condition_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12)  
  INTERSECT DISTINCT  
  (SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 1 
   AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number > m.range_high
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12  
  UNION ALL 
  SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 2 
  AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number < m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12
)),

rxmx_case_exclusion_criteria_1 AS (
SELECT * FROM
(SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT de.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.drug_exposure de, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = de.visit_occurrence_id
    AND de.drug_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 
    de.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT de.drug_concept_id) >= 1) cont1,
  {database}.drug_exposure de,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = de.person_id
  AND de.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6
 UNION ALL
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_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 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.drug_exposure de,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = de.person_id
  AND de.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6)
UNION ALL
(SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT de.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.drug_exposure de, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = de.visit_occurrence_id
    AND de.drug_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 
    de.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT de.drug_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6
  UNION ALL
 SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_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 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6)
),

mx_control_inclusion_criteria_1 AS (
SELECT * FROM (
SELECT m.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
  WHERE 
    m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 14 
    AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number < m.range_high
    AND m.value_as_number > m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1
 INTERSECT DISTINCT
 SELECT m.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_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}
    AND m.value_as_number < m.range_high
    AND m.value_as_number > m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1)
),

visit_criteria_1 AS (
  SELECT v.person_id
  FROM
    {database}.visit_occurrence v
  WHERE
    v.admitting_source_concept_id = 44814672
  GROUP BY 
    v.person_id
  HAVING
    COUNT(DISTINCT v.visit_occurrence_id) >= 2
),

mx_control_inclusion_criteria_2 AS (
  SELECT de.person_id
  FROM
    {database}.drug_exposure de
  GROUP BY 
    de.person_id
  HAVING
    COUNT(DISTINCT de.drug_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}.HYPOTHYROIDISM_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
),

mx_control_exclusion_criteria_1 AS (
SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.measurement m, 
  {database}.HYPOTHYROIDISM_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
),

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}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

px_control_exclusion_criteria_1 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 19
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

px_control_exclusion_criteria_2 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 20
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 21
  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, 'HYPOTHYROIDISM_CASE' AS cohort_type
    FROM (    
    ((SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      UNION DISTINCT 
    (SELECT person_id, code_set FROM mx_case_inclusion_criteria_1)) 
      INTERSECT DISTINCT
    SELECT person_id, code_set FROM mxrx_case_inclusion_criteria_1)         
      EXCEPT DISTINCT      
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM rx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dxmx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM rxmx_case_exclusion_criteria_1))
    GROUP BY person_id, code_set, cohort_type)
    
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'HYPOTHYROIDISM_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 mx_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM all_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM px_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM px_control_exclusion_criteria_2
          INTERSECT DISTINCT
        SELECT person_id FROM dx_control_exclusion_criteria_2)
    AND p.person_id IN (
        (SELECT person_id FROM visit_criteria_1
         UNION DISTINCT
        SELECT person_id FROM mx_control_inclusion_criteria_1)
         INTERSECT DISTINCT
        SELECT person_id FROM mx_control_inclusion_criteria_2)
  GROUP BY p.person_id, code_set, cohort_type)
;

HYPOTHYROIDISM_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}.HYPOTHYROIDISM_COHORT_VARS cohort
  WHERE 
    co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
    AND cohort.phenotype_definition_number = 0
    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
),

mx_case_inclusion_criteria_1 AS (
  SELECT * FROM (
    SELECT m.person_id, cohort.standard_code_set AS code_set 
    FROM 
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
    WHERE 
      m.measurement_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 
      m.person_id, cohort.standard_code_set
    HAVING 
      COUNT(DISTINCT m.measurement_concept_id) >= 1)
   UNION ALL
    (SELECT m.person_id, cohort.standard_code_set AS code_set 
    FROM 
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
    WHERE 
      m.measurement_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 
      m.person_id, cohort.standard_code_set
    HAVING 
      COUNT(DISTINCT m.measurement_concept_id) >= 1   
)),

mxrx_case_inclusion_criteria_1 AS (
SELECT * FROM 
(SELECT de.person_id, cohort.standard_code_set AS code_set
    FROM 
      {database}.drug_exposure de, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE
      de.drug_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
      de.person_id, cohort.standard_code_set
    HAVING 
    COUNT(DISTINCT de.drug_concept_id) >= 1)
  UNION ALL
  (SELECT m.person_id, cohort.standard_code_set
    FROM 
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
  WHERE 
    m.measurement_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
    m.person_id, cohort.standard_code_set
  HAVING 
  COUNT(DISTINCT m.measurement_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}.HYPOTHYROIDISM_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
),

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

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

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

rx_case_exclusion_criteria_2 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  de.drug_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 de.person_id, cohort.standard_code_set
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dxmx_case_exclusion_criteria_1 AS (
SELECT * FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM
  {database}.condition_occurrence co,
  {database}.HYPOTHYROIDISM_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 ALL
(SELECT m.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort
  WHERE 
    m.measurement_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 m.person_id, cohort.standard_code_set
  HAVING
    COUNT(DISTINCT m.measurement_concept_id) >= 1
)),

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

rxmx_case_exclusion_criteria_1 AS (
SELECT * FROM
(SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM
    {database}.drug_exposure de, 
    {database}.HYPOTHYROIDISM_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 ALL
(SELECT m.person_id, cohort.standard_code_set AS code_set 
 FROM 
  {database}.measurement m, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
 WHERE 
  m.measurement_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 m.person_id, cohort.standard_code_set
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1)
),

mx_control_inclusion_criteria_1 AS (
SELECT * FROM (
SELECT m.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
  WHERE 
    m.measurement_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 
    m.person_id, cohort.standard_code_set
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1
 INTERSECT DISTINCT
 SELECT m.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_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)
),

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

mx_control_exclusion_criteria_1 AS (
SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.measurement m, 
  {database}.HYPOTHYROIDISM_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
),

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}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
)),

px_control_exclusion_criteria_1 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 19
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

px_control_exclusion_criteria_2 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 20
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 21
  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, 'HYPOTHYROIDISM_CASE' 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
      UNION DISTINCT 
    (SELECT person_id, code_set FROM mx_case_inclusion_criteria_1)) 
      INTERSECT DISTINCT
    SELECT person_id, code_set FROM mxrx_case_inclusion_criteria_1)         
      EXCEPT DISTINCT      
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM rx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dxmx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM rxmx_case_exclusion_criteria_1))
    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, 'HYPOTHYROIDISM_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 mx_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM all_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM px_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM px_control_exclusion_criteria_2
          INTERSECT DISTINCT
        SELECT person_id FROM dx_control_exclusion_criteria_2)
    AND p.person_id IN (
        SELECT person_id FROM mx_control_inclusion_criteria_1)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
  
 UNION ALL
 
  # conditions only + codes
  (SELECT person_id, code_set, 'HYPOTHYROIDISM_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
      EXCEPT DISTINCT      
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dxmx_case_exclusion_criteria_2))
    GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
    
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'HYPOTHYROIDISM_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 all_control_exclusion_criteria_2
          INTERSECT DISTINCT
        SELECT person_id FROM dx_control_exclusion_criteria_2)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
;

HYPOTHYROIDISM_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}.HYPOTHYROIDISM_COHORT_VARS cohort
  WHERE 
    co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
    AND cohort.phenotype_definition_number = 0
    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
),

mx_case_inclusion_criteria_1 AS (
  SELECT * FROM (
    SELECT m.person_id, cohort.standard_code_set AS code_set 
    FROM 
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
    WHERE 
      m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
      AND cohort.phenotype_definition_number = 1 
      AND cohort.standard_code_set = {code_set_group}
      AND m.value_as_number > m.range_high
    GROUP BY 
      m.person_id, cohort.standard_code_set
    HAVING 
      COUNT(DISTINCT m.measurement_concept_id) >= 1
   UNION ALL
    SELECT m.person_id, cohort.standard_code_set AS code_set 
    FROM 
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
    WHERE 
      m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
      AND cohort.phenotype_definition_number = 2 
      AND cohort.standard_code_set = {code_set_group}
      AND m.value_as_number < m.range_high
    GROUP BY 
      m.person_id, cohort.standard_code_set
    HAVING 
      COUNT(DISTINCT m.measurement_concept_id) >= 1   
)),

mxrx_case_inclusion_criteria_1 AS (
SELECT * FROM 
(SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.drug_exposure de, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE de.visit_occurrence_id = v.visit_occurrence_id  
      AND de.drug_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT de.drug_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_id) >= 2
  UNION ALL
  SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE m.visit_occurrence_id = v.visit_occurrence_id  
      AND m.measurement_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT m.measurement_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_id) >= 2)
UNION ALL
(SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.drug_exposure de, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE de.visit_occurrence_id = v.visit_occurrence_id  
      AND de.drug_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT de.drug_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_id) >= 1
  INTERSECT DISTINCT
  SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE m.visit_occurrence_id = v.visit_occurrence_id  
      AND m.measurement_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT m.measurement_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_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}.HYPOTHYROIDISM_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
),

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

px_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set FROM
(SELECT pr.person_id, v.visit_end_date as procedure_date, cohort.standard_code_set AS code_set
  FROM 
    {database}.procedure_occurrence pr,
    {database}.visit_occurrence v,
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort
  WHERE 
    pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
    AND v.visit_occurrence_id = pr.visit_occurrence_id
    AND cohort.phenotype_definition_number = 11
    AND cohort.standard_code_set = {code_set_group}
  GROUP BY pr.person_id, cohort.standard_code_set, v.visit_end_date
  HAVING
    COUNT(DISTINCT pr.procedure_concept_id) >= 1) cont1,
  {database}.condition_occurrence co,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = co.person_id
  AND co.visit_occurrence_id = v.visit_occurrence_id
  AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 0
  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
  AND max(cont1.procedure_date) < max(v.visit_end_date)
),

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

rx_case_exclusion_criteria_2 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  de.drug_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 de.person_id, cohort.standard_code_set
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dxmx_case_exclusion_criteria_1 AS (
SELECT * FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 1 
    AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number > m.range_high
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.condition_occurrence co,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = co.person_id
  AND co.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT co.condition_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12
  UNION ALL
  SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 2 
    AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number < m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.condition_occurrence co,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = co.person_id
  AND co.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT co.condition_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12)  
  INTERSECT DISTINCT  
  (SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 1 
   AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number > m.range_high
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12  
  UNION ALL 
  SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 2 
  AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number < m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12
)),

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

rxmx_case_exclusion_criteria_1 AS (
SELECT * FROM
(SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT de.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.drug_exposure de, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = de.visit_occurrence_id
    AND de.drug_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 
    de.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT de.drug_concept_id) >= 1) cont1,
  {database}.drug_exposure de,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = de.person_id
  AND de.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6
 UNION ALL
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_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 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.drug_exposure de,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = de.person_id
  AND de.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6)
UNION ALL
(SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT de.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.drug_exposure de, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = de.visit_occurrence_id
    AND de.drug_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 
    de.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT de.drug_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6
  UNION ALL
 SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_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 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6)
),

mx_control_inclusion_criteria_1 AS (
SELECT * FROM (
SELECT m.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
  WHERE 
    m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 14 
    AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number < m.range_high
    AND m.value_as_number > m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1
 INTERSECT DISTINCT
 SELECT m.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_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}
    AND m.value_as_number < m.range_high
    AND m.value_as_number > m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1)
),

visit_criteria_1 AS (
  SELECT v.person_id
  FROM
    {database}.visit_occurrence v
  WHERE
    v.admitting_source_concept_id = 44814672
  GROUP BY 
    v.person_id
  HAVING
    COUNT(DISTINCT v.visit_occurrence_id) >= 2
),

mx_control_inclusion_criteria_2 AS (
  SELECT de.person_id
  FROM
    {database}.drug_exposure de
  GROUP BY 
    de.person_id
  HAVING
    COUNT(DISTINCT de.drug_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}.HYPOTHYROIDISM_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
),

mx_control_exclusion_criteria_1 AS (
SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.measurement m, 
  {database}.HYPOTHYROIDISM_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
),

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}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
)),

px_control_exclusion_criteria_1 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 19
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

px_control_exclusion_criteria_2 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 20
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 21
  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, 'HYPOTHYROIDISM_CASE' 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
      UNION DISTINCT 
    (SELECT person_id, code_set FROM mx_case_inclusion_criteria_1)) 
      INTERSECT DISTINCT
    SELECT person_id, code_set FROM mxrx_case_inclusion_criteria_1)         
      EXCEPT DISTINCT      
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM rx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dxmx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM rxmx_case_exclusion_criteria_1))
    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, 'HYPOTHYROIDISM_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 mx_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM all_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM px_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM px_control_exclusion_criteria_2
          INTERSECT DISTINCT
        SELECT person_id FROM dx_control_exclusion_criteria_2)
    AND p.person_id IN (
        (SELECT person_id FROM visit_criteria_1
         UNION DISTINCT
        SELECT person_id FROM mx_control_inclusion_criteria_1)
         INTERSECT DISTINCT
        SELECT person_id FROM mx_control_inclusion_criteria_2)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
  
 UNION ALL
 
  # conditions only + definitions
  (SELECT person_id, code_set, 'HYPOTHYROIDISM_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
      EXCEPT DISTINCT      
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dxmx_case_exclusion_criteria_2))
    GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
    
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'HYPOTHYROIDISM_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 all_control_exclusion_criteria_2
          INTERSECT DISTINCT
        SELECT person_id FROM dx_control_exclusion_criteria_2)
    AND p.person_id IN (SELECT person_id FROM visit_criteria_1)
  GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
;
;