Query Verification: Hypothyroidism Cohort

[callahantiff]

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

This one is pretty massive. I broke it apart a bit more than might be expected because it was easier for me to track the different conditions. Note, that I use A and B to signify when I broke the query into subparts.

---

COHORT CRITERIA Case Criteria:

• dx_case_inclusion_criteria_1: Presence of hypothyroidism (ICD9 codes - criteria # 0)
• mx_case_inclusion_criteria_1: Presence of Abnormal TSH (>5.0)/FT4 (<0.5) - lab results (measurement strings - criteria 1 and 2)
• mxrx_case_inclusion_criteria_1: Require:
  • At least 2 instances of medication, with >= 3 months between the first and last instance of the medication occurrence (drug strings - criteria # 3)
  • At least 2 instances of lab, with >= 3 months between the first and last instance of the lab occurrence (measurement strings - criteria # 4)
  • At least 1 medication (drug strings - criteria # 3) and at least 1 lab (measurement strings - criteria # 4), with >= 3 months between the first and last instance of the medication or lab occurrence
• dx_case_exclusion_criteria_1: NOT presence of secondary causes of hypothyroidism (ICD9 codes - criteria # 5)
• dx_case_exclusion_criteria_2: NOT presence of other thyroid diseases (ICD9 codes; condition strings - criteria # 6)
• px_case_exclusion_criteria_1: NOT presence of ANY radiation therapy codes that proceeds hypothyroidism dx (CPT codes - criteria # 11)
• px_case_exclusion_criteria_2: NOT presence of ANY Thyroidectomy codes (CPT codes - criteria # 12)
• rx_case_exclusion_criteria_2: NOT presence of ANY thyroid-altering medications (drug strings - criteria # 13)
• dxmx_case_exclusion_criteria_1: NOT presence of abnormal TSH/FT4 within 6 months before pregnancy to one 1 year after pregnancy:
  • Pregnancy exclusion ICD 9 codes (ICD9 codes - criteria # 7)
  • Abnormally high b-HCG in urine or serum (measurement strings - criteria # 8)
• rxmx_case_exclusion_criteria_1: NOT presence of ALL Abnormal Lab or Medication References Occur Within 6 Weeks of a Contrast Study:
  • Contrast exposure keyword search (drug strings - criteria # 9)
  • Contrast exposure keyword search (measurement strings - criteria # 10)

Control Criteria:

• mx_control_inclusion_criteria_1: Have a normal TSH (0.5 – 5) and a normal FT4 (0.5-1.2; if checked) (measurement strings - criteria # 14 and criteria # 15)
• visit_criteria_1: Presence of >= 2 past medical history sections (could substitute two non-acute clinic visits or requirement for annual physical; visit_admitting_source_concept_id == Ambulatory visit (44814672))
• mx_control_inclusion_criteria_2: Presence of >= 2 medication lists (could substitute two non-acute clinic visits or requirement for annual physical; visit_admitting_source_concept_id == Ambulatory visit (44814672))
• dx_control_exclusion_criteria_1: NOT presence of ANY billing codes for hypothyroidism (ICD9 codes - criteria # 16)
• mx_control_exclusion_criteria_1: NOT presence of ANY evidence of thyroid altering medications (drug strings - criteria # 17)
• all_control_exclusion_criteria_1: NOT presence of ANY cause of hypothyroidism or hyperthyroidism (condition strings; drug strings; measurement strings; observation strings; procedure strings - criteria # 18)
• px_control_exclusion_criteria_1: NOT presence of ANY radiation therapy codes (CPT codes - criteria # 19)
• px_control_exclusion_criteria_2: NOT presence of ANY thyroidectomy codes (CPT codes - criteria # 20)
• dx_control_exclusion_criteria_2: NOT presence of other thyroid diseases (ICD9 codes; condition strings - criteria # 21)

---

Cohort Logic Table

COHORT	CHUNK	LOGICAL OPERATOR
CASE	dx_case_inclusion_criteria_1	OR
CASE	mx_case_inclusion_criteria_1	AND
CASE	mxrx_case_inclusion_criteria_1	AND
CASE	dx_case_exclusion_criteria_1	AND
CASE	dx_case_exclusion_criteria_2	AND
CASE	px_case_exclusion_criteria_1	AND
CASE	px_case_exclusion_criteria_2	AND
CASE	rx_case_exclusion_criteria_2	AND
CASE	dxmx_case_exclusion_criteria_1	AND
CASE	rxmx_case_exclusion_criteria_1	---
CONTROL	mx_control_inclusion_criteria_1	AND
CONTROL	visit_criteria_1	AND
CONTROL	mx_control_inclusion_criteria_2	AND
CONTROL	dx_control_exclusion_criteria_1	AND
CONTROL	mx_control_exclusion_criteria_1	AND
CONTROL	all_control_exclusion_criteria_1	AND
CONTROL	px_control_exclusion_criteria_1	AND
CONTROL	px_control_exclusion_criteria_2	AND
CONTROL	dx_control_exclusion_criteria_2	---

---

NOTE.

1. Replace all instances of {database} with CHCO_DeID_Oct2018
2. Comment out all instances of {code_set_group}

Query can be found here and is also included below:

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

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

[mgkahn]

mx_case_inclusion_criteria_1: (1) Assuming definition 1 is looking for high TSH and definition 2 is look for low T4. (2) May not matter but UNION ALL will repeat PERSON_ID if same PERSON_ID seen in both parts of the UNION. UNION DISTINCT would return PERSON_ID only once.

[mgkahn]

mxrx_case_inclusion_criteria_1: Need to rewrite. Need to use V1/V2 as discussed previously where V1.start_datetime = min(medication), V2.start_datetime = max(medication), V3.start_datetime= min(lab), V4.start_datetime = max(lab). Then, first segment looks for DATEDIFF(v1.start_datetime,v2.start_datetime,month)>30; second fragment looks for DATEDIFF(v3.start_datetime, v4.start_datetime, month) > 30 and third fragment looks for DATEDIFF( min(V1.start_datetime,V3.start_datetime), max(V2.start_datetime, V4.start_datetime). MONTH) > 3

[mgkahn]

Also need to ensure V1<V2 and V3<V4 in previous logic. The entire query will be M-U-C-H simpler than your current query.

[mgkahn]

px_case_exclusion_criteria_1: is "proceeds" supposed to mean precedes? If so, my interpretation of this English is that you must be diagnosed with hypothyroidism prior to any radiation tx (because radiation tx can cause secondary hypothyroidism). If this is right, then cannot have your first radiation tx event { =min(cont1.procedure.date) } happen before your first hypothyroidism diagnosis { = min(visit_start_datetime) }

If this is the right interpretation, then max(cont1.procedure_date) < max(v.visit_end_date)

needs to be changed to

min(visit.start_date) < min(cont1.procedure_date) // first Dx visit before first RadX visit

[mgkahn]

We have to talk about dxmx_case_exclusion_criteria_1 -- the one with 6 months before and 1 year after pregnancy. I can't see how you can define the pregnancy interval to know the start of a pregnancy for "6 months before" and the end of a pregnancy for "1 year after, especially when there are multiple pregnancy events for a patient.

[mgkahn]

Not done. Had to stop for a phone call.