Drug Era‎ > ‎

DER23: Distribution of drug era start dates, stratified by drug

This query is used to summary statistics of the drug era start dates (drug_era_start_date) across all drug era records, stratified by drug (drug_concept_id): the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. The input to the query is a value (or a comma-separated list of values) of a drug_concept_id. If the input is omitted, all possible values are summarized.

Input:
 Parameter  Example  Mandatory  Notes
drug_concept_id 1300978, 1304643, 1549080 Yes

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

-- Oracle
SELECT 
          tt.drug_concept_id 
          , 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 percentile_75_date
FROM
          ( SELECT (t.drug_era_start_date - MIN(t.drug_era_start_date) OVER(partition by t.drug_concept_id)) AS start_date_num,
          t.drug_era_start_date AS start_date,
          MIN(t.drug_era_start_date) OVER(partition by t.drug_concept_id) min_date,
          t.drug_concept_id 
FROM drug_era t 
          where t.drug_concept_id in (1300978, 1304643, 1549080)
          ) tt 
GROUP BY tt.min_date , tt.drug_concept_id 
order by tt.drug_concept_id 
;
 Output:
Output field list:
 Field  Description
drug_concept_id A foreign key that refers to a standard concept identifier in the vocabulary for the drug concept.
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.
min_date
avg_value 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     
median_value     
percentile_75     

Sample output record:
 Field  Description
drug_concept_id
drug_era_start_date
min_date
avg_value
percentile_25     
median_value     
percentile_75     
Comments