Table Name: Measurement

The MEASUREMENT table will house records from PATBILL, PATCPT, VITALS, GENLAB, LAB_RESULT and PATICD_DIAG that have been mapped to the measurement domain.

Measurements are recorded in the PATBILL table as standard charges. Premier captures the date the measurement is made in the SERV_DATE field thus, the MEASUREMENT_DATE is determined from the VISIT_START_DATE from VISIT_OCCURRENCE and PATBILL.SERV_DATE unless the start date is greater than the end of the month, then it’s truncated to the end of month. For measurements recorded in the PATCPT table, the date the measurement was made is unknown so MEASUREMENT_DATE is recorded as VISIT_END_DATE.

The MEASUREMENT table contains data from after 2016 only. Data prior to 2016 is not associated with a standard code (LOINC, etc.). Records mapped from LAB_RESULT have a column named data_source_ind, with a value of 3 or 4. Records with a data_source_ind of 4 are part of the current dataset, and have a LOINC code available. Records with a data_source_ind of 3 are historical records (prior to 2016), with no LOINC available.

The LAB_SENS table contains Microbiology Sensitivty Results. THis data has not yet been mapped.

Reading from PATBILL, PATCPT, PATICD_DIAG

The field mapping is performed as follows:

Destination Field Source Field Applied Rule Comment
MEASUREMENT_ID - System generated  
PERSON_ID PAT.MEDREC_KEY    
MEASUREMENT_CONCEPT_ID PATCPT.CPT_CODE
PATBILL.STD_CHG_CODE
PATICD_DIAG.ICD_CODE
PATBILL.STD_CHG_DESC		 QUERY: SOURCE TO STANDARD
SELECT TARGET_CONCEPT_ID FROM CTE_VOCAB_MAP WHERE SOURCE_VOCABULARY_ID IN ('CPT4', 'HCPCS', 'ICD10CM', 'ICD9CM', 'JNJ_PMR_PROC_CHRG_CD') AND TARGET_DOMAIN_ID = 'Measurement'		 Only capture those records that have a domain map to Measurement.
MEASUREMENT_DATE VISIT_OCCURRENCE.VISIT_START_DATEPATBILL.SERV_DATE
Or
VISIT_OCCURRENCE.VISIT_END_DATE
Or
VISIT_OCCURRENCE.VISIT_START_DATEPATICD_PROC.PROC_DAY		   If measurement is from PATBILL use service date and visit start date unless the service date is greater than the end of the month
If measurement comes from PATCPT then use visit end date
MEASUREMENT_DATETIME - NULL  
MEASUREMENT_TYPE_CONCEPT_ID - All records within the measurement table should have a measurement_type_concept_id = 32875 (Provider financial system)  
OPERATOR_CONCEPT_ID - NULL  
VALUE_AS_NUMBER - See query below  
VALUE_AS_CONCEPT_ID - NULL  
UNIT_CONCEPT_ID -   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
RANGE_LOW - NULL  
RANGE_HIGH - NULL  
PROVIDER_ID PATICD_PROC.PROC_PHY
PAT.ADMPHY		    
VISIT_OCCURRENCE_ID PAT.PAT_KEY    
MEASUREMENT_SOURCE_VALUE   SELECT SOURCE_VALUE FROM (SELECT CONCAT(STD_CHG_DESC, ' / ', HOSP_CHG_DESC) AS SOURCE_VALUE FROM PATBILL A JOIN CHGMSTR B ON A.STD_CHG_CODE=B.STD_CHG_CODE JOIN hospchg C ON A.hosp_chg_id=C.hosp_chg_id ) A UNION(SELECT CPT_CODE AS SOURCE_VALUE FROM PATCPT)  
MEASUREMENT_SOURCE_CONCEPT_ID - QUERY: SOURCE TO SOURCE
SELECT SOURCE_CONCEPT_ID FROM CTE_VOCAB_MAP WHERE SOURCE_VOCABULARY_ID IN ('CPT4', 'HCPCS') AND TARGET_VOCABULARY_ID IN ('CPT4', 'HCPCS') AND DOMAIN_ID='Measurement'		 Only populated for standard coding CPT4, and HCPCS codes
UNIT_SOURCE_VALUE - NULL  
VALUE_SOURCE_VALUE - NULL  

