CONDITION ERA‎ > ‎

CE07: Comorbidities of patient with condition

This query counts the top ten comorbidities for patients with diabetes

Input:
 Parameter  Example  Mandatory  Notes
condition_era_end_date    

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue
WITH SNOMed_diabetes
  AS ( SELECT DISTINCT descendant_concept_id AS snomed_diabetes_id
         FROM vocabulary.source_to_concept_map map
         JOIN vocabulary.concept_ancestor ON ancestor_concept_id = target_concept_id
        WHERE source_vocabulary_id = 2 /* icd9 */
          AND target_vocabulary_id = 1 /* SNOMed */
          AND source_code LIKE '250.%'
          AND sysdate BETWEEN valid_start_date AND valid_end_date
          AND primary_map = 'Y'
     )
SELECT comorbidity, frequency
  FROM /* top 10 */
     ( SELECT comorbidity, count(*) frequency
         FROM /* commorbidities for patients with diabetes */
            ( SELECT DISTINCT diabetic.person_id, concept_name AS comorbidity
                FROM /* people with diabetes/onset date */       
                   ( SELECT person_id, MIN( condition_era_start_date ) AS onset_date
                      FROM condition_era 
                      JOIN SNOMed_diabetes ON snomed_diabetes_id = condition_concept_id
                     GROUP BY person_id
                   ) diabetic
                JOIN /* condition after onset date, that are not diabetes */
                   ( SELECT person_id, condition_concept_id, condition_era_start_date
                       FROM condition_era
                      WHERE condition_concept_id
                        NOT IN( SELECT snomed_diabetes_id
                                  FROM SNOMed_diabetes
                              )
                   ) comorb ON comorb.person_id = diabetic.person_id
                           AND comorb.condition_era_start_date > diabetic.onset_date
               JOIN vocabulary.concept ON concept_id = comorb.condition_concept_id
            )
        GROUP BY comorbidity
        ORDER BY frequency DESC
     )
 WHERE rownum <= 10;
Output:
Output field list:
 Field  Description
concept_name An unambiguous, meaningful and descriptive name for the concept
person_id A system-generated unique identifier for each person.
ancestor_concept_id A foreign key to the concept code in the concept table for the higher-level concept that forms the ancestor in the relationship.
target_concept_id A foreign key to the concept to which the source code is being mapped.
valid_start_date The date when the mapping instance was first recorded.
valid_end_date The date when the mapping instance became invalid because it was deleted or superseded (updated) by a new relationship. Default value is 31-Dec-2099.
condition_concept_id A foreign key that refers to a standard condition concept identifier in the vocabulary.
condition_era_start_date The start date for the condition era constructed from the individual instances of condition occurrences. It is the start date of the very first chronologically recorded instance of the condition.
   

Sample output record:
 Field  Description
concept_name  
person_id  
condition_concept_id  
Comments