Med2Mech Patient Cohorts

congenital_hypothyroidism.sql

-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
  CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
  c1.concept_name AS OMOP_LABEL,
  CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE, 
  CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
  c.vocabulary_id AS VOCABULARY

FROM CHCO_DeID_Oct2018.concept c 
  JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id 
  JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id 

WHERE r.relationship_id IN ("Maps to") 
  AND c1.standard_concept in ("S", "C") 
  AND c.concept_code IN ("E00.0",  "E00.1",  "E00.2",  "E00.9",  "E03.0",  "E03.1", "243") 
  AND c.vocabulary_id IN ("ICD9", "ICD9CM", "ICD10", "ICD10CM") 
  AND c.domain_id = "Condition"
  AND c1.vocabulary_id = "SNOMED" 
  AND c1.domain_id = "Condition"

ORDER BY CLINICAL_CODE, OMOP_ID;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
  search_string AS SEARCH_STRING,
  CONCAT('"', concept_id, '"') AS OMOP_ID, 
  concept_name AS OMOP_LABEL,
  CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
  vocabulary_id AS VOCABULARY
FROM 
  (
    SELECT 
      DISTINCT c.concept_name, 
      CAST(c.concept_id AS STRING) AS concept_id,
      c.concept_code, 
      c.domain_id, 
      c.vocabulary_id, 
      CASE
      WHEN LOWER(c.concept_name) LIKE "%congenital hypothyroidism%" THEN "congenital hypothyroidism"
      ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string 
    FROM 
      CHCO_DeID_Oct2018.concept c
   WHERE c.domain_id = "Condition"
  ) 
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;

cystic_fibrosis.sql

-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
  CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
  c1.concept_name AS OMOP_LABEL,
  CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE, 
  CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
  c.vocabulary_id AS VOCABULARY

FROM CHCO_DeID_Oct2018.concept c 
  JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id 
  JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id 

WHERE r.relationship_id IN ("Maps to") 
  AND c1.standard_concept in ("S", "C") 
  AND c.concept_code IN ("277.0", "277.00", "E84", "E84.0", "E84.1", "E84.8", "E84.9") 
  AND c.vocabulary_id IN ("ICD9", "ICD9CM", "ICD10", "ICD10CM") 
  AND c.domain_id = "Condition"
  AND c1.vocabulary_id = "SNOMED" 
  AND c1.domain_id = "Condition"

ORDER BY CLINICAL_CODE, OMOP_ID;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
  search_string AS SEARCH_STRING,
  CONCAT('"', concept_id, '"') AS OMOP_ID, 
  concept_name AS OMOP_LABEL,
  CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
  vocabulary_id AS VOCABULARY
FROM 
  (
    SELECT 
      DISTINCT c.concept_name, 
      CAST(c.concept_id AS STRING) AS concept_id,
      c.concept_code, 
      c.domain_id, 
      c.vocabulary_id, 
      CASE
      WHEN LOWER(c.concept_name) LIKE "%cystic fibrosis%" THEN "cystic fibrosis"
      ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string 
    FROM 
      CHCO_DeID_Oct2018.concept c
   WHERE c.domain_id = "Condition"
  ) 
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;

muscular_dystrophy.sql

-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
  CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
  c1.concept_name AS OMOP_LABEL,
  CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
  CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE, 
  c.vocabulary_id AS VOCABULARY

FROM CHCO_DeID_Oct2018.concept c 
  JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id 
  JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id 

WHERE r.relationship_id IN ("Maps to") 
  AND c1.standard_concept in ("S", "C") 
  AND c.concept_code IN ("121230", "F391800", "387732009", "111507009", "121228", "G552.11", "195016002", "10059117", "129619006", "F391B00", "315608004", "193234005", "677C300", "758921000000101", "66731000119103", "677C200", "758911000000107", "137511000119103", "141683", "10013801", "F391000", "76670001", "267712004", "155095006", "138743", "359.1", "10019897", "F391.00", "193225000", "44292004", "F391z00", "193236007", "240050008", "C562928", "133622", "G71.0", "10028356", "3303", "F39B.00", "73297009", "193257004", "10028357", "240046001", "D020388", "F39z.00", "193261005", "240071003", "240048000", "240047005") 
  AND c.vocabulary_id IN ("CIEL", "Read", "SNOMED","SNOMED", "CIEL", "Read", "SNOMED", "MedDRA", "SNOMED", "Read", "SNOMED", "SNOMED", "Read", "SNOMED","SNOMED", "Read", "SNOMED", "SNOMED", "CIEL", "MedDRA","Read", "SNOMED", "SNOMED", "SNOMED", "CIEL", "ICD9CM", "MedDRA", "Read", "SNOMED", "SNOMED", "Read","SNOMED", "SNOMED", "MeSH", "CIEL","ICD10", "MedDRA", "OXMIS", "Read", "SNOMED", "SNOMED", "MedDRA","SNOMED", "MeSH", "Read", "SNOMED", "SNOMED", "SNOMED", "SNOMED") 
  AND c.domain_id = "Condition"
  AND c1.vocabulary_id = "SNOMED" 
  AND c1.domain_id = "Condition"