Reading from VITALS

The field mapping is performed as follows:

Destination Field Source Field Logic Comment
measurement_id
person_id PAT.MEDREC_KEY
measurement_concept_id lab_test_loinc_code when lab_test_loinc_code !=’’, map to LOINC using lab_test_loinc_code = concept.concept_code and vocabulary_id =’LOINC’ :

Use the Source-to-Standard Query.

WHERE SOURCE_VOCABULARY_ID IN ('LOINC') AND TARGET_STANDARD_CONCEPT = 'S' AND TARGET_INVALID_REASON IS NULL

when lab_test_loinc_code = ‘’, then map to SNOMED using regexp_replace(lab_test, '\\(.*\\)', '') = c.concept_name and c. standard_concept ='S' and c.vocabulary_id ='SNOMED'

if there’s still no standard concept, set to 0

measurement_date observation_datetime
measurement_source_value lab_test, lab_test_loinc_code When lab_test_loinc_code!=''

Then lab_test_loinc_code

When lab_test_loinc_code ='' Then lab_test

value_as_number test_result_numeric_value
unit_concept_id lab_test_result_unit map using STCM with the source_vocabulary_id =’JNJ UNITS’
value_as_concept_id
meas_event_field_concept_id
measurement_event_id
measurement_source_concept_id lab_test, lab_test_loinc_code when lab_test_loinc_code !=’’, map to LOINC using lab_test_loinc_code = concept.concept_code and vocabulary_id =’LOINC’:

select concept_id from VITALS join concept c on lab_test_loinc_code = c.concept_code and c.vocabulary_id ='LOINC' where lab_test_loinc_code !=''

when lab_test_loinc_code = ‘’, then map to SNOMED using regexp_replace(lab_test, '\\(.*\\)', '') = c.concept_name and c.vocabulary_id ='SNOMED'

measurement_datetime observation_datetime

measurement_time observation_time_of_day
measurement_type_concept_id 32836 EHR physical examination
operator_concept_id numeric_value_operator The following mappings should be leveraged:

> map to 4172704

< map to 4171756

+ map to 0

=> map to 4171755

<= map to 4171754

range_low
range_high
provider_id
visit_occurrence_id pat_key Referncing visit that exists in VISIT_OCCURRENCE, a combindation of medrec_key and pat_key create a unique visit for a specific patient.
visit_detail_id
unit_source_value lab_test_result_unit
unit_source_concept_id 0 0
value_source_value lab_test_result

Reading from GENLAB:

Destination Field Source Field Logic Comment
measurement_id
person_id PAT.MEDREC_KEY
measurement_concept_id lab_test_loinc_code when lab_test_loinc_code !=’’, map to LOINC using lab_test_loinc_code = concept.concept_code and vocabulary_id =’LOINC’:

Use the Source-to-Standard Query.

WHERE SOURCE_VOCABULARY_ID IN ('LOINC')

AND TARGET_STANDARD_CONCEPT = 'S'

AND TARGET_INVALID_REASON IS NULL

when lab_test_loinc_code = ‘’, then map to SNOMED using regexp_replace(lab_test, '\\(.*\\)', '') = c.concept_name and c. standard_concept ='S' and c.vocabulary_id ='SNOMED'

</br> if lab_test_loinc_code = 'LP17803-5-15' map to 4179840 (Rh blood group typing) </br> if lab_test_loinc_code = 'LP30736-0-3' map to 3045688 (Homocysteine cysteine disulfide [Moles/volume] in Serum or Plasma) </br> if there’s still no standard concept, set to 0

