Episode of Care Definitions API

This beta API delivers Episode of Care Definitions as a MySQL compatible database that supports read-only SQL queries. Users can employ any MySQL compatible SQL editor or programming language library.

Episode of Care definitions (also called metadata) are procedure and diagnosis codes grouped together to outline the entire range of treatment for more than 90 conditions.
Our Episode of Care definitions are an essential ingredient to success for anyone developing apps designed to:
  • convey price and quality of care information to consumers
  • to facilitate bundled payments or other forms of alternative payment models
  • to help clinicians in managing patients along the continuum of care, or
  • any of hundreds of other potential use cases
Please keep in mind that per the user agreement you accepted, you may use or incorporate all or part of the metadata in your own work for commercial or noncommercial purposes, but you may not redistribute the connection string or the metadata collection itself, and you may not state or imply that HCI³ has reviewed or is responsible for your analysis or outputs connected to your use of the metadata. This message is not a substitute for the user agreement, so please review that document.

If you are interested in licensing the complete PROMETHEUS Analytics, receiving complete metadata and analytics logic, please contact us at PROMETHEUS_Contact@altarum.org.

Access the API

API Documentation

The definitions are stored in a publicly available MySQL database. You will need a MySQL compatible client, editor, or programming language library to access the metadata via this method. You will also need some basic understanding of SQL, at least enough to run and apply simple modifications to the template SQL code provided here.

While the database is publicly available, your agreement to access this data restricts you from distributing the data or the connection information to others. If you know others who would like to access the definitions, please direct them here or the registration and user agreement.

Please Note: The Episode of Care Definition API is not for runtime use. Please copy data you need for your projects from the API once and not each time you run.

The episode definition metadata includes the following core components:

  • Episode List and Parameters
  • Trigger Criteria
  • Trigger Codes
  • Typical Diagnosis Codes
  • Typical Procedure Codes
  • Potentially Avoidable Complication Codes
  • Prescription Codes
  • Subtypes
  • Global E&Ms
  • Universal Risk Factors

See an interactive data model of the Episode of Care Definitions schema.

Episode List and Parameters

For a list of all episodes available in this ECR Metadata release, run:

SELECT * FROM episode;

This list will also include episode parameters such as the look back (BOUND _OFFSET) and look forward (BOUND_LENGTH).

The EPISODE_ID field in the results is the key you will use to retrieve additional information about any individual episode in most of the queries (except Universal Risk Factors and Global E&Ms which potentially apply to any episode). In the following examples, we will use EPISODE_ID=’EC1001’ to retrieve information about the “DIAB” or “Diabetes” episode.

Trigger Criteria

The trigger criteria identify the: type of claims, number of claims, codes to find, and position in which to find them required to identify a signal for an episode of care. There are different combinations of trigger criteria for each episode. Episodes might trigger off of inpatient (IP), outpatient (OP), or professional (PB) claims. They can also trigger due to another episode having been triggered. Some trigger signals require two claims to satisfy the criteria. If two are required, there will be a minimum and maximum separation in time the two claims must be found in the data in order to satisfy the trigger conditions, in addition to the claims matching other criteria. Trigger criteria require finding a diagnosis, procedural, and/or sometimes a code from the Global E&M List (referenced later). When a code from the Global E&M list is required to satisfy the criteria, that code must also exist in the list of “typical” codes for the episode. Each episode may have more than one set of triggering criteria that can be matched to indicate a positive signal for the episode of care in question.

Trigger Criteria:

SELECT * FROM episode_trigger WHERE episode_id=’EC1001′;

Facility Types:

IP – inpatient
OP – outpatient
PR – professional
IC – confirming (second) inpatient
OC – confirming (second) outpatient
PC – confirming (second) professional
EP – episode

The second claim is required if REQ_CONF_CLAIM=1. This can occur for episodes triggered by another episode.

Lookback Episode Triggers:

SELECT LOOKBACK_EPISODE_ID FROM episode_lookback_trigger WHERE EPISODE_ID=’EC1001′;

Trigger, Typical Diagnosis, Procedure, and Potentially Avoidable Complication Codes

These codes are all retrieved essentially the same way. The “FUNCTION_ID” in the episode_to_code table defines the “function” the code has for the episode.

Function_id:

‘tr’ = trigger
‘dx’ = typical diagnosis
‘px’ = typical procedure
‘cm’ = potentially avoidable complication

The query to retrieve all of these codes for all of the functions is below. This does not include pharmacy codes, which are a special case. More on those later. The query below will also include all potential attributes of the code type. For example, the qualifying diagnosis is applicable to triggers (function_id=’tr’) and if it is 1, the trigger code is a qualifying diagnosis code. Typical procedures can be “sufficient”. If the “SUFFICIENT” field=1, then the typical procedure (function_id=’px’) code is sufficient.