ORDER BY SNOMED_CODE, VOCABULARY;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
  search_string AS SEARCH_STRING,
  CONCAT('"', concept_id, '"') AS OMOP_ID, 
  concept_name AS OMOP_LABEL,
  CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
  vocabulary_id AS VOCABULARY
FROM 
  (
    SELECT 
      DISTINCT c.concept_name, 
      CAST(c.concept_id AS STRING) AS concept_id,
      c.concept_code, 
      c.domain_id, 
      c.vocabulary_id, 
      CASE
      WHEN LOWER(c.concept_name) LIKE "%muscular dystrophy%" THEN "muscular dystrophy"
      WHEN LOWER(c.concept_name) LIKE "%duchenne%" THEN "duchenne muscular dystrophy" 
      WHEN LOWER(c.concept_name) LIKE "%becker%" THEN "becker muscular dystrophy"
      WHEN LOWER(c.concept_name) LIKE "%severe%dystrophinopathy%" THEN "severe dystrophinopathy"
      ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string 
    FROM 
      CHCO_DeID_Oct2018.concept c
   WHERE c.domain_id = "Condition"
  ) 
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

- Query 2: Get age in yrs at first diagnosis
SELECT DISTINCT co.person_id,'muscular dystrophy' AS cond, 3 AS cond_num, 'MD' AS cond_short, p.birth_datetime,
min(co.condition_start_datetime) AS cond_start,
TIMESTAMP_DIFF(min(co.condition_start_datetime), p.birth_datetime, DAY)/365.25 AS age_at_first_dx
FROM CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.person p
ON p.person_id = co.person_id

WHERE co.condition_concept_id IN (4296473, 4154093, 4308668)
GROUP BY co.person_id, cond, cond_num, cond_short, p.birth_datetime
ORDER BY co.person_id

-- Query 3: Final code set to identify MD patients (Nov2019 Update)

SELECT DISTINCT
  CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
  c1.concept_name AS OMOP_LABEL,
  CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
  CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE, 
  c.vocabulary_id AS VOCABULARY

FROM CHCO_DeID_Oct2018.concept c 
  JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id 
  JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id 

WHERE r.relationship_id IN ("Maps to") 
  AND c1.standard_concept in ("S", "C") 
  AND c.concept_code IN ("121230", "F391800", "387732009", "111507009", "121228", "G552.11", "195016002", "10059117", "129619006", "F391B00", "315608004", "193234005", "677C300", "758921000000101", "66731000119103", "677C200", "758911000000107", "137511000119103", "141683", "10013801", "F391000", "76670001", "267712004", "155095006", "138743", "359.1", "10019897", "F391.00", "193225000", "44292004", "F391z00", "193236007", "240050008", "C562928", "133622", "G71.0", "10028356", "3303", "F39B.00", "73297009", "193257004", "10028357", "240046001", "D020388", "F39z.00", "193261005", "240071003", "240048000", "240047005") 
  AND c.vocabulary_id IN ("CIEL", "Read", "SNOMED","SNOMED", "CIEL", "Read", "SNOMED", "MedDRA", "SNOMED", "Read", "SNOMED", "SNOMED", "Read", "SNOMED","SNOMED", "Read", "SNOMED", "SNOMED", "CIEL", "MedDRA","Read", "SNOMED", "SNOMED", "SNOMED", "CIEL", "ICD9CM", "MedDRA", "Read", "SNOMED", "SNOMED", "Read","SNOMED", "SNOMED", "MeSH", "CIEL","ICD10", "MedDRA", "OXMIS", "Read", "SNOMED", "SNOMED", "MedDRA","SNOMED", "MeSH", "Read", "SNOMED", "SNOMED", "SNOMED", "SNOMED")
  
  # adding exclusion criteria from MD clinic meeting
  AND c1.concept_id NOT IN (80399, 4247802, 72618)
  
  AND c.domain_id = "Condition"
  AND c1.vocabulary_id = "SNOMED" 
  AND c1.domain_id = "Condition"

