inst/QueryLibrary/Aggregate/observation_period/OP11.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

OP11: Distribution of observation period end dates

Description

This query is used to to provide summary statistics for observation period end dates (observation_period_end_date) across all observation period 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.

Query

WITH op AS
  (SELECT
      cast(observation_period_end_date - '1900-01-01'::date as integer) diffs,
      CAST(TO_CHAR(observation_period_end_date,'YYYYMMDD') AS INTEGER) AS end_date,
      COUNT(*) OVER() AS population_size
   FROM @cdm.observation_period
  )
SELECT
MIN(end_date) AS min_end_date,
max(end_date) AS max_end_date,
 '1900-01-01'::date + make_interval(days => ROUND(AVG(diffs),0)::integer) AS avg_end_date,
  round( STDDEV( end_date ), 1 )                                                                                      AS STDEV_days ,
CAST(MIN(CASE WHEN order_nr < .25 * population_size THEN 9999999999 ELSE end_date END) AS VARCHAR)  AS percentile_25,
 CAST(MIN(CASE WHEN order_nr < .50 * population_size THEN 9999999999 ELSE end_date END) AS VARCHAR)  AS median_value,
CAST(MIN(CASE WHEN order_nr < .75 * population_size THEN 9999999999 ELSE end_date END) AS VARCHAR)  AS percentile_75

FROM
 ( SELECT
     diffs,
     end_date                                                     AS end_date,
     ROW_NUMBER() OVER (ORDER BY end_date)                        AS order_nr,
     population_size
   FROM op
 ) ordered_data;

Input

None

Output

| Field | Description | | --- | --- | | min_end_date | Minimum value of observation period end date | | max_end_date | Maximum value of observation period end date | | avg_end_date | Average value of observation period end date | | STDEV_end_date | Standard deviation of observation period end date | | percentile_25 | 25th percentile of observation period end date | | median | Median value of observation period end date | | percentile_75 | 75th percentile of observation period end date |

Example output record

| Field | Description | | --- | --- | | min_end_date | 1/31/2003 | | max_end_date | 6/30/2011 | | avg_end_date | 11/21/2009 | | STDEV_end_date | 614 | | percentile_25 | 12/31/2008 | | median | 12/31/2009 | | percentile_75 | 12/31/2010 |

Documentation

https://github.com/OHDSI/CommonDataModel/wiki/

sib-swiss/dsQueryLibraryServer documentation built on Feb. 13, 2025, 8:07 p.m.