inst/QueryLibrary/Aggregate/observation_period/OP10.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

OP10: Observation period records stratified by observation month

Description

Counts the observation period records stratified by observation month. All possible values are summarized.

Query

WITH table_months AS (
SELECT row_number() over (order by person_id) as month from @cdm.observation_period fetch first 12 rows only
)
SELECT
  month::integer,
  SUM( observations )::integer                                                     AS num_observations
FROM
  ( SELECT
      month                                                               AS month,
      min_count +
        CASE
          WHEN MONTH >= start_month AND MONTH <= start_month_remainder_check
            THEN 1
          WHEN MONTH >= 1 AND MONTH <= start_month + remainder - 12
            THEN 1
          ELSE 0
        END                                                                AS observations

  FROM table_months
  CROSS JOIN
    ( SELECT
        person_id,
        start_date,
        end_date,
        min_count,
        start_month,
        remainder,
        CASE
          WHEN SIGN(start_month + remainder - 12) = -1 THEN start_month + remainder
          ELSE 12
        END                                                                 AS start_month_remainder_check

      FROM
        ( SELECT
            person_id,
            observation_period_start_date                                                                AS start_date,
            observation_period_end_date                                                                  AS end_date,
            round(observation_period_end_date - observation_period_start_date, 0 )::integer            AS months /* number of complete years */ ,
            floor(round((observation_period_end_date - observation_period_start_date)/30,0)/12)::integer AS min_count ,
            date_part('month',observation_period_start_date )::integer start_month ,
            cast(round((observation_period_end_date - observation_period_start_date)/30,0) AS integer) - 12*floor(cast(round((observation_period_end_date - observation_period_start_date)/30,0) AS integer)/12) AS remainder
          FROM @cdm.observation_period
        ) t_0
    ) t_1
  ) t_2
GROUP BY month
ORDER BY month;

Input

None

Output

| Field | Description | | --- | --- | | month | Month number 1-12 | | num_observations | Number of observation in a specific month |

Example output record

| Field | Value | | --- | --- | | month | 1 | | num_observations | 12266979 |

Documentation

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

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