ORDER BY SNOMED_CODE, VOCABULARY;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- Look at counts of exclusion codes for eligible patients
SELECT DISTINCT co.person_id,
IFNULL(sq1.HPMD_COUNT, 0) AS HPMD_COUNT,
IFNULL(sq2.MD_COUNT,0) AS MD_COUNT,
IFNULL(sq3.DSM_COUNT,0) AS DSM_COUNT,   
IFNULL(sq4.BMD_COUNT, 0) AS BMD_COUNT,
IFNULL(sq5.CDMD_COUNT,0) AS CDMD_COUNT,
IFNULL(sq6.DMD_COUNT,0) AS DMD_COUNT 

FROM CHCO_DeID_Oct2018.condition_occurrence co

LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS DMD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 4296473
GROUP BY person_id
ORDER BY person_id) sq6
ON co.person_id = sq6.person_id 

LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS CDMD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 4154093
GROUP BY person_id
ORDER BY person_id) sq5
ON co.person_id = sq5.person_id 

LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS BMD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 4308668
GROUP BY person_id
ORDER BY person_id) sq4
ON co.person_id = sq4.person_id 

LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS DSM_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 72618
GROUP BY person_id
ORDER BY person_id) sq3
ON co.person_id = sq3.person_id 

LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS MD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 4247802
GROUP BY person_id
ORDER BY person_id) sq2
ON co.person_id = sq2.person_id 

LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS HPMD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 80399
GROUP BY person_id
ORDER BY person_id) sq1
ON co.person_id = sq1.person_id 

WHERE co.person_id IN (SELECT DISTINCT person_id
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id IN (4296473, 4154093, 4308668, 80399, 4247802, 72618))

-- get MD cohort
SELECT DISTINCT co.person_id,
'muscular dystrophy' AS cond,
p.birth_datetime,
condition_concept_id,
min(co.condition_start_datetime) AS cond_start,
TIMESTAMP_DIFF(min(co.condition_start_datetime), p.birth_datetime, DAY)/325.25 AS age_at_first_dx
FROM CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.person p
ON p.person_id = co.person_id
WHERE co.condition_concept_id IN (4296473, 4154093, 4308668)
GROUP BY co.person_id, cond, p.birth_datetime, condition_concept_id
ORDER BY co.person_id


-- get counts of visits
SELECT DISTINCT v.person_id,
c1.concept_name AS VISIT_TYPE,
c2.concept_name AS SPEC_CONCEPT,
COUNT(v.visit_occurrence_id) AS COUNT

FROM CHCO_DeID_Oct2018.visit_occurrence v
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.care_site cs
ON cs.care_site_id = v.care_site_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = cs.specialty_concept_id

