Skip to content

Instantly share code, notes, and snippets.

@niquola

niquola/cql-to-omop.md

Last active March 27, 2026 11:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save niquola/e749508c76eceda62533d0107dfde0a7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save niquola/e749508c76eceda62533d0107dfde0a7 to your computer and use it in GitHub Desktop.
Download ZIP
Translating CQL to OMOP CDM SQL: Chlamydia Screening Measure (CMS153)
Raw
cql-to-omop.md

Translating CQL to OMOP CDM SQL: Chlamydia Screening (CMS153)

This document demonstrates how Clinical Quality Language (CQL) artifacts — both quality measures (CQM) and clinical decision support (CDS) — can be expressed as SQL against the OMOP Common Data Model.

The example uses two versions of the same clinical logic: the Chlamydia Screening for Women measure (CMS153 / NQF0033).

Part 1: Quality Measure (CQM)

The CQM version answers: "What percentage of eligible women were screened?" It computes a score over a fixed measurement period.

Original CQL (CQM)

library ChlamydiaScreening_CQM version '2'

using QUICK

valueset "Female Administrative Sex": '2.16.840.1.113883.3.560.100.2'
valueset "Other Female Reproductive Conditions": '2.16.840.1.113883.3.464.1003.111.12.1006'
valueset "Genital Herpes": '2.16.840.1.113883.3.464.1003.110.12.1049'
valueset "Genococcal Infections and Venereal Diseases": '2.16.840.1.113883.3.464.1003.112.12.1001'
valueset "Inflammatory Diseases of Female Reproductive Organs": '2.16.840.1.113883.3.464.1003.112.12.1004'
valueset "Chlamydia": '2.16.840.1.113883.3.464.1003.112.12.1003'
valueset "HIV": '2.16.840.1.113883.3.464.1003.120.12.1003'
valueset "Syphilis": '2.16.840.1.113883.3.464.1003.112.12.1002'
valueset "Complications of Pregnancy, Childbirth and the Puerperium": '2.16.840.1.113883.3.464.1003.111.12.1012'
valueset "Pregnancy Test": '2.16.840.1.113883.3.464.1003.111.12.1011'
valueset "Pap Test": '2.16.840.1.113883.3.464.1003.108.12.1017'
valueset "Lab Tests During Pregnancy": '2.16.840.1.113883.3.464.1003.111.12.1007'
valueset "Lab Tests for Sexually Transmitted Infections": '2.16.840.1.113883.3.464.1003.110.12.1051'
valueset "Chlamydia Screening": '2.16.840.1.113883.3.464.1003.110.12.1052'

parameter MeasurementPeriod default Interval[DateTime(2013, 1, 1, 0, 0, 0, 0), DateTime(2014, 1, 1, 0, 0, 0, 0))

context Patient

define "InDemographic":
    AgeInYearsAt(start of MeasurementPeriod) >= 16
        and AgeInYearsAt(start of MeasurementPeriod) < 24
        and "Patient"."gender" in "Female Administrative Sex"

define "SexuallyActive":
    exists (["Condition": "Other Female Reproductive Conditions"] C
        where Interval[C."onsetDateTime", C."abatementDate"] overlaps MeasurementPeriod)
    or exists (["Condition": "Genital Herpes"] C
        where Interval[C."onsetDateTime", C."abatementDate"] overlaps MeasurementPeriod)
    or exists (["Condition": "Genococcal Infections and Venereal Diseases"] C
        where Interval[C."onsetDateTime", C."abatementDate"] overlaps MeasurementPeriod)
    or exists (["Condition": "Inflammatory Diseases of Female Reproductive Organs"] C
        where Interval[C."onsetDateTime", C."abatementDate"] overlaps MeasurementPeriod)
    or exists (["Condition": "Chlamydia"] C
        where Interval[C."onsetDateTime", C."abatementDate"] overlaps MeasurementPeriod)
    or exists (["Condition": "HIV"] C
        where Interval[C."onsetDateTime", C."abatementDate"] overlaps MeasurementPeriod)
    or exists (["Condition": "Syphilis"] C
        where Interval[C."onsetDateTime", C."abatementDate"] overlaps MeasurementPeriod)
    or exists (["Condition": "Complications of Pregnancy, Childbirth and the Puerperium"] C
        where Interval[C."onsetDateTime", C."abatementDate"] overlaps MeasurementPeriod)
    or exists (["DiagnosticOrder": "Pregnancy Test"] O
        where Last(O."event" E where E."status" = 'completed'
            sort by E."dateTime")."dateTime" during MeasurementPeriod)
    or exists (["DiagnosticOrder": "Pap Test"] O
        where Last(O."event" E where E."status" = 'completed'
            sort by E."dateTime")."dateTime" during MeasurementPeriod)
    or exists (["DiagnosticOrder": "Lab Tests During Pregnancy"] O
        where Last(O."event" E where E."status" = 'completed')."dateTime"
            during MeasurementPeriod)
    or exists (["DiagnosticOrder": "Lab Tests for Sexually Transmitted Infections"] O
        where Last(O."event" E where E."status" = 'completed')."dateTime"
            during MeasurementPeriod)