measurement_date collection_datetime
measurement_source_value lab_test, lab_test_loinc_code When lab_test_loinc_code!=''

Then lab_test_loinc_code

When lab_test_loinc_code =''

Then lab_test

value_as_number test_result_numeric_value
unit_concept_id lab_test_result_unit map using STCM with the source_vocabulary_id =’JNJ UNITS’
value_as_concept_id
meas_event_field_concept_id
measurement_event_id
measurement_source_concept_id lab_test, lab_test_loinc_code when lab_test_loinc_code !=’’, map to LOINC using lab_test_loinc_code = concept.concept_code and vocabulary_id =’LOINC’:

select concept_id from VITALS join concept c on lab_test_loinc_code = c.concept_code and c.vocabulary_id ='LOINC' where lab_test_loinc_code !=''

when lab_test_loinc_code = ‘’, then map to SNOMED using regexp_replace(lab_test, '\\(.*\\)', '') = c.concept_name and c.vocabulary_id ='SNOMED'

measurement_datetime collection_datetime

measurement_time collection_datetime
measurement_type_concept_id 32836 EHR physical examination
operator_concept_id numeric_value_operator The following mappings should be leveraged:

> map to 4172704

< map to 4171756

+ map to 0

=> map to 4171755

<= map to 4171754

range_low
range_high
provider_id
visit_occurrence_id pat_key Referncing visit that exists in VISIT_OCCURRENCE, a combindation of medrec_key and pat_key create a unique visit for a specific patient.
visit_detail_id
unit_source_value lab_test_result_unit
unit_source_concept_id 0 0
value_source_value lab_test_result

Reading from LAB_RESULTS

Destination Field Source Field Logic Comment
measurement_id pat_key

specimen_id

observation

System generated. Consistent with the measurement_id attribute creation.

System Generated - Consisten with current schema design.

value_source_value observation Directly Mapped.
person_id pat_key Lookup of PAT.MEDREC_KEY leveraging the PAT_KEY.

SELECT PAT.MEDREC_KEY

FROM PAT, LAB_RES

WHERE PAT.PAT_KEY = LAB_RES.PAT_KEY

measurement_concept_id test The LAB_RES table provides a LOINC formatted description but does not provide the actual LOINC code utilized in this table. Usagi was leveraged to infer standard concept codes based on the LOINC formatted descriptions.

Leverage source to concept mappings (+80% mapped).

measurement_date collection_datetime Direct mapping
measurement_datetime collection_datetime Direct mapping
measurement_time collection_datetime Direct mapping
unit_source_value LAB_RESULT Transformation:

This hold the source codes inferred by Usagi for the LAB_RESULT.TEST attribute which Premier identifies as LOINC for source data with a DATA_SOURCE_IND of '4'.

measurement_type_concept_id GENLAB; LAB_RESULT Transformation:

Values mapped to Concept ID 5001, 'Test ordered through EHR'

operator_concept_id
value_as_number
value_as_concept_id
unit_concept_id
range_low NULL
range_high NULL
provider_id GENLAB; LAB_RESULT Transformation:

Lookup PAT.ADM_PHY leveraging PAT_KEY.

SELECT PAT.ADM_PHY

FROM PAT

WHERE PAT.PAT_KEY = GEN_LAB.PAT_KEY

visit_occurrence_id
visit_detail_id NULL
measurement_source_value
measurement_source_concept_id Leveraged standard Source-to-Concept lookup.

Change Log:

2024.03.21:

Added mapping for genlab.lab_test_loinc_code (‘LP17803-5-15’ and ‘LP30736-0-3’)

2024.03.12:

Moved Surgery (concept_id=3016562) to the Observation table

2021.08.11:

Updated MEASUREMENT_TYPE_CONCEPT_ID to leverage standard concept id.

29-Aug-2023

Added readings from VITALS, GENLAB, LAB_RESULT

Please contact Clair Blacketer (https://github.com/clairblacketer) if you have any questions