WHERE v.person_id IN (SELECT DISTINCT co.person_id
  FROM CHCO_DeID_Oct2018.condition_occurrence co
  WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND v.visit_concept_id != 0
GROUP BY v.person_id, c1.concept_name, c2.concept_name
ORDER BY v.person_id ASC


-- Most frequent conditions
SELECT DISTINCT co.condition_concept_id,
co.condition_source_value,
c3.concept_name AS CODE,
c1.concept_name AS VISIT_TYPE,
COUNT(co.condition_concept_id) AS COUNT

FROM CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON v.visit_occurrence_id = co.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.care_site cs
ON cs.care_site_id = v.care_site_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = co.condition_concept_id

WHERE co.person_id IN (SELECT DISTINCT co.person_id
  FROM CHCO_DeID_Oct2018.condition_occurrence co
  WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND co.condition_concept_id != 0
GROUP BY c1.concept_name, co.condition_concept_id, c3.concept_name, co.condition_source_value
ORDER BY c3.concept_name, c1.concept_name DESC


-- Most frequent drugs
SELECT DISTINCT d.drug_concept_id,
c3.concept_name AS CODE,
d.drug_source_value,
d.frequency,
c1.concept_name AS VISIT_TYPE,
c4.concept_name,
COUNT(d.drug_concept_id) AS COUNT

FROM CHCO_DeID_Oct2018.drug_exposure d
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON v.visit_occurrence_id = d.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.care_site cs
ON cs.care_site_id = v.care_site_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = d.drug_concept_id
JOIN CHCO_DeID_Oct2018.concept c4
ON d.drug_type_concept_id = c4.concept_id

WHERE d.person_id IN (SELECT DISTINCT co.person_id
  FROM CHCO_DeID_Oct2018.condition_occurrence co
  WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND d.drug_concept_id != 0
GROUP BY c1.concept_name, d.drug_concept_id, c3.concept_name, c3.concept_class_id, c4.concept_name, drug_source_value, d.frequency
ORDER BY c3.concept_name, c1.concept_name DESC


-- Most frequent measurements
SELECT DISTINCT m.measurement_concept_id,
c3.concept_name AS CODE,
c1.concept_name AS VISIT_TYPE,
COUNT(m.measurement_concept_id) AS COUNT

FROM CHCO_DeID_Oct2018.measurement m
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON v.visit_occurrence_id = m.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.care_site cs
ON cs.care_site_id = v.care_site_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = m.measurement_concept_id

WHERE m.person_id IN (SELECT DISTINCT co.person_id
  FROM CHCO_DeID_Oct2018.condition_occurrence co
  WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND m.measurement_concept_id != 0
GROUP BY c1.concept_name, m.measurement_concept_id, c3.concept_name
ORDER BY c3.concept_name, c1.concept_name DESC


-- Most frequent procedures
SELECT DISTINCT p.procedure_concept_id,
c3.concept_name AS CODE,
c1.concept_name AS VISIT_TYPE,
c3.domain_id,
COUNT(p.procedure_concept_id) AS COUNT
FROM CHCO_DeID_Oct2018.procedure_occurrence p
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON v.visit_occurrence_id = p.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = p.procedure_concept_id

WHERE p.person_id IN (SELECT DISTINCT co.person_id
  FROM CHCO_DeID_Oct2018.condition_occurrence co
  WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND p.procedure_concept_id != 0
GROUP BY c1.concept_name, p.procedure_concept_id, c3.concept_name, c3.domain_id
ORDER BY c3.concept_name, c1.concept_name DESC;

other_patients.sql

-- RANDOM PATIENTS: Query is designed to retrieve 15,000 random patients having >9 visits
SELECT 
  v.person_id AS patient_id, 
  count(v.visit_occurrence_id) AS visit_count, 
  p_dat.cond As disease_group_label, 
  p_dat.cond_num As disease_group_num, 
  p_dat.cond_short As disease_group_abbreviation, 
  p_dat.DOB AS date_of_birth, 
  p_dat.cond_start AS earliest_dx_date, 
  p_dat.age_at_dx AS age_at_earliest_dx 
FROM 
  CHCO_DeID_Oct2018.visit_occurrence v 
  RIGHT JOIN (
    SELECT 
      co.person_id, 
      'Control' AS cond, 
      99 AS cond_num, 
      'Control' AS cond_short, 
      min(co.condition_start_date) AS cond_start, 
      DATE_DIFF(
        min(co.condition_start_date), 
        CAST(
          DATE(p.birth_datetime) AS DATE
        ), 
        DAY
      )/ 325.25 AS age_at_dx, 
      p.birth_datetime AS DOB 
    FROM 
      CHCO_DeID_Oct2018.condition_occurrence co 
      JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id 
    WHERE 
      p.person_id NOT IN (
        SELECT 
          patient_id 
        FROM 
          CHCO_DeID_Oct2018.Med2Mech_RareDisease_CasePatients
      ) 
      AND p.person_id IS NOT NULL 
    GROUP BY 
      co.person_id, 
      cond, 
      cond_num, 
      cond_short, 
      p.birth_datetime 
    ORDER BY 
      co.person_id
  ) AS p_dat ON v.person_id = p_dat.person_id 
WHERE 
  v.visit_occurrence_id IS NOT NULL 
GROUP BY 
  v.person_id, 
  p_dat.cond, 
  p_dat.cond_num, 
  p_dat.cond_short, 
  date_of_birth, 
  p_dat.cond_start, 
  p_dat.age_at_dx 
HAVING 
  visit_count > 9 
ORDER BY 
  RAND() 
LIMIT 
  15000;

phenylketonuria.sql

-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
  CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
  c1.concept_name AS OMOP_LABEL,
  CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE, 
  CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
  c.vocabulary_id AS VOCABULARY

FROM CHCO_DeID_Oct2018.concept c 
  JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id 
  JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id 

WHERE r.relationship_id IN ("Maps to") 
  AND c1.standard_concept in ("S", "C") 
  AND c.concept_code IN ("E70.0", "270.1") 
  AND c.vocabulary_id IN ("ICD9", "ICD9CM", "ICD10", "ICD10CM") 
  AND c.domain_id = "Condition"
  AND c1.vocabulary_id = "SNOMED" 
  AND c1.domain_id = "Condition"

ORDER BY CLINICAL_CODE, OMOP_ID;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
  search_string AS SEARCH_STRING,
  CONCAT('"', concept_id, '"') AS OMOP_ID, 
  concept_name AS OMOP_LABEL,
  CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
  vocabulary_id AS VOCABULARY
FROM 
  (
    SELECT 
      DISTINCT c.concept_name, 
      CAST(c.concept_id AS STRING) AS concept_id,
      c.concept_code, 
      c.domain_id, 
      c.vocabulary_id, 
      CASE
      WHEN LOWER(c.concept_name) LIKE "%phenylketonuria%" THEN "phenylketonuria"
      ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string 
    FROM 
      CHCO_DeID_Oct2018.concept c
   WHERE c.domain_id = "Condition"
  ) 
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;

rare_disease_patients.sql

-- RARE DISEASE CASES: Query is designed to retrieve test-cases having specific concept codes indicative of CF, DMD, and SC as well as having >9 visits
SELECT 
  v.person_id AS patient_id, 
  count(v.visit_occurrence_id) AS visit_count, 
  p_dat.cond As disease_group_label, 
  p_dat.cond_num As disease_group_num, 
  p_dat.cond_short As disease_group_abbreviation, 
  p_dat.birth_datetime as date_of_birth, 
  p_dat.cond_start AS earliest_dx_date, 
  p_dat.age_at_dx AS age_at_earliest_dx 
FROM 
  CHCO_DeID_Oct2018.visit_occurrence v 
  JOIN CHCO_DeID_Oct2018.person d ON d.person_id = v.person_id 
  RIGHT JOIN (
    SELECT 
      * 
    FROM 
      (
        SELECT 
          co.person_id, 
          'Phenylketonuria' AS cond, 
          1 AS cond_num, 
          'PKU' AS cond_short, 
          p.birth_datetime, 
          min(co.condition_start_datetime) AS cond_start, 
          TIMESTAMP_DIFF(
            min(co.condition_start_datetime), 
            p.birth_datetime, 
            DAY
          )/ 325.25 AS age_at_dx 
        FROM 
          CHCO_DeID_Oct2018.condition_occurrence co 
          JOIN CHCO_DeID_Oct2018.concept c1 ON c1.concept_id = co.condition_concept_id 
          JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id 
        WHERE 
          c1.vocabulary_id = 'SNOMED' 
          AND c1.domain_id = 'Condition' 
          AND c1.concept_id IN (
            432872, 45576461, 35207031, 45576462, 
            35207032, 44821794
          ) 
        GROUP BY 
          co.person_id, 
          cond, 
          cond_num, 
          cond_short, 
          p.birth_datetime 
        ORDER BY 
          co.person_id
      ) 
    UNION ALL 
      (
        SELECT 
          co.person_id, 
          'Congenital Hypothyroidism' AS cond, 
          2 AS cond_num, 
          'CAH' AS cond_short, 
          p.birth_datetime, 
          min(co.condition_start_datetime) AS cond_start, 
          TIMESTAMP_DIFF(
            min(co.condition_start_datetime), 
            p.birth_datetime, 
            DAY
          )/ 325.25 AS age_at_dx 
        FROM 
          CHCO_DeID_Oct2018.condition_occurrence co 
          JOIN CHCO_DeID_Oct2018.concept c1 ON c1.concept_id = co.condition_concept_id 
          JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id 
        WHERE 
          c1.vocabulary_id = 'SNOMED' 
          AND c1.domain_id = 'Condition' 
          AND c1.concept_id IN (
            44829873, 133728, 35206853, 45576424, 
            45524819
          ) 
        GROUP BY 
          co.person_id, 
          cond, 
          cond_num, 
          cond_short, 
          p.birth_datetime 
        ORDER BY 
          co.person_id
      ) 
    UNION ALL 
      (
        SELECT 
          co.person_id, 
          'Sickle Cell Disease' AS cond, 
          3 AS cond_num, 
          'SCD' AS cond_short, 
          p.birth_datetime, 
          min(co.condition_start_datetime) AS cond_start, 
          TIMESTAMP_DIFF(
            min(co.condition_start_datetime), 
            p.birth_datetime, 
            DAY
          )/ 325.25 AS age_at_dx 
        FROM 
          CHCO_DeID_Oct2018.condition_occurrence co 
          JOIN CHCO_DeID_Oct2018.concept c1 ON c1.concept_id = co.condition_concept_id 
          JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id 
        WHERE 
          c1.vocabulary_id = 'SNOMED' 
          AND c1.domain_id = 'Condition' 
          AND c1.concept_id IN (
            45566700, 22281, 1567845, 45571634, 
            45542709, 35206724, 44831065, 1567846
          ) 
        GROUP BY 
          co.person_id, 
          cond, 
          cond_num, 
          cond_short, 
          p.birth_datetime 
        ORDER BY 
          co.person_id
      ) 
    UNION ALL 
      (
        SELECT 
          co.person_id, 
          'Cystic Fibrosis' AS cond, 
          4 AS cond_num, 
          'CF' AS cond_short, 
          p.birth_datetime, 
          min(co.condition_start_datetime) AS cond_start, 
          TIMESTAMP_DIFF(
            min(co.condition_start_datetime), 
            p.birth_datetime, 
            DAY
          )/ 325.25 AS age_at_dx 
        FROM 
          CHCO_DeID_Oct2018.condition_occurrence co 
          JOIN CHCO_DeID_Oct2018.concept c1 ON c1.concept_id = co.condition_concept_id 
          JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id 
        WHERE 
          c1.vocabulary_id = 'SNOMED' 
          AND c1.domain_id = 'Condition' 
          AND c1.concept_id IN (
            441267, 45605436, 1568075, 45605437, 
            35207083, 45605435, 35207082, 44821799, 
            45576477, 35207084
          ) 
        GROUP BY 
          co.person_id, 
          cond, 
          cond_num, 
          cond_short, 
          p.birth_datetime 
        ORDER BY 
          co.person_id
      )
  ) AS p_dat ON v.person_id = p_dat.person_id 
GROUP BY 
  v.person_id, 
  p_dat.cond, 
  p_dat.cond_num, 
  p_dat.cond_short, 
  p_dat.birth_datetime, 
  p_dat.cond_start, 
  p_dat.age_at_dx 
HAVING 
  visit_count > 9 
ORDER BY 
  visit_count;

sickle_cell_disease.sql

-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
  CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
  c1.concept_name AS OMOP_LABEL,
  CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE, 
  CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
  c.vocabulary_id AS VOCABULARY

FROM CHCO_DeID_Oct2018.concept c 
  JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id 
  JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id 

WHERE r.relationship_id IN ("Maps to") 
  AND c1.standard_concept in ("S", "C") 
  AND c.concept_code IN ("D57.0", "D57.1", "D57.2", "282.6", "282.60", "282.61", "282.68") 
  AND c.vocabulary_id IN ("ICD9", "ICD9CM", "ICD10", "ICD10CM") 
  AND c.domain_id = "Condition"
  AND c1.vocabulary_id = "SNOMED" 
  AND c1.domain_id = "Condition"

ORDER BY CLINICAL_CODE, OMOP_ID;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
  search_string AS SEARCH_STRING,
  CONCAT('"', concept_id, '"') AS OMOP_ID, 
  concept_name AS OMOP_LABEL,
  CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
  vocabulary_id AS VOCABULARY
FROM 
  (
    SELECT 
      DISTINCT c.concept_name, 
      CAST(c.concept_id AS STRING) AS concept_id,
      c.concept_code, 
      c.domain_id, 
      c.vocabulary_id, 
      CASE
      WHEN LOWER(c.concept_name) LIKE "%sickle%cell disease%" THEN "sickle-cell disease"
      WHEN LOWER(c.concept_name) LIKE "%hb%ss%disease%" THEN "Hb SS disease"
      ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string 
    FROM 
      CHCO_DeID_Oct2018.concept c
   WHERE c.domain_id = "Condition"
  ) 
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;