define "InInitialPopulation":
    "InDemographic" and "SexuallyActive"

define "InDenominator":
    true

define "InNumerator":
    exists (["DiagnosticReport": "Chlamydia Screening"] R
        where R."issued" during MeasurementPeriod and R."result" is not null)

context Population

define "MeasureScore":
    (Count(Patient P where "InInitialPopulation" and "InDenominator" and "InNumerator")
     / Count("Patient" P where "InInitialPopulation" and "InDenominator")) * 100

How CQL maps to OMOP CDM

Data model mapping

CQL operates on the QUICK/FHIR data model. OMOP CDM uses a different but equivalent relational structure. Here is how the key resources translate:

CQL / FHIR Resource OMOP CDM Table Notes
Patient person Demographics, birth year, gender
Patient.gender person.gender_concept_id 8532 = Female, 8507 = Male
Condition condition_occurrence onsetDateTime → condition_start_date, abatementDate → condition_end_date
DiagnosticOrder procedure_occurrence or measurement Ordered tests map to procedures or measurements depending on domain
DiagnosticReport measurement Lab results with value_as_number / value_as_concept_id
AgeInYearsAt(date) EXTRACT(YEAR FROM date) - year_of_birth Computed from birth year

Value set mapping

CQL references value sets by OID (e.g. 2.16.840.1.113883.3.464.1003.112.12.1003). These are maintained in the VSAC and contain explicit lists of codes from terminologies like ICD-10-CM, SNOMED CT, CPT, and LOINC.

OMOP uses concept sets instead. There are two approaches to translate:

Approach 1: Hierarchy-based (recommended)

Pick the root standard concept in SNOMED and include all descendants via concept_ancestor. This is the idiomatic OMOP approach — it automatically picks up new child concepts when vocabularies are updated.

-- CQL:  valueset "Chlamydia": '2.16.840.1.113883.3.464.1003.112.12.1003'
-- OMOP: ancestor_concept_id = 438066 (Chlamydial infection) + descendants
SELECT descendant_concept_id AS concept_id
FROM concept_ancestor
WHERE ancestor_concept_id = 438066;
-- Returns 60+ concepts including all subtypes

Approach 2: Explicit mapping from VSAC

Download the expanded value set from VSAC, map each code to an OMOP concept_id via the concept table, and store the list. This gives exact 1:1 parity with the original CQL but requires manual maintenance when value sets are updated.

-- Map ICD-10-CM codes from a VSAC value set to OMOP concept_ids
SELECT c.concept_id
FROM concept c
WHERE c.vocabulary_id = 'ICD10CM'
  AND c.concept_code IN ('A56.0', 'A56.1', 'A56.2', ...)  -- codes from VSAC

Concept set definitions for this measure

CQL Value Set Root OMOP Concept concept_id Descendants
Female Administrative Sex FEMALE 8532 No (single concept)
Chlamydia Chlamydial infection 438066 Yes
Genital Herpes Genital herpes simplex 74855 Yes
Genococcal Infections Gonorrhea 433417 Yes
HIV Human immunodeficiency virus infection 439727 Yes
Syphilis Syphilis 436033 Yes
Inflammatory Diseases of Female Reproductive Organs Inflammatory disease of female pelvic organs 196523 Yes
Complications of Pregnancy Complication of pregnancy 4128331 Yes
Other Female Reproductive Conditions Disorder of female reproductive system 4214800 Yes
Pregnancy Test Pregnancy test 4014769 Yes
Pap Test Papanicolaou smear 2720510 Yes
Chlamydia Screening Chlamydia test 4055008 Yes

