inst/QueryLibrary/Aggregate/condition_era/CE08.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

CE08: Number of comorbidities for patients with condition

Description

Calculated the number of comorbidities for patients with a specific condition

Query

WITH snomed_diabetes AS (
SELECT ca.descendant_concept_id AS snomed_diabetes_id
  FROM @vocab.concept c
  JOIN @vocab.concept_ancestor ca
    ON ca.ancestor_concept_id = c.concept_id
 WHERE c.concept_code = '73211009'
),  people_with_diabetes AS (
SELECT ce.person_id,
       MIN(ce.condition_era_start_date) AS onset_date
  FROM @cdm.condition_era ce
  JOIN snomed_diabetes sd
    ON sd.snomed_diabetes_id = ce.condition_concept_id
 GROUP BY ce.person_id
), non_diabetic AS (
SELECT person_id,
       condition_concept_id,
       condition_era_start_date
  FROM @cdm.condition_era
 WHERE condition_concept_id NOT IN (SELECT snomed_diabetes_id FROM snomed_diabetes)
), comorbidities_by_person AS (
SELECT diabetic.person_id,
       COUNT(DISTINCT comorb.condition_concept_id) AS comorbidities         
  FROM people_with_diabetes diabetic
  JOIN non_diabetic comorb
    ON comorb.person_id = diabetic.person_id
   AND comorb.condition_era_start_date > diabetic.onset_date
  JOIN @vocab.concept c
    ON c.concept_id = comorb.condition_concept_id
 GROUP BY diabetic.person_id
), ordered_data AS (
SELECT comorbidities,
       DENSE_RANK()OVER(ORDER BY comorbidities) order_nr,
       MIN(comorbidities)OVER() AS min_value,
       MAX(comorbidities)OVER() AS max_value,
       AVG(comorbidities)OVER() AS avg_value
  FROM comorbidities_by_person
)
SELECT min_value::numeric,max_value::numeric,round(avg_value::numeric,2),
       MIN(CASE WHEN order_nr < .25 * max_value THEN 999999999999 ELSE comorbidities END)::numeric AS pct_25,
       MIN(CASE WHEN order_nr < .50 * max_value THEN 999999999999 ELSE comorbidities END)::numeric AS median,
       MIN(CASE WHEN order_nr < .75 * max_value THEN 999999999999 ELSE comorbidities END)::numeric AS pct_75
  FROM ordered_data
 GROUP BY min_value,max_value,avg_value;

Input

None

Output

| Field | Description | | --- | --- | | min | | | max | | | average | | | percentile_25 | | | median | | | percentile_75 | |

Documentation

https://github.com/OHDSI/CommonDataModel/wiki/

sib-swiss/dsQueryLibraryServer documentation built on Feb. 13, 2025, 8:07 p.m.