condition

The condition table is related to the Condition resource that is available via the FHIR API.

The condition table combines all diagnoses data from all data sources (clinical and claims) for a HIEBus person (recordgroup) into a consolidated structure.

The condition table includes columns to trace rows to their underlying data source. However, commonly this table is used as the source of all available diagnoses regardless of where the data originated. This table represents a unified model for diagnoses for a person from all sources. Diagnoses are not reconciled in this table. If an event is documented by two data sources (i.e. related clinical and claims systems), the diagnoses for this event will be duplicated in this table.

Diagnoses in the condition table are annotated with computed columns that can be used to roll up the detailed codes (commonly ICD10) into higher order representations of conditions such as: Hierarchical Condition Categories (HCCs), Condition Categories (HCUP CCSR), and care setting.

Source Concepts

HIEBus problems (encounter)
HIEBus claimproblems (claim)

Table Schema

Name	Type	Description
personid	VARCHAR(38) GUID	Unique identifier representing a unique person that may include data from several sources that have been linked. This identifier is the person_id of a HIEBus recordgroup.
conditionid	VARCHAR(38) GUID	Internal identifier of the HIEBus source row.
code	VARCHAR	Mapped by Rosetta. The primary mapped terminology code determined to represent the diagnosis. This is the preferred mapped concept determined by the Rosetta Terminology Service.
display	VARCHAR	Mapped by Rosetta. The primary mapped terminology description determined to represent the diagnosis. This is the preferred mapped concept determined by the Rosetta Terminology Service.
codesystem	VARCHAR	Mapped by Rosetta. The terminology namespace of the code field. This is the preferred mapped concept determined by the Rosetta Terminology Service.
termid	INTEGER	A reference to the original primary term that can be used to show the original code or relate to alternative mappings.
alternatetermids	VARCHAR	A comma delimited list of termids that provide supplemental terms (usually in alternate coding systems) to represent the diagnosis.
asserteddate	VARCHAR	The date the diagnosis was documented.
stopdate	VARCHAR	The data the diagnosis was no longer active.
encounterid	VARCHAR(38) GUID	Source encounter, if determined from an encounter-based data source.
claimid	VARCHAR(38) GUID	Source claim, if determined from an claim-based data source.
datasource	VARCHAR	The name of the data source. Commonly determined from the namespace of the source terminology namespace.
itemsequence	INTEGER	The sequence assigned by to the condition by the source system. Used to compute the isprimary column.
type	VARCHAR	Mapped by Rosetta. Type of diagnosis. This is the preferred mapped concept determined by the Rosetta Terminology Service.
typecode	VARCHAR	Mapped by Rosetta. Coded Value. Type of diagnosis. Used to compute the isprimary column. This is the preferred mapped concept determined by the Rosetta Terminology Service.
typetermid	INTEGER	Reference to HIEBus terminology properties for the claim.
primarycaresetting	VARCHAR	Computed. A normalized description of the place of service in which the diagnoses was recorded. This property is copied from the related encounter or claim.
hcc	INTEGER	Computed. The number of the related Hierarchical Condition Category.
hccdescription	VARCHAR	Computed. The description of the related Hierarchical Condition Category.
ccscategoryid	VARCHAR	Computed. The identifier of the related CCSR condition category.
ccsdescription	VARCHAR	Computed. The description of the related CCSR condition category.
ischronic	BOOLEAN	Computed. TRUE if the condition is tagged Chronic by the HCUP Chronic Condition Indicator algorithm, described here
isprimary	BOOLEAN	Computed. TRUE if the condition is the primary diagnosis for the related encounter or claim. Computed using the itemsequence and typecode columns. Exactly one condition will be set isprimary=TRUE for a given encounterid or claimid.
annotations	VARCHAR	Computed. A comma delimited set of strings that represent computed features of the condition.
lastupdated	TIMESTAMP	System generated timestamp indicating the most recent change to this data row. Used for differential operations. Managed in UTC.

Back to Tables