Skip to content

Instantly share code, notes, and snippets.

@callahantiff

callahantiff/HYPOTHYROIDISM_COHORT.sql

Last active August 11, 2019 22:34
Show Gist options
  • Save callahantiff/7050937cef4ba932196c3b4c964ecd92 to your computer and use it in GitHub Desktop.
Save callahantiff/7050937cef4ba932196c3b4c964ecd92 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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)
;
Raw
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)
;
Raw
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)
;
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment