Skip to content

Instantly share code, notes, and snippets.

@callahantiff

callahantiff/STEROIDINDUCEDOSTEONECROSIS_COHORT.sql

Last active August 12, 2019 19:52
Show Gist options
  • Save callahantiff/7cc17ec064e16803376d0b34fc34e966 to your computer and use it in GitHub Desktop.
Save callahantiff/7cc17ec064e16803376d0b34fc34e966 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
STEROIDINDUCEDOSTEONECROSIS_COHORT.sql
WITH rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE de.route_concept_id IN (4132161, 4171047, 4302612)
AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 1
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_start_datetime), day) >= 14
),
dx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort,
(SELECT co.person_id, min(DATETIME(co.condition_start_datetime)) AS cond_start_date
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 8
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, co.condition_concept_id
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1) cont
WHERE
cont.person_id = de.person_id
AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 1
AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime, cont.cond_start_date
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
AND cont.cond_start_date >= min(DATETIME(de.drug_exposure_start_datetime))
AND cont.cond_start_date <= DATETIME_ADD(DATETIME(max(de.drug_exposure_end_datetime)), INTERVAL 1 YEAR)
),
all_case_inclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
dx_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 2
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_case_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 3
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_case_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 4
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
all_case_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
rx_control_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE de.route_concept_id IN (4132161, 4171047, 4302612)
AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 10
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_start_datetime), day) >= 14
),
rx_control_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 11
AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
),
dx_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 12
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 13
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_control_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 14
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
all_control_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_control_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_control_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
))
SELECT * FROM
(SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CASE' AS cohort_type
FROM (
(SELECT person_id, code_set FROM rx_case_inclusion_criteria_1
INTERSECT DISTINCT
(SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_case_inclusion_criteria_1))
EXCEPT DISTINCT
(SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_3))
GROUP BY person_id, code_set, cohort_type)
UNION ALL
(SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type
FROM (
(SELECT person_id, code_set FROM rx_control_inclusion_criteria_1)
EXCEPT DISTINCT
(SELECT person_id, code_set FROM rx_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_3
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_3))
-- making sure no control patient is also a case
WHERE person_id NOT IN (
(SELECT person_id FROM rx_case_inclusion_criteria_1
INTERSECT DISTINCT
(SELECT person_id FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM all_case_inclusion_criteria_1))
EXCEPT DISTINCT
(SELECT person_id FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_3))
GROUP BY person_id, code_set, cohort_type)
;
Raw
STEROIDINDUCEDOSTEONECROSIS_COHORT_CODES.sql
-- CODES + ALL CLINICAL DOMAINS vs. CONDITIONS ONLY
WITH rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 1
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
),
dx_case_inclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 8
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
all_case_inclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_inclusion_criteria_1A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
dx_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 2
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_case_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 3
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_case_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 4
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
all_case_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_exclusion_criteria_1A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
all_case_exclusion_criteria_2A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
all_case_exclusion_criteria_3A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
rx_control_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 10
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
),
rx_control_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 11
AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
),
dx_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 12
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 13
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_control_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 14
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
all_control_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_control_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_control_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_control_exclusion_criteria_1A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
all_control_exclusion_criteria_2A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
all_control_exclusion_criteria_3A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
))
SELECT * FROM
# all clinical domains + codes
(SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CASE' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
FROM (
(SELECT person_id, code_set FROM rx_case_inclusion_criteria_1
INTERSECT DISTINCT
(SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_case_inclusion_criteria_1))
EXCEPT DISTINCT
(SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_3))
GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
UNION ALL
(SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'CODES' AS cohort_assignment
FROM (
(SELECT person_id, code_set FROM rx_control_inclusion_criteria_1)
EXCEPT DISTINCT
(SELECT person_id, code_set FROM rx_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_3
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_3))
WHERE person_id NOT IN (
(SELECT person_id FROM rx_case_inclusion_criteria_1
INTERSECT DISTINCT
(SELECT person_id FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM all_case_inclusion_criteria_1))
EXCEPT DISTINCT
(SELECT person_id FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_3)
)
GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
UNION ALL
# only conditions + codes
(SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CASE' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment
FROM (
((SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_case_inclusion_criteria_1A))
EXCEPT DISTINCT
(SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_1A
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_2A
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_3A))
GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
UNION ALL
(SELECT p.person_id, {code_set_group} AS code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'CODES' AS cohort_assignment
FROM {database}.person p
WHERE p.person_id NOT IN (
SELECT person_id FROM dx_control_exclusion_criteria_1
INTERSECT DISTINCT
SELECT person_id FROM dx_control_exclusion_criteria_2
INTERSECT DISTINCT
SELECT person_id FROM dx_control_exclusion_criteria_3
INTERSECT DISTINCT
SELECT person_id FROM all_control_exclusion_criteria_1A
INTERSECT DISTINCT
SELECT person_id FROM all_control_exclusion_criteria_2A
INTERSECT DISTINCT
SELECT person_id FROM all_control_exclusion_criteria_3A)
AND person_id NOT IN (
(((SELECT person_id FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM all_case_inclusion_criteria_1A))
EXCEPT DISTINCT
(SELECT person_id FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_1A
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_2A
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_3A)))
GROUP BY p.person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
;
Raw
STEROIDINDUCEDOSTEONECROSIS_COHORT_DEFINITIONS.sql
-- DEFINITIONS + ALL CLINICAL DOMAINS vs. CONDITIONS ONLY
WITH rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE de.route_concept_id IN (4132161, 4171047, 4302612)
AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 1
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_start_datetime), day) >= 14
),
dx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort,
(SELECT co.person_id, min(DATETIME(co.condition_start_datetime)) AS cond_start_date
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 8
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, co.condition_concept_id
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1) cont
WHERE
cont.person_id = de.person_id
AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 1
AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime, cont.cond_start_date
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
AND cont.cond_start_date >= min(DATETIME(de.drug_exposure_start_datetime))
AND cont.cond_start_date <= DATETIME_ADD(DATETIME(max(de.drug_exposure_end_datetime)), INTERVAL 1 YEAR)
),
all_case_inclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_inclusion_criteria_1A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 9
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
dx_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 2
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_case_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 3
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_case_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 4
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
all_case_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_case_exclusion_criteria_1A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 5
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
all_case_exclusion_criteria_2A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 6
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
all_case_exclusion_criteria_3A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 7
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
rx_control_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE de.route_concept_id IN (4132161, 4171047, 4302612)
AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 10
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_start_datetime), day) >= 14
),
rx_control_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 11
AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
),
dx_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 12
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 13
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
dx_control_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 14
AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
),
all_control_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_control_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_control_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
{database}.drug_exposure de,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
de.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM
{database}.measurement m,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
m.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM
{database}.observation o,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
o.observation_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
o.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM
{database}.procedure_occurrence pr,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
pr.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),
all_control_exclusion_criteria_1A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 15
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
all_control_exclusion_criteria_2A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 16
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
)),
all_control_exclusion_criteria_3A AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM
{database}.condition_occurrence co,
{database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE
co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
AND cohort.phenotype_definition_number = 17
AND cohort.standard_code_set = {code_set_group}
GROUP BY
co.person_id, cohort.standard_code_set
HAVING
COUNT(DISTINCT co.condition_concept_id) >= 1
))
SELECT * FROM
# all clinical domains + definitions
(SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CASE' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
FROM (
(SELECT person_id, code_set FROM rx_case_inclusion_criteria_1
INTERSECT DISTINCT
(SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_case_inclusion_criteria_1))
EXCEPT DISTINCT
(SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_3))
GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
UNION ALL
(SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type, 'ALL DOMAINS' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
FROM (
(SELECT person_id, code_set FROM rx_control_inclusion_criteria_1)
EXCEPT DISTINCT
(SELECT person_id, code_set FROM rx_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_3
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_3))
WHERE person_id NOT IN (
(SELECT person_id FROM rx_case_inclusion_criteria_1
INTERSECT DISTINCT
(SELECT person_id FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM all_case_inclusion_criteria_1))
EXCEPT DISTINCT
(SELECT person_id FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_3))
GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
UNION ALL
# only conditions + definitions
(SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CASE' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
FROM (
((SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_case_inclusion_criteria_1A))
EXCEPT DISTINCT
(SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_1A
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_2A
UNION DISTINCT
SELECT person_id, code_set FROM all_case_exclusion_criteria_3A))
GROUP BY person_id, code_set, cohort_type, clinical_data_type, cohort_assignment)
UNION ALL
(SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type, 'CONDITIONS ONLY' AS clinical_data_type, 'DEFINITIONS' AS cohort_assignment
FROM (
(SELECT person_id, code_set FROM rx_control_inclusion_criteria_1)
EXCEPT DISTINCT
(SELECT person_id, code_set FROM rx_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM dx_control_exclusion_criteria_3
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_1
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_2
UNION DISTINCT
SELECT person_id, code_set FROM all_control_exclusion_criteria_3))
WHERE person_id NOT IN (
((SELECT person_id FROM dx_case_inclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM all_case_inclusion_criteria_1A))
EXCEPT DISTINCT
(SELECT person_id FROM dx_case_exclusion_criteria_1
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_2
UNION DISTINCT
SELECT person_id FROM dx_case_exclusion_criteria_3
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_1A
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_2A
UNION DISTINCT
SELECT person_id FROM all_case_exclusion_criteria_3A))
GROUP BY person_id, code_set, cohort_type)
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment