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 |
|
|
|