Table Name: Observation
The observation table houses additional demographic and visit data that is housed in Premier. Certain ICD9, CPT, and standard charge codes map to standardized observation table concept. Marital status and patient type records are specific to Premier and map to non-standard observation table concepts.
Additionally, procedures that occur on the same day as billing records for operation time will have operation time calculated and recorded in the observation table.
PATICD_DIAG.ICD_CODE, PATICD_PROC.ICD_CODE, PATCPT.CPT_CODE, and PATBILL.STD_CHG_CODE map to OBSERVATION.OBSERVATION_CONCEPT_ID using the source to standard cte_vocab_map. These records also map to OBSERVATION.OBSERVATION_SOURCE_CONCEPT_ID using the source to source cte_vocab_map.
PAT.MART_STATUS and PAT.PATTYPE map to set OBSERVATION_CONCEPT_ID codes described in the table below and OBSERVATION_SOURCE_CONCEPT_ID=0.
The observation start date is assigned the VISIT_START_DATE. The ASSOCIATED_PROVIDER_ID that is provided is the randomly generated key provided by Premier for the provider that admitted the patient. There are two providers that exist in Premier, the admitting and attending. This ETL makes the decision to use admitting because it is unknown whether the admitting provider, attending provider or another person diagnosed the person.
In Premier, many procedures are recorded in the PATICD_PROC table, which includes the date the procedure was performed as PATICD_PROC.PROC_DATE. Certain billing records in PATBILL include information on surgical operation time. The sample code below the field mapping table returns surgical operation time values in minutes for procedures where operation time billing record(s) happen on the same day. It is assumed that if a procedure and an operating time bill happen on the same day, then the operating time is associated with the procedure. These operation time values move to the Observation table and the OBSERVATION_DATE equals the corresponding PROCEDURE_DATE (which is PROC_DATE). To associate a surgical operation time with a procedure: OBSERVATION.VISIT_OCCURRENCE_ID=PROCEDURE_OCCURRENCE.VISIT_OCCURRENCE_ID AND OBSERVATION.OBSERVATION_DATE=PROCEDURE_OCCURRENCE.PROCEDURE_DATE. There are three providers that exist in Premier, the admitting, attending, and procedure. This ETL makes the decision to use admitting physician for all observations except operation time because it is unknown whether the admitting provider, attending provider or another person obtained the observation.
##TODO:
- For operation time observations, the provider is set as the procedure physician. Only records that fall within an OBSERVATION_PERIOD are available for each person. The VISIT_OCCURRENCE table must be created before the OBSERVATION table is created.
The field mapping is performed as follows:
| Destination Field | Source Field | Applied Rule | Comment |
|---|---|---|---|
| OBSERVATION_ID | - | System-generated | |
| PERSON_ID | PAT.MEDREC_KEY | ||
| OBSERVATION_CONCEPT_ID | PATCPT.CPT_COD PATBILL.STD_CHG_CODE PATICD_PROC.ICD_CODEPAT ICD_DIAG.ICD_CODE | For records from PATCPT.CPT_CODE, and PATBILL.STD_CHG_CODE: QUERY: SOURCE TO STANDARD SELECT TARGET_CONCEPT_ID FROM CTE_VOCAB_MAP WHERE SOURCE_VOCABULARY_ID IN ('CPT4', 'HCPCS', 'JNJ_PMR_OBS_CODE', 'JNJ_PMR_PROC_CHRG_CD') AND TARGET_DOMAIN_ID = 'Observation' For records from PATICD_PROC.ICD_CODE and PATICD_DIAG.ICD_CODE: where ICD_VERSION=9 QUERY: SOURCE TO STANDARD SELECT TARGET_CONCEPT_ID FROM CTE_VOCAB_MAP WHERE SOURCE_VOCABULARY_ID IN ('ICD9CM') AND TARGET_DOMAIN_ID = 'Observation' For records from PATICD_PROC.ICD_CODE and PATICD_DIAG.ICD_CODE:where ICD_VERSION=10 QUERY: SOURCE TO STANDARD SELECT TARGET_CONCEPT_ID FROM CTE_VOCAB_MAP WHERE SOURCE_VOCABULARY_ID IN ('ICD10CM') AND TARGET_DOMAIN_ID = 'Observation' For PAT.MART_STATUS, OBSERVATION_CONCEPT_ID=4053609 For PAT.PATTYPE, OBSERVATION_CONCEPT_ID=40769091 When operation time observation values then 3016562 | |
| OBSERVATION_DATE | PATBILL.SERV_DATE VISIT_OCCURRENCE.VISIT_START_DATE OR VISIT_OCCURRENCE.VISIT_START_DATE | If observation is from PATBILL use service date If observation comes from PAT.MS_DRG, PATCPT.CPT_CODE, PATICD_PROC.ICD_CODE, PATICD_DIAG.ICD_CODE then use visit start date For operation time observation, a combination of procedure date and visit start date unless the procedure date is greater than the end of the month | |
| OBSERVATION_DATETIME | - | NULL | |
| OBSERVATION_TYPE_CONCEPT_ID | - | 38000281 Observation recorded from EHR with text result | |
| VALUE_AS_NUMBER | |||
| VALUE_AS_STRING | PAT.MART_STATUS PAT.PAT_TYPE | Value_as_string only populated for Premier-specific fields mart_status and pat_type Marital status values populated directly from PAT.MART_STATUS as ‘M’, ‘S’, ‘O’, or ‘U’ select pat_type_desc from pattype pjoin pat p1 on p1.pat_type=p.pat_type | Use look up values in the text fields. |
| VALUE_AS_CONCEPT_ID | PATCPT.CPT_COD PATBILL.STD_CHG_CODE PATICD_PROC.ICD_CODEPAT ICD_DIAG.ICD_CODE | same rules as for concept_id and source_concept_id, but use ‘Maps to value’ relationship | |
| QUALIFER_CONCEPT_ID | - | NULL | |
| UNIT_CONCEPT_ID | - | For operation time records 8550 Else NULL | Set UNIT_CONCEPT_ID = NULL when the source unit value is NULL;Set UNIT_CONCEPT_ID = 0 when source unit value is not NULL but doesn’t have a mapping |
| PROVIDER_CONCEPT_ID | PAT.ADMPHY | When operation time PATICD_PROC.PROC_PHY Else PAT.ADMPHY | |
| VISIT_OCCURRENCE_ID | PAT.PAT_KEY | ||
| OBSERVATION_SOURCE_VALUE | PAT.DRG PATICD.ICD_CODE PATCPT.CPT_CODE CHARGE CODE | Standard charge code value:SELECT CONCAT(STD_CHG_DESC, ' / ', HOSP_CHG_DESC) AS SOURCE_VALUE FROM PATBILL AJOIN CHGMSTR B ON A.STD_CHG_CODE=B.STD_CHG_CODE JOIN hospchg C ON A.hosp_chg_id=C.hosp_chg_id | |
| OBSERVATION_SOURCE_CONCEPT_ID | - | QUERY: SOURCE TO SOURCE SELECT SOURCE_CONCEPT_ID FROM CTE_VOCAB_MAP WHERE SOURCE_VOCABULARY_ID IN ('ICD9CM', 'ICD10CM', 'CPT4', 'HCPCS') AND TARGET_VOCABULARY_ID IN ('ICD9CM', 'ICD10CM', 'CPT4', 'HCPCS') For operation time records, NULL for now | |
| UNITS_SOURCE_VALUE | - | NULL | |
| QUALIFIER_SOURCE_VALUE | - | NULL |
27-Sept-2024
- Removed admitting source and discharge destination from cdm.observation
03-Mar-2024
- Added Surgery (concept_id=3016562)
01-Aug-2023
- Added Maps to value logic
- Cleared formatting