New check request: overlapping observation periods

[MaximMoinat]

From the OMOP Observation Period conventions:

For each Person, one or more OBSERVATION_PERIOD records may be present, but they will not overlap or be back to back to each other.

The former is checked, the latter two conditions are not. Below query would give you the violating records for overlapping observation periods. By adding one day to the end dates, this also captures observation periods ending and starting within a day (i.e. back to back, without a gap).

SELECT *
FROM observation_period AS a
JOIN observation_period AS b ON
      a.person_id = b.person_id 
  AND a.observation_period_start_date <= DATEADD(d, 1, b.observation_period_end_date)
  AND DATEADD(d, 1, a.observation_period_end_date) >= b.observation_period_start_date
  AND a.observation_period_id <> b.observation_period_id
;

[katy-sadowski]

Love this idea, this is a very important one to check.