Destination Field | Source field | Logic | Comment field |
---|---|---|---|
measurement_id | |||
person_id | patient | Lookup in the person table: map by mapping person.person_source_value to patient. | |
measurement_concept_id | code | Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from procedures pr join cte_target_vocab_map ctvm on ctvm.source_code = pr.code and ctvm.target_domain_id = ‘Measurement’ and ctvm.target_vocabulary_id = ‘SNOMED’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL | |
measurement_date | date | ||
measurement_datetime | date | Use 00:00:00 as the time. | |
measurement_time | date | ||
measurement_type_concept_id | Use concept_id 5001 for all records | ||
operator_concept_id | |||
value_as_number | |||
value_as_concept_id | |||
unit_concept_id | |||
range_low | |||
range_high | |||
provider_id | |||
visit_occurrence_id | encounter | Lookup visit_occurrence_id using encounter, joining to temp table defined in AllVisitTable.sql. | |
visit_detail_id | |||
measurement_source_value | code | ||
measurement_source_concept_id | code | Use code to lookup source_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from cte_source_vocab_map csvm join procedures pr on csvm.source_code = pr.code and csvm.source_vocabulary_id = ‘SNOMED’ | |
unit_source_value | |||
value_source_value |
Destination Field | Source field | Logic | Comment field |
---|---|---|---|
measurement_id | |||
person_id | patient | Map by mapping person.person_source_value to patient. Find person.person_id by mapping encouters.patient to person.person_source_value. | |
measurement_concept_id | code | Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from observations o join cte_target_vocab_map ctvm on ctvm.source_code = o.code and ctvm.target_domain_id = ‘Measurement’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL | |
measurement_date | date | ||
measurement_datetime | date | Use 00:00:00 as time. | |
measurement_time | |||
measurement_type_concept_id | Use concept_id 5001 for all records | ||
operator_concept_id | |||
value_as_number | value | ||
value_as_concept_id | |||
unit_concept_id | |||
range_low | |||
range_high | |||
provider_id | |||
visit_occurrence_id | code | Lookup visit_occurrence_id using encounter, joining to temp table defined in AllVisitTable.sql. | |
visit_detail_id | |||
measurement_source_value | code | ||
measurement_source_concept_id | code | Use code to lookup source_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from cte_source_vocab_map csvm join observations o on csvm.source_code = o.code and csvm.source_vocabulary_id = ‘SNOMED’ | |
unit_source_value | units | ||
value_source_value | value |