OMOP CDM SQL Translation

-- ============================================================================
-- CMS153: Chlamydia Screening for Women
-- Translated from CQL to OMOP CDM v5 SQL
-- ============================================================================

-- Step 1: Define concept sets
-- Each CQL valueset becomes a set of concept_ids derived from the OMOP
-- vocabulary hierarchy. Using concept_ancestor lets us include all descendant
-- concepts automatically — no need to maintain explicit code lists.

CREATE TEMPORARY TABLE concept_set AS

  -- Conditions indicating sexual activity
  -- Maps CQL value sets: Chlamydia, Genital Herpes, Gonorrhea, HIV, Syphilis,
  -- Inflammatory Diseases, Pregnancy Complications, Other Female Reproductive Conditions
  SELECT DISTINCT ca.descendant_concept_id AS concept_id,
         'sexually_active_dx' AS set_name
  FROM concept_ancestor ca
  WHERE ca.ancestor_concept_id IN (
    438066,   -- Chlamydial infection
    74855,    -- Genital herpes simplex
    433417,   -- Gonorrhea
    439727,   -- Human immunodeficiency virus infection
    436033,   -- Syphilis
    196523,   -- Inflammatory disease of female pelvic organs
    4128331,  -- Complication of pregnancy
    4214800   -- Disorder of female reproductive system
  )

  UNION ALL

  -- Tests/orders indicating sexual activity
  -- Maps CQL value sets: Pregnancy Test, Pap Test,
  -- Lab Tests During Pregnancy, Lab Tests for STIs
  SELECT DISTINCT ca.descendant_concept_id, 'sexually_active_test'
  FROM concept_ancestor ca
  WHERE ca.ancestor_concept_id IN (
    4014769,  -- Pregnancy test
    2720510,  -- Papanicolaou smear
    4299393,  -- Laboratory test during pregnancy
    4228194   -- Sexually transmitted infection test
  )

  UNION ALL

  -- Chlamydia screening result (numerator)
  -- Maps CQL value set: Chlamydia Screening
  SELECT DISTINCT ca.descendant_concept_id, 'chlamydia_screen'
  FROM concept_ancestor ca
  WHERE ca.ancestor_concept_id IN (
    4055008,  -- Chlamydia test
    44792246  -- Chlamydia screening test
  );


-- Step 2: Set measurement period (CQL parameter)

CREATE TEMPORARY TABLE measurement_period AS
SELECT DATE '2013-01-01' AS mp_start,
       DATE '2014-01-01' AS mp_end;


-- Step 3: InDemographic
-- CQL: AgeInYearsAt(start of MeasurementPeriod) >= 16
--      AND AgeInYearsAt(start of MeasurementPeriod) < 24
--      AND Patient.gender in "Female Administrative Sex"

CREATE TEMPORARY TABLE in_demographic AS
SELECT p.person_id
FROM person p
CROSS JOIN measurement_period mp
WHERE p.gender_concept_id = 8532                                -- Female
  AND EXTRACT(YEAR FROM mp.mp_start) - p.year_of_birth >= 16    -- Age >= 16
  AND EXTRACT(YEAR FROM mp.mp_start) - p.year_of_birth < 24;   -- Age < 24


-- Step 4: SexuallyActive
-- CQL: exists([Condition: <valueset>] where interval overlaps MeasurementPeriod)
--   OR exists([DiagnosticOrder: <valueset>] where dateTime during MeasurementPeriod)
--
-- In OMOP, conditions live in condition_occurrence.
-- Diagnostic orders/reports map to procedure_occurrence and measurement.

CREATE TEMPORARY TABLE sexually_active AS

  -- Conditions overlapping the measurement period
  SELECT DISTINCT co.person_id
  FROM condition_occurrence co
  JOIN concept_set cs
    ON cs.concept_id = co.condition_concept_id
   AND cs.set_name = 'sexually_active_dx'
  CROSS JOIN measurement_period mp
  WHERE co.condition_start_date < mp.mp_end
    AND COALESCE(co.condition_end_date, co.condition_start_date) >= mp.mp_start

  UNION

  -- Procedures during the measurement period
  SELECT DISTINCT po.person_id
  FROM procedure_occurrence po
  JOIN concept_set cs
    ON cs.concept_id = po.procedure_concept_id
   AND cs.set_name = 'sexually_active_test'
  CROSS JOIN measurement_period mp
  WHERE po.procedure_date >= mp.mp_start
    AND po.procedure_date < mp.mp_end

  UNION

  -- Measurements (lab orders) during the measurement period
  SELECT DISTINCT m.person_id
  FROM measurement m
  JOIN concept_set cs
    ON cs.concept_id = m.measurement_concept_id
   AND cs.set_name = 'sexually_active_test'
  CROSS JOIN measurement_period mp
  WHERE m.measurement_date >= mp.mp_start
    AND m.measurement_date < mp.mp_end;


-- Step 5: Initial Population = InDemographic AND SexuallyActive
-- CQL: define "InInitialPopulation": "InDemographic" and "SexuallyActive"

CREATE TEMPORARY TABLE initial_population AS
SELECT d.person_id
FROM in_demographic d
JOIN sexually_active sa ON sa.person_id = d.person_id;


-- Step 6: Denominator = Initial Population
-- CQL: define "InDenominator": true
-- (all patients in the initial population qualify)


-- Step 7: Numerator
-- CQL: exists([DiagnosticReport: "Chlamydia Screening"] R
--        where R.issued during MeasurementPeriod and R.result is not null)
--
-- In OMOP, diagnostic reports with results map to the measurement table.
-- "result is not null" translates to having a value.

CREATE TEMPORARY TABLE numerator AS
SELECT DISTINCT m.person_id
FROM measurement m
JOIN concept_set cs
  ON cs.concept_id = m.measurement_concept_id
 AND cs.set_name = 'chlamydia_screen'
CROSS JOIN measurement_period mp
WHERE m.measurement_date >= mp.mp_start
  AND m.measurement_date < mp.mp_end
  AND (m.value_as_number IS NOT NULL          -- numeric result
       OR m.value_as_concept_id IS NOT NULL   -- coded result (pos/neg)
      );


-- Step 8: Measure Score
-- CQL: (Count(numerator ∩ denominator) / Count(denominator)) * 100

SELECT
  COUNT(DISTINCT ip.person_id) AS denominator_count,
  COUNT(DISTINCT n.person_id)  AS numerator_count,
  ROUND(
    100.0 * COUNT(DISTINCT n.person_id)
          / NULLIF(COUNT(DISTINCT ip.person_id), 0),
    2
  ) AS measure_score_pct
FROM initial_population ip
LEFT JOIN numerator n ON n.person_id = ip.person_id;

Key differences between CQL and OMOP SQL

Aspect CQL OMOP SQL
Data model FHIR/QUICK resources Relational tables (person, condition_occurrence, measurement, etc.)
Value sets Fixed code lists maintained in VSAC, referenced by OID Concept sets built from vocabulary hierarchy via concept_ancestor
Terminology Works with source codes (ICD, SNOMED, CPT directly) Works with standard concept_id — all source codes are pre-mapped
Interval logic Built-in overlaps, during operators SQL date comparisons (start < end AND end >= start)
Patient context Implicit — each define runs per patient Explicit JOINs on person_id
Composability Named definitions referencing each other CTEs or temp tables referencing each other
Vocabulary updates Must re-download value sets from VSAC Hierarchy-based sets auto-expand with vocabulary updates

Running this on OMOP CDM

This SQL runs against any OMOP CDM v5.x database with:

  • Standard clinical data tables (person, condition_occurrence, measurement, procedure_occurrence)
  • OMOP standardized vocabulary tables (concept, concept_ancestor)

The vocabulary tables provide the terminology mapping layer that eliminates the need for external value set services like VSAC — all code mappings and hierarchies are already embedded in the database.

Part 2: Clinical Decision Support (CDS)

