inst/QueryLibrary/Aggregate/observation_period/OP13.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

OP13: Distribution of observation period start dates

Description

This query is used to to provide summary statistics for observation period start dates (observation_period_start_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_start_date - '1900-01-01'::date as integer) diffs,
      CAST(TO_CHAR(observation_period_start_date,'YYYYMMDD') AS INTEGER) AS start_date,
      ROW_NUMBER() over (order by observation_period_start_date) AS order_nr,
      observation_period_start_date AS org_start_date,
      COUNT(*) OVER() AS population_size
   FROM @cdm.observation_period
  )

SELECT
  MIN(org_start_date) AS min_start_date,
  MAX(org_start_date) AS max_start_date,
  '1900-01-01'::date + make_interval(days => ROUND(AVG(diffs),0)::integer) AS avg_start_date,
  ROUND(STDDEV(start_date), 1)                                             AS STDEV_days,
  MIN(CASE WHEN order_nr < .25 * population_size THEN '9999-12-31'::date ELSE org_start_date END) AS percentile_25,
  MIN(CASE WHEN order_nr < .50 * population_size THEN '9999-12-31'::date ELSE org_start_date END) AS median,
  MIN(CASE WHEN order_nr < .75 * population_size THEN '9999-12-31'::date ELSE org_start_date END) AS percentile_75
FROM op
;

Input

None

Output

| Field | Description | | --- | --- | | min_start_date | Minimum start date value | | max_start_date | Maximum start date value | | avg_start_date | Average start date value | | STDEV_days | Standard Deviation of start date | | percentile_25 | 25th percentile of start date | | median | Median of start date | | percentile_75 | 75th percentile of start date |

Example output record

| Field | Value | | --- | --- | | min_start_date | 1/1/2003 | | max_start_date | 6/30/2011 | | avg_start_date | 2/5/2008 | | STDEV_days | 741 | | percentile_25 | 1/1/2006 | | median | 1/1/2009 | | percentile_75 | 1/1/2010 |

Documentation

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

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