Med2Mech Patient Queries - https://github.com/callahantiff/Med2Mech/wiki

all_clinical_domains.SQL

SELECT * FROM
(SELECT patient_id, disease_group_label, disease_group_num, disease_group_abbreviation, condition_concept_id AS concept_id, visit_occurrence_id, visit_start_interval
 FROM CHCO_DeID_Oct2018.Med2Mech_RareDisease_Conditions
 WHERE visit_start_interval>0 AND visit_end_interval>0)

UNION ALL

(SELECT patient_id, disease_group_label, disease_group_num, disease_group_abbreviation, drug_concept_id AS concept_id, visit_occurrence_id, visit_start_interval 
 FROM CHCO_DeID_Oct2018.Med2Mech_RareDisease_Medications
 WHERE visit_start_interval>0 AND visit_end_interval>0)

UNION ALL

(SELECT patient_id, disease_group_label, disease_group_num, disease_group_abbreviation, measurement_concept_id AS concept_id, visit_occurrence_id, visit_start_interval  
 FROM CHCO_DeID_Oct2018.Med2Mech_RareDisease_Measurements
 WHERE visit_start_interval>0 AND visit_end_interval>0) ;

condition_data.SQL

SELECT * FROM
((SELECT mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, co.condition_occurrence_id,
co.condition_concept_id, c1.concept_code AS condition_code, c1.concept_name AS condition_name, c3.concept_name AS condition_type, v.visit_occurrence_id, c2.concept_name AS visit_type, TIMESTAMP_DIFF(co.condition_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS condition_start_interval, TIMESTAMP_DIFF(co.condition_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS condition_end_interval, TIMESTAMP_DIFF(v.visit_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_start_interval, TIMESTAMP_DIFF(v.visit_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_end_interval,

CASE WHEN co.condition_concept_id IN (22281, 133728, 432872, 441267, 441267, 1567845, 1567846, 1568075, 35206724, 35206853, 35207031, 35207032, 35207082, 35207083, 35207084, 44821794, 44821799, 44829873, 44831065, 45524819, 45542709, 45566700, 45571634, 45576424, 45576461, 45576462, 45576477, 45605435, 45605436, 45605437) THEN 1 ELSE 0 END AS disease_group_dx_flag

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.Med2Mech_RareDisease_CasePatients mm
ON co.person_id = mm.patient_id
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON co.visit_occurrence_id = v.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = co.condition_type_concept_id

WHERE c1.domain_id = 'Condition' AND c1.vocabulary_id = 'SNOMED'
GROUP BY mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation,  co.condition_occurrence_id, co.condition_concept_id, condition_code, condition_name, condition_type, v.visit_occurrence_id, visit_type, condition_start_interval, condition_end_interval, visit_start_interval, visit_end_interval, disease_group_dx_flag
ORDER BY mm.patient_id, v.visit_occurrence_id)

UNION ALL

(SELECT mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, co.condition_occurrence_id,
co.condition_concept_id, c1.concept_code AS condition_code, c1.concept_name AS condition_name, c3.concept_name AS condition_type, v.visit_occurrence_id, c2.concept_name AS visit_type, TIMESTAMP_DIFF(co.condition_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS condition_start_interval, TIMESTAMP_DIFF(co.condition_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS condition_end_interval, TIMESTAMP_DIFF(v.visit_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_start_interval, TIMESTAMP_DIFF(v.visit_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_end_interval,

CASE WHEN co.condition_concept_id IN (22281, 133728, 432872, 441267, 441267, 1567845, 1567846, 1568075, 35206724, 35206853, 35207031, 35207032, 35207082, 35207083, 35207084, 44821794, 44821799, 44829873, 44831065, 45524819, 45542709, 45566700, 45571634, 45576424, 45576461, 45576462, 45576477, 45605435, 45605436, 45605437) THEN 1 ELSE 0 END AS disease_group_dx_flag

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.Med2Mech_RareDisease_ControlPatients mm
ON co.person_id = mm.patient_id
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON co.visit_occurrence_id = v.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = co.condition_type_concept_id

WHERE c1.domain_id = 'Condition' AND c1.vocabulary_id = 'SNOMED'
GROUP BY mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation,  co.condition_occurrence_id, co.condition_concept_id, condition_code, condition_name, condition_type, v.visit_occurrence_id, visit_type, condition_start_interval, condition_end_interval, visit_start_interval, visit_end_interval, disease_group_dx_flag
ORDER BY mm.patient_id, v.visit_occurrence_id))

WHERE visit_start_interval>0 AND visit_end_interval>0;

control_patients.SQL

-- RARE DISEASE - 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,
TIMESTAMP_DIFF(co.condition_start_datetime, p.birth_datetime, 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, co.condition_start_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;

drug_data.SQL

SELECT * FROM
((SELECT mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, de.drug_exposure_id,
de.drug_concept_id, c2.concept_name AS drug_type, c1.concept_code AS drug_code, c1.concept_name AS drug_name, c3.concept_id AS ingredient_id, c3.concept_code AS ingredient_code, c3.concept_name AS ingredient_name, c3.concept_class_id AS ingredient_class_id, de.days_supply, de.effective_drug_dose, de.quantity, de.refills, ds.amount_value, ds.numerator_value, c4.concept_name AS numerator_unit, ds.denominator_value, c5.concept_name AS denominator_unit, de.visit_occurrence_id, c6.concept_name AS visit_type, TIMESTAMP_DIFF(de.drug_exposure_order_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS drug_order_interval, TIMESTAMP_DIFF(de.drug_exposure_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS drug_exposure_start_interval, TIMESTAMP_DIFF(de.drug_exposure_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS drug_exposure_end_interval, TIMESTAMP_DIFF(v.visit_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_start_interval, TIMESTAMP_DIFF(v.visit_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_end_interval

FROM CHCO_DeID_Oct2018.drug_exposure de
JOIN CHCO_DeID_Oct2018.drug_strength ds
ON de.drug_concept_id = ds.drug_concept_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = de.drug_concept_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = de.drug_type_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = ds.ingredient_concept_id
JOIN CHCO_DeID_Oct2018.concept c4 
ON c4.concept_id = ds.numerator_unit_concept_id
JOIN CHCO_DeID_Oct2018.concept c5 
ON c5.concept_id = ds.denominator_unit_concept_id
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON de.visit_occurrence_id = v.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c6 
ON c6.concept_id = v.visit_type_concept_id
JOIN CHCO_DeID_Oct2018.Med2Mech_RareDisease_CasePatients mm
ON de.person_id = mm.patient_id

WHERE c1.domain_id = 'Drug' AND c1.vocabulary_id = 'RxNorm'

GROUP BY mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, de.drug_exposure_id, de.drug_concept_id, drug_type, drug_code, drug_name, ingredient_id, ingredient_code, ingredient_name, ingredient_class_id, de.days_supply, de.effective_drug_dose, de.quantity, de.refills,
ds.amount_value, ds.numerator_value, numerator_unit, ds.denominator_value, denominator_unit, de.visit_occurrence_id, visit_type, drug_order_interval, drug_exposure_start_interval, drug_exposure_end_interval, visit_start_interval, visit_end_interval
ORDER BY mm.patient_id)

UNION ALL

(SELECT mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, de.drug_exposure_id,
de.drug_concept_id, c2.concept_name AS drug_type, c1.concept_code AS drug_code, c1.concept_name AS drug_name, c3.concept_id AS ingredient_id, c3.concept_code AS ingredient_code, c3.concept_name AS ingredient_name, c3.concept_class_id AS ingredient_class_id, de.days_supply, de.effective_drug_dose, de.quantity, de.refills, ds.amount_value, ds.numerator_value, c4.concept_name AS numerator_unit, ds.denominator_value, c5.concept_name AS denominator_unit, de.visit_occurrence_id, c6.concept_name AS visit_type, TIMESTAMP_DIFF(de.drug_exposure_order_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS drug_order_interval, TIMESTAMP_DIFF(de.drug_exposure_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS drug_exposure_start_interval, TIMESTAMP_DIFF(de.drug_exposure_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS drug_exposure_end_interval, TIMESTAMP_DIFF(v.visit_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_start_interval, TIMESTAMP_DIFF(v.visit_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_end_interval

FROM CHCO_DeID_Oct2018.drug_exposure de
JOIN CHCO_DeID_Oct2018.drug_strength ds
ON de.drug_concept_id = ds.drug_concept_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = de.drug_concept_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = de.drug_type_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = ds.ingredient_concept_id
JOIN CHCO_DeID_Oct2018.concept c4 
ON c4.concept_id = ds.numerator_unit_concept_id
JOIN CHCO_DeID_Oct2018.concept c5 
ON c5.concept_id = ds.denominator_unit_concept_id
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON de.visit_occurrence_id = v.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c6 
ON c6.concept_id = v.visit_type_concept_id
JOIN CHCO_DeID_Oct2018.Med2Mech_RareDisease_ControlPatients mm
ON de.person_id = mm.patient_id

WHERE c1.domain_id = 'Drug' AND c1.vocabulary_id = 'RxNorm'

GROUP BY mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, de.drug_exposure_id, de.drug_concept_id, drug_type, drug_code, drug_name, ingredient_id, ingredient_code, ingredient_name, ingredient_class_id, de.days_supply, de.effective_drug_dose, de.quantity, de.refills,
ds.amount_value, ds.numerator_value, numerator_unit, ds.denominator_value, denominator_unit, de.visit_occurrence_id, visit_type, drug_order_interval, drug_exposure_start_interval, drug_exposure_end_interval, visit_start_interval, visit_end_interval
ORDER BY mm.patient_id))

WHERE visit_start_interval>0 AND visit_end_interval>0;

measurement_data.SQL

SELECT * FROM (
(SELECT mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, m.measurement_id, m.measurement_concept_id, c1.concept_code AS measurement_code, c1.concept_name AS measurement_label, c3.concept_name AS measurement_type, v.visit_occurrence_id, c2.concept_name AS visit_type, TIMESTAMP_DIFF(m.measurement_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS measurement_interval, TIMESTAMP_DIFF(m.measurement_order_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS measurement_order_interval, TIMESTAMP_DIFF(v.visit_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_start_interval, TIMESTAMP_DIFF(v.visit_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_end_interval,
m.value_as_number AS result, m.range_low AS result_reference_range_low, m.range_high AS result_reference_range_high,

CASE 
WHEN CAST(m.value_as_number as numeric) > CAST(m.range_high AS numeric) THEN "High"
WHEN CAST(m.value_as_number as numeric) < CAST(m.range_low AS numeric) THEN "Low"
ELSE "Normal"
END AS lab_result

FROM CHCO_DeID_Oct2018.measurement m
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = m.measurement_concept_id
JOIN CHCO_DeID_Oct2018.Med2Mech_RareDisease_CasePatients mm
ON m.person_id = mm.patient_id
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON m.visit_occurrence_id = v.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = m.measurement_type_concept_id

WHERE c1.domain_id = 'Measurement' AND c1.vocabulary_id = 'LOINC'
GROUP BY mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, m.measurement_id, m.measurement_concept_id, measurement_code, measurement_label, measurement_type, v.visit_occurrence_id, visit_type, measurement_interval, measurement_order_interval, visit_start_interval, visit_end_interval, result, result_reference_range_low, result_reference_range_high, lab_result
ORDER BY mm.patient_id, v.visit_occurrence_id)

UNION ALL

(SELECT mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, m.measurement_id, m.measurement_concept_id, c1.concept_code AS measurement_code, c1.concept_name AS measurement_label, c3.concept_name AS measurement_type, v.visit_occurrence_id, c2.concept_name AS visit_type, TIMESTAMP_DIFF(m.measurement_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS measurement_interval, TIMESTAMP_DIFF(m.measurement_order_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS measurement_order_interval, TIMESTAMP_DIFF(v.visit_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_start_interval, TIMESTAMP_DIFF(v.visit_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_end_interval,
m.value_as_number AS result, m.range_low AS result_reference_range_low, m.range_high AS result_reference_range_high,

CASE 
WHEN CAST(m.value_as_number as numeric) > CAST(m.range_high AS numeric) THEN "High"
WHEN CAST(m.value_as_number as numeric) < CAST(m.range_low AS numeric) THEN "Low"
ELSE "Normal"
END AS lab_result

FROM CHCO_DeID_Oct2018.measurement m
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = m.measurement_concept_id
JOIN CHCO_DeID_Oct2018.Med2Mech_RareDisease_ControlPatients mm
ON m.person_id = mm.patient_id
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON m.visit_occurrence_id = v.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = m.measurement_type_concept_id

WHERE c1.domain_id = 'Measurement' AND c1.vocabulary_id = 'LOINC'
GROUP BY mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, m.measurement_id, m.measurement_concept_id, measurement_code, measurement_label, measurement_type, v.visit_occurrence_id, visit_type, measurement_interval, measurement_order_interval, visit_start_interval, visit_end_interval, result, result_reference_range_low, result_reference_range_high, lab_result
ORDER BY mm.patient_id, v.visit_occurrence_id))

WHERE visit_start_interval>0 AND visit_end_interval>0;

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;

visit_data.SQL

SELECT * FROM 
((SELECT mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, v.visit_occurrence_id, visit_concept_id, c1.concept_code As visit_code, c1.concept_name AS visit_name, c2.concept_name AS visit_type, c3.concept_name AS admitting_source, c4.concept_name AS discharge_to, TIMESTAMP_DIFF(v.visit_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_start_interval, TIMESTAMP_DIFF(v.visit_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_end_interval

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.Med2Mech_RareDisease_CasePatients mm
ON v.person_id = mm.patient_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = v.visit_type_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = v.admitting_source_concept_id
JOIN CHCO_DeID_Oct2018.concept c4
ON c4.concept_id = v.discharge_to_source_concept_id

GROUP BY mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, v.visit_occurrence_id, visit_concept_id, visit_code, visit_name, visit_type, admitting_source, discharge_to, visit_start_interval, visit_end_interval
ORDER BY mm.patient_id, v.visit_occurrence_id)

UNION ALL

(SELECT mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, v.visit_occurrence_id, visit_concept_id, c1.concept_code As visit_code, c1.concept_name AS visit_name, c2.concept_name AS visit_type, c3.concept_name AS admitting_source, c4.concept_name AS discharge_to, TIMESTAMP_DIFF(v.visit_start_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_start_interval, TIMESTAMP_DIFF(v.visit_end_datetime, mm.date_of_birth, MICROSECOND)/86400000000 AS visit_end_interval

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.Med2Mech_RareDisease_ControlPatients mm
ON v.person_id = mm.patient_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = v.visit_type_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = v.admitting_source_concept_id
JOIN CHCO_DeID_Oct2018.concept c4
ON c4.concept_id = v.discharge_to_source_concept_id

GROUP BY mm.patient_id, mm.disease_group_label, mm.disease_group_num, mm.disease_group_abbreviation, v.visit_occurrence_id, visit_concept_id, visit_code, visit_name, visit_type, admitting_source, discharge_to, visit_start_interval, visit_end_interval
ORDER BY mm.patient_id, v.visit_occurrence_id))

WHERE visit_end_interval>0 AND visit_start_interval>0;