SELECT DISTINCT
e.FUNCTION_ID AS function_id,
e.CODE_VALUE AS code_id,
e.CODE_TYPE_ID AS type_id,
c.DESCRIPTION AS code_name,
c.GROUP_ID AS group_id,
g.NAME AS group_name,
e.COMPLICATION AS complication_type,
e.CORE AS core,
e.SUFFICIENT AS sufficient,
e.PAS AS pas,
e.RX_FUNCTION AS rx_assignment,
c.BETOS_CATEGORY as betos_category,
e.QUALIFYING_DIAGNOSIS as qualifying_diagnosis
FROM episode_to_code e
JOIN code c ON e.CODE_VALUE=c.VALUE
AND e.CODE_TYPE_ID=c.TYPE_ID
AND e.CODE_MULTUM_CATEGORY=c.`MULTUM_CATEGORY`
AND e.EPISODE_ID=’EC1001′
AND e.FUNCTION_ID<>’rx’
JOIN `group` g ON c.GROUP_ID=g.`ID`;

To limit this query such that it only includes a given function (for example, only typical diagnosis codes) add the “AND e.FUNCTION_ID=’dx’” line seen near the bottom in the example below:

SELECT DISTINCT
e.FUNCTION_ID AS function_id,
e.CODE_VALUE AS code_id,
e.CODE_TYPE_ID AS type_id,
c.DESCRIPTION AS code_name,
c.GROUP_ID AS group_id,
g.NAME AS group_name,
e.COMPLICATION AS complication_type,
e.CORE AS core,
e.SUFFICIENT AS sufficient,
e.PAS AS pas,
e.RX_FUNCTION AS rx_assignment,
c.BETOS_CATEGORY as betos_category,
e.QUALIFYING_DIAGNOSIS as qualifying_diagnosis
FROM episode_to_code e
JOIN code c ON e.CODE_VALUE=c.VALUE
AND e.CODE_TYPE_ID=c.TYPE_ID
AND e.CODE_MULTUM_CATEGORY=c.`MULTUM_CATEGORY`
AND e.EPISODE_ID=’EC1001′
AND e.FUNCTION_ID=’dx’
JOIN `group` g ON c.GROUP_ID=g.`ID`;

The pharmacy codes are a special case. The codes stored in the episode_to_code table are not NDC codes, but RxNorm codes that indicate groups of NDC codes. The same model still applies for retrieving the RxNorm codes, but an additional join is required to retrieve the NDC codes.

SELECT DISTINCT
e.FUNCTION_ID,
c.VALUE AS CODE_VALUE,
e.CODE_TYPE_ID,
c.DESCRIPTION AS CODE_NAME,
e.CODE_VALUE AS GROUP_ID,
c.DESCRIPTION AS GROUP_NAME,
e.RX_FUNCTION AS rx_assignment,
c.BETOS_CATEGORY as betos_category
FROM episode_to_code e
JOIN ndc_to_rxcui nr ON e.CODE_VALUE=nr.rxcui
JOIN code_ndc c ON nr.ndc = c.VALUE
AND e.CODE_TYPE_ID=c.TYPE_ID
AND e.EPISODE_ID=’EC1001′
AND e.CODE_TYPE_ID=’RX’;

Subtypes

Episodes may also have subtypes, or variants. These are used to adjust for the severity of the episode and reduce the need to have multiple episodes of the same type. Subtypes can be based on code groups, typical diagnosis, or typical procedure codes. In addition to the subtypes are universal risk factors. They are applicable to all episodes and are used to adjust for severity as well.

You may have noticed function_id of ‘sp’ retrieved from the query above. Those are the subtypes. The sub_type_group_id is “ST” concatenated with the group_id, so to retrieve the subtypes with the correct sub_type_group_id:

SELECT DISTINCT
e.FUNCTION_ID AS function_id,
e.CODE_VALUE AS code_id,
e.CODE_TYPE_ID AS type_id,
c.DESCRIPTION AS code_name,
CONCAT(‘ST’,c.GROUP_ID) AS sub_type_group_id,
g.NAME AS sub_type_group_name
FROM episode_to_code e
JOIN code c ON e.CODE_VALUE=c.VALUE
AND e.CODE_TYPE_ID=c.TYPE_ID
AND e.CODE_MULTUM_CATEGORY=c.`MULTUM_CATEGORY`
AND e.EPISODE_ID=’EC1001′
AND e.FUNCTION_ID=p’
JOIN `group` g ON c.GROUP_ID=g.`ID`;

Universal Risk Factors:

The following query will get the universal risk factors.

SELECT
u.code_id,
u.type_id,
c.DESCRIPTION AS ‘code_name’,
u.group_id,
u.group_name
FROM universal_risk_factors u
LEFT JOIN code c ON u.code_id=c.VALUE
AND u.type_id=c.TYPE_ID;

Global E&M Codes:

SELECT
c.VALUE AS code_id,
c.TYPE_ID as type_id,
c.DESCRIPTION AS code_name,
c.GROUP_ID AS group_id,
g.NAME AS group_name
FROM code c
JOIN global_em e ON e.VALUE=c.VALUE
AND e.TYPE_ID = c.TYPE_ID
JOIN `group` g on c