The CDS version of the same logic answers a different question: "Which patients need a chlamydia screening right now?"

Instead of measuring past performance, it identifies patients who are due for screening and proposes an order. This is the kind of logic that fires as a CDS Hook in an EHR.

Original CQL (CDS)

library ChlamydiaScreening_CDS version '2'

using QUICK

codesystem "SNOMED": 'http://snomed.info/sct'

valueset "Female Administrative Sex": '2.16.840.1.113883.3.560.100.2'
valueset "Other Female Reproductive Conditions": '2.16.840.1.113883.3.464.1003.111.12.1006'
valueset "Genital Herpes": '2.16.840.1.113883.3.464.1003.110.12.1049'
valueset "Genococcal Infections and Venereal Diseases": '2.16.840.1.113883.3.464.1003.112.12.1001'
valueset "Inflammatory Diseases of Female Reproductive Organs": '2.16.840.1.113883.3.464.1003.112.12.1004'
valueset "Chlamydia": '2.16.840.1.113883.3.464.1003.112.12.1003'
valueset "HIV": '2.16.840.1.113883.3.464.1003.120.12.1003'
valueset "Syphilis": '2.16.840.1.113883.3.464.1003.112.12.1002'
valueset "Complications of Pregnancy, Childbirth and the Puerperium": '2.16.840.1.113883.3.464.1003.111.12.1012'
valueset "Pregnancy Test": '2.16.840.1.113883.3.464.1003.111.12.1011'
valueset "Pap Test": '2.16.840.1.113883.3.464.1003.108.12.1017'
valueset "Lab Tests During Pregnancy": '2.16.840.1.113883.3.464.1003.111.12.1007'
valueset "Lab Tests for Sexually Transmitted Infections": '2.16.840.1.113883.3.464.1003.110.12.1051'
valueset "Chlamydia Screening": '2.16.840.1.113883.3.464.1003.110.12.1052'
valueset "Reason for not performing Chlamydia Screening": 'TBD'

context Patient

define "InDemographic":
    AgeInYears() >= 16 and AgeInYears() < 24
        and "Patient"."gender" in "Female Administrative Sex"

define "SexuallyActive":
    exists (["Condition": "Other Female Reproductive Conditions"])
    or exists (["Condition": "Genital Herpes"])
    or exists (["Condition": "Genococcal Infections and Venereal Diseases"])
    or exists (["Condition": "Inflammatory Diseases of Female Reproductive Organs"])
    or exists (["Condition": "Chlamydia"])
    or exists (["Condition": "HIV"])
    or exists (["Condition": "Syphilis"])
    or exists (["Condition": "Complications of Pregnancy, Childbirth and the Puerperium"])
    or exists (["DiagnosticOrder": "Pregnancy Test"])
    or exists (["DiagnosticOrder": "Pap Test"])
    or exists (["DiagnosticOrder": "Lab Tests During Pregnancy"])
    or exists (["DiagnosticOrder": "Lab Tests for Sexually Transmitted Infections"])

define "NoScreening":
    not exists (["DiagnosticReport": "Chlamydia Screening"] R
        where R."issued" during Interval[Today() - 1 years, Today()]
        and R."result" is not null)
    and not exists (["ProcedureRequest": "Chlamydia Screening"] P
        where P."orderedOn" same day or after Today())
    and not exists (["Observation": "Reason for not performing Chlamydia Screening"])

define "NeedScreening": "InDemographic" and "SexuallyActive" and "NoScreening"

define "ChlamydiaScreeningRequest": Tuple {
    type: Code '442487003' from "SNOMED"
        display 'Screening for Chlamydia trachomatis (procedure)',
    status: 'proposed'
}

CQM vs CDS: Key Differences

The two CQL libraries share the same value sets and demographic logic but differ in purpose and temporal semantics:

Aspect CQM (Measure) CDS (Decision Support)
Question What % were screened? Who needs screening now?
Time frame Fixed MeasurementPeriod parameter Dynamic: Today() - 1 year to Today()
Conditions Must overlap the measurement period exists with no date filter (any time in record)
Output MeasureScore (percentage) NeedScreening (boolean) + ChlamydiaScreeningRequest (proposed order)
Exclusions None Already ordered today, or reason documented
Use case Retrospective reporting Real-time alert in EHR

