COC05: Mortality rate after initial diagnosis



Input:
 Parameter  Example  Mandatory  Notes
concept_name OMOP Acute Myocardial Infarction 1 Yes

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue

SELECT COUNT( DISTINCT diagnosed.person_id ) AS all_infarctions
     , SUM( CASE WHEN death.person_id IS NULL
                 THEN 0
                 ELSE 1
            END
          ) AS death_from_infarction
  FROM -- Initial diagnosis of Acute Myocardial Infarction
     ( SELECT DISTINCT person_id, condition_era_start_date
         FROM  /* diagnosis of Acute Myocardial Infarction, ranked by
                  date, 6 month clean period with 1 year follow-up
                */
            ( SELECT condition.person_id, condition.condition_era_start_date
                   , sum(1) OVER( PARTITION BY condition.person_id
                                      ORDER BY condition_era_start_date ROWS UNBOUNDED PRECEDING
                                ) AS ranking
                FROM condition_era condition
                JOIN -- definition of Acute Myocardial Infarction 1
                   ( SELECT DISTINCT descendant_concept_id
                       FROM vocabulary.relationship
                       JOIN vocabulary.concept_relationship rel USING( relationship_id ) 
                       JOIN vocabulary.concept concept1 ON concept1.concept_id = concept_id_1
                       JOIN vocabulary.concept_ancestor ON ancestor_concept_id = concept_id_2
                      WHERE relationship_name = 'HOI contains SNOMED (OMOP)'
                        AND concept1.concept_name = 'OMOP Acute Myocardial Infarction 1'
                        AND sysdate BETWEEN rel.valid_start_date and rel.valid_end_date
                   ) ON descendant_concept_id = condition_concept_id
                JOIN observation_period obs
                  ON obs.person_id = condition.person_id
                 AND condition_era_start_date 
                     BETWEEN observation_period_start_date  + 180
                         AND observation_period_end_date - 360
            )
        WHERE ranking = 1
     ) diagnosed
  LEFT OUTER JOIN death /* death within a year */
    ON death.person_id = diagnosed.person_id
   AND death.death_date <= condition_era_start_date + 360;
Output:
Output field list:
 Field  Description
condition_concept_id A foreign key that refers to a standard condition concept identifier in the vocabulary.
concept_name An unambiguous, meaningful and descriptive name for the concept.
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.
person_id A foreign key identifier to the person who is experiencing the condition during the condition era. The demographic details of that person are stored in the person table.
death_date The date the person deceased. If the precise date including day or month is not known or not allowed, December is used as the default month, and the last day of the month the default day.

Sample output record:
 Field  Description
condition_concept_id  
concept_name  
condition_era_start_date  
person_id  
death_date  
Comments