inst/QueryLibrary/Aggregate/condition_occurrence/CO12.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

CO12: Distribution of condition end dates.

Description

This query is used to to provide summary statistics for condition occurrence end dates (condition_occurrence_end_date) across all condition occurrence 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 end_rank AS 
  (SELECT
    condition_end_date,
    cast(SUM(1) over (partition BY 1 ORDER BY condition_end_date ASC ROWS BETWEEN unbounded preceding AND CURRENT row) as integer) AS rownumASc
  FROM @cdm.condition_occurrence where condition_end_date is not null
  ),
    other_stat AS 
  (SELECT
    COUNT(condition_end_date)::integer                                                                         AS condition_end_date_count,
    MIN(condition_end_date)                                                                                     AS condition_end_date_min,
    MAX(condition_end_date)                                                                                     AS condition_end_date_max,
    CAST(AVG(CAST(condition_end_date - MAKE_DATE(1,1,1) AS NUMERIC)) AS INTEGER) + MAKE_DATE(1,1,1)          AS condition_end_date_average,
    ROUND(STDDEV(CAST(condition_end_date- MAKE_DATE(1,1,1) AS NUMERIC)),0)                              AS condition_end_date_stddev
   FROM @cdm.condition_occurrence
   WHERE condition_end_date IS NOT NULL
  )
SELECT
  (SELECT 
    COUNT(condition_end_date)::integer 
   FROM @cdm.condition_occurrence 
   WHERE condition_end_date IS NULL
  ) AS condition_end_date_null_count,
  condition_end_date_count,condition_end_date_min,condition_end_date_max,condition_end_date_average,condition_end_date_stddev,
  condition_end_date_25pctile,condition_end_date_median,condition_end_date_75pctile

FROM other_stat,
    (SELECT CAST(AVG(CAST(condition_end_date - MAKE_DATE(1,1,1) AS NUMERIC)) AS INTEGER) + MAKE_DATE(1,1,1)  AS condition_end_date_25pctile
      FROM (
       SELECT condition_end_date, rownumAsc,(SELECT COUNT(*) FROM end_rank ) AS rowno 
         FROM end_rank 
           ) a_1
       WHERE ( rownumASc = CAST(rowno*0.25 AS int)   AND floor(rowno*25/100)  = rowno*25/100 ) 
          OR ( rownumASc = CAST(rowno*0.25 AS int)   AND floor(rowno*25/100) != rowno*25/100 )
          OR ( rownumASc = CAST(rowno*0.25 AS int)+1 AND floor(rowno*25/100) != rowno*25/100 )
     ) condition_end_date_25pctile,
    (SELECT CAST(AVG(CAST(condition_end_date - MAKE_DATE(1,1,1) AS NUMERIC)) AS INTEGER) + MAKE_DATE(1,1,1) AS condition_end_date_median
      FROM (
       SELECT condition_end_date, rownumAsc,(SELECT COUNT(*) FROM end_rank ) AS rowno 
         FROM end_rank 
          ) a_1
       WHERE ( rownumASc = CAST(rowno*0.5 AS int)   AND floor(rowno*50/100)  = rowno*50/100 ) 
          OR ( rownumASc = CAST(rowno*0.5 AS int)   AND floor(rowno*50/100) != rowno*50/100 )
          OR ( rownumASc = CAST(rowno*0.5 AS int)+1 AND floor(rowno*50/100) != rowno*50/100 )
     ) condition_end_date_median,
    (SELECT CAST(AVG(CAST(condition_end_date - MAKE_DATE(1,1,1) AS NUMERIC)) AS INTEGER) + MAKE_DATE(1,1,1) AS condition_end_date_75pctile
      FROM (
       SELECT condition_end_date, rownumAsc,(SELECT COUNT(*) FROM end_rank ) AS rowno 
         FROM end_rank 
         ) a_1
       WHERE ( rownumASc = CAST(rowno*0.75 AS int)   AND floor(rowno*75/100)  = rowno*75/100 ) 
          OR ( rownumASc = CAST(rowno*0.75 AS int)   AND floor(rowno*75/100) != rowno*75/100 )
          OR ( rownumASc = CAST(rowno*0.75 AS int)+1 AND floor(rowno*75/100) != rowno*75/100 )
     ) condition_end_date_75pctile
;

Input

None

Output

| Field | Description | | --- | --- | | condition_end_date_null_count | Number of condition occurrences where end date is null | | condition_end_date_count | Number of condition occurrence end dates | | condition_end_date_min | The earliest end date of a condition occurrence | | condition_end_date_max | The latest end date of a condition occurrence | | condition_end_date_average | The average end date (spanning from the earliest to the latest date and counted by days) | | condition_end_date_stddev | The standard deviation of end dates, in number of days (spanning from the earliest to the latest date and counted by days) | | condition_end_date_25percentile | An end date where 25 percent of the other end dates are earlier | | condition_end_date_median | An end date where half of the other end dates are earlier and half are later | | condition_end_date_75percentile | An end date where 75 percent of the other end dates are earlier |

Example output record

| Field | Value | | --- | --- | | condition_end_date_null_count | 0 | | condition_end_date_count | 224523674 | | condition_end_date_min | 2003-01-01 | | condition_end_date_max | 011-12-15 | | condition_end_date_average | 2008-11-30 | | condition_end_date_stddev | 651.19 | | condition_end_date_25percentile | 2007-10-30 | | condition_end_date_median | 2009-05-07 | | condition_end_date_75percentile | 2010-05-04 |

Documentation

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

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