OMOP CDM SQL Translation (CDS)

The CDS version uses the same concept sets as the CQM but changes how time constraints are applied. It also adds exclusion logic for patients who already have a recent screening, a pending order, or a documented reason.

-- ============================================================================
-- Chlamydia Screening CDS: Who needs screening right now?
-- Translated from CQL to OMOP CDM v5 SQL
-- ============================================================================

-- Reuse the same concept sets from the CQM translation above.
-- The only addition is a set for "reason not performed".

-- Step 1: Define concept sets (same as CQM, plus exclusion reasons)

CREATE TEMPORARY TABLE concept_set_cds AS

  -- Conditions indicating sexual activity (any time in record)
  SELECT DISTINCT ca.descendant_concept_id AS concept_id,
         'sexually_active_dx' AS set_name
  FROM concept_ancestor ca
  WHERE ca.ancestor_concept_id IN (
    438066,   -- Chlamydial infection
    74855,    -- Genital herpes simplex
    433417,   -- Gonorrhea
    439727,   -- Human immunodeficiency virus infection
    436033,   -- Syphilis
    196523,   -- Inflammatory disease of female pelvic organs
    4128331,  -- Complication of pregnancy
    4214800   -- Disorder of female reproductive system
  )

  UNION ALL

  -- Tests/orders indicating sexual activity
  SELECT DISTINCT ca.descendant_concept_id, 'sexually_active_test'
  FROM concept_ancestor ca
  WHERE ca.ancestor_concept_id IN (
    4014769,  -- Pregnancy test
    2720510,  -- Papanicolaou smear
    4299393,  -- Laboratory test during pregnancy
    4228194   -- Sexually transmitted infection test
  )

  UNION ALL

  -- Chlamydia screening (for checking recent results and pending orders)
  SELECT DISTINCT ca.descendant_concept_id, 'chlamydia_screen'
  FROM concept_ancestor ca
  WHERE ca.ancestor_concept_id IN (
    4055008,  -- Chlamydia test
    44792246  -- Chlamydia screening test
  )

  UNION ALL

  -- Reason for not performing screening
  -- CQL: valueset "Reason for not performing Chlamydia Screening": 'TBD'
  -- In OMOP, this maps to observation concepts for declined/not indicated
  SELECT concept_id, 'screening_not_performed'
  FROM (VALUES
    (4260745),  -- Chlamydia screening declined
    (37208445), -- Chlamydia screening not indicated
    (37310771)  -- Chlamydia screening test not indicated
  ) AS v(concept_id);


-- Step 2: InDemographic
-- CQL: AgeInYears() >= 16 and AgeInYears() < 24
--      and Patient.gender in "Female Administrative Sex"
--
-- Unlike the CQM, age is computed as of today (not a fixed period).

CREATE TEMPORARY TABLE in_demographic_cds AS
SELECT p.person_id
FROM person p
WHERE p.gender_concept_id = 8532                                    -- Female
  AND EXTRACT(YEAR FROM CURRENT_DATE) - p.year_of_birth >= 16       -- Age >= 16
  AND EXTRACT(YEAR FROM CURRENT_DATE) - p.year_of_birth < 24;      -- Age < 24


-- Step 3: SexuallyActive
-- CQL: exists([Condition: <valueset>])  — no date filter!
--
-- This is a key difference from the CQM: the CDS version checks if
-- the patient has EVER had a relevant condition or test, not just
-- during a specific measurement period. This makes sense for CDS —
-- once a patient is known to be sexually active, the recommendation applies.

CREATE TEMPORARY TABLE sexually_active_cds AS

  -- Any relevant condition on record (no date constraint)
  SELECT DISTINCT co.person_id
  FROM condition_occurrence co
  JOIN concept_set_cds cs
    ON cs.concept_id = co.condition_concept_id
   AND cs.set_name = 'sexually_active_dx'

  UNION

  -- Any relevant procedure on record
  SELECT DISTINCT po.person_id
  FROM procedure_occurrence po
  JOIN concept_set_cds cs
    ON cs.concept_id = po.procedure_concept_id
   AND cs.set_name = 'sexually_active_test'

  UNION

  -- Any relevant measurement on record
  SELECT DISTINCT m.person_id
  FROM measurement m
  JOIN concept_set_cds cs
    ON cs.concept_id = m.measurement_concept_id
   AND cs.set_name = 'sexually_active_test';


-- Step 4: NoScreening — three exclusion checks
-- CQL: not exists(recent screening with result)
--      and not exists(pending order for today or later)
--      and not exists(documented reason for not screening)

-- 4a: Has a chlamydia screening result in the past year?
CREATE TEMPORARY TABLE recently_screened AS
SELECT DISTINCT m.person_id
FROM measurement m
JOIN concept_set_cds cs
  ON cs.concept_id = m.measurement_concept_id
 AND cs.set_name = 'chlamydia_screen'
WHERE m.measurement_date >= CURRENT_DATE - INTERVAL '1 year'
  AND m.measurement_date <= CURRENT_DATE
  AND (m.value_as_number IS NOT NULL OR m.value_as_concept_id IS NOT NULL);

-- 4b: Has a pending chlamydia screening order (today or later)?
-- In OMOP, pending orders appear in procedure_occurrence.
-- Some implementations use a status field; here we check for future dates.
CREATE TEMPORARY TABLE pending_order AS
SELECT DISTINCT po.person_id
FROM procedure_occurrence po
JOIN concept_set_cds cs
  ON cs.concept_id = po.procedure_concept_id
 AND cs.set_name = 'chlamydia_screen'
WHERE po.procedure_date >= CURRENT_DATE;

-- 4c: Has a documented reason for not screening?
-- In OMOP, this is captured in the observation table.
CREATE TEMPORARY TABLE screening_declined AS
SELECT DISTINCT o.person_id
FROM observation o
JOIN concept_set_cds cs
  ON cs.concept_id = o.observation_concept_id
 AND cs.set_name = 'screening_not_performed';


-- Step 5: NeedScreening = InDemographic AND SexuallyActive AND NoScreening
-- CQL: define "NeedScreening":
--          "InDemographic" and "SexuallyActive" and "NoScreening"
--
-- This is the core CDS output: a list of patients who should be screened.

SELECT d.person_id
FROM in_demographic_cds d
JOIN sexually_active_cds sa ON sa.person_id = d.person_id
WHERE d.person_id NOT IN (SELECT person_id FROM recently_screened)
  AND d.person_id NOT IN (SELECT person_id FROM pending_order)
  AND d.person_id NOT IN (SELECT person_id FROM screening_declined);

The Action Part: ChlamydiaScreeningRequest

The CDS CQL defines an output action — a proposed procedure order:

define "ChlamydiaScreeningRequest": Tuple {
    type: Code '442487003' from "SNOMED"
        display 'Screening for Chlamydia trachomatis (procedure)',
    status: 'proposed'
}

This is where OMOP CDM reaches its boundary. OMOP is an analytical model — it records what happened, not what should happen. It does not have a native concept of "proposed" or "draft" orders.

However, the analytical part (identifying who needs screening) is fully expressible. The action can then be handled by:

  1. ATLAS Cohort — save the query result as a cohort definition in ATLAS for review
  2. CDS Hooks — feed the patient list into a CDS Hooks service that creates FHIR ServiceRequest resources in the EHR
  3. ETL back to EHR — export the list to the source system for clinician action

The SNOMED code 442487003 (Screening for Chlamydia trachomatis) maps to OMOP concept_id = 4055008, which is already in our concept set — closing the loop between the CDS recommendation and the measurement that satisfies the CQM numerator.

Summary

What OMOP covers What OMOP does not cover
Patient demographics and eligibility Proposed/draft orders
Condition history (any time or time-bounded) Real-time EHR workflow triggers
Lab results and procedure history CDS Hook integration
Vocabulary hierarchy for concept sets FHIR ServiceRequest creation
Exclusion logic (declined, not indicated) User-facing alerts and cards
Cohort identification ("who needs screening") Order entry in the EHR

The analytical core of both CQM and CDS is fully expressible in OMOP SQL. The difference is what happens with the result: CQM produces a score, CDS produces a patient list that feeds into an action workflow outside OMOP.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment