Drug Era‎ > ‎

DER11: Distribution of drug era start dates

This query is used to to provide summary statistics for drug era start dates (drug_era_start_date) across all drug era records: the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. No input is required for this query.

Input:
<None>

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

-- Oracle
SELECT min(tt.start_date) AS min_date
     , max(tt.start_date) AS max_date
     , avg(tt.start_date_num) + tt.min_date   AS avg_date
     , (round(stdDev(tt.start_date_num)) ) AS stdDev_days
     , tt.min_date + (PERCENTILE_DISC(0.25) WITHIN GROUP( ORDER BY tt.start_date_num ) )
                                     AS percentile_25_date
     , tt.min_date + (PERCENTILE_DISC(0.5)  WITHIN GROUP (ORDER BY tt.start_date_num ) )
                                     AS median_date
     , tt.min_date + (PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY tt.start_date_num ) )
                                     AS percential_75_date
  FROM
    ( SELECT (t.drug_era_start_date - MIN(t.drug_era_start_date) OVER()) AS start_date_num,
             t.drug_era_start_date AS start_date,
             MIN(t.drug_era_start_date) OVER() min_date
      FROM mdcr_cdm4.drug_era t 
    ) tt GROUP BY tt.min_date 
;
 Output:
Output field list:
 Field  Description
drug_era_id A system-generated unique identifier for each drug era.
drug_era_start_date The start date for the drug era constructed from the individual instances of drug exposures. It is the start date of the very first chronologically recorded instance of utilization of a drug.
percentile_25_date
median_date
percentile_75_date

Sample output record:
 Field  Description
drug_era_id
drug_era_end_date
percentile_25_date
median_date
percentile_75_date
Comments