CONDITION ERA‎ > ‎

CE16: Distribution of condition era length, stratified by condition and condition type

This query is used to provide summary statistics for the condition era length across all condition era records stratified by condition (condition_concept_id) and condition type (condition_type_concept_id, in CDM V2 condition_occurrence_type): the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. The length of an era is defined as the difference between the start date and the end date. The input to the query is a value (or a comma-separated list of values) of a condition_concept_id and a condition_type_concept_id. If the input is omitted, all existing value combinations are summarized.

Input:
 Parameter  Example  Mandatory  Notes
condition_concept_id   No  
condition_type_concept_id   No  

Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue
SELECT condition_concept_id, condition_type_concept_id
     , min( condition_era_length ) AS min
     , max( condition_era_length ) AS max
     , round( avg( condition_era_length ), 2 ) AS avg
     , round( stdDev( condition_era_length ) ) AS stdDev
     , PERCENTILE_DISC(0.25) WITHIN GROUP( ORDER BY condition_era_length ) 
                                     AS percentile_25
     , PERCENTILE_DISC(0.5)  WITHIN GROUP (ORDER BY condition_era_length )
                                     AS median
     , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY condition_era_length )
                                     AS percential_75
  FROM /* condition_concept, condition_type, condition_era_length */
     ( SELECT condition_concept_id, condition_type_concept_id
            , condition_era_end_date - condition_era_start_date + 1 
              AS condition_era_length
         FROM condition_era 
       WHERE condition_concept_id IN( 254761, 257011, 320128, 432867, 25297 )
     )
 GROUP BY condition_concept_id, condition_type_concept_id;
Output:
Output field list:
 Field  Description
condition_type_concept_id A foreign key to the predefined concept identifier in the vocabulary reflecting the parameters used to construct the condition era.
condition_concept_id A foreign key that refers to a standard condition concept identifier in the vocabulary.
condition_era_length  
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.
condition_era_end_date The end date for the condition era constructed from the individual instances of condition occurrences. It is the end date of the final continuously recorded instance of the condition.

Sample output record:
 Field  Description
condition_type_concept_id  
condition_concept_id  
condition_era_length  
condition_era_start_date  
condition_era_end_date  
Comments