Skip to content

Changes

Page history
Update Admission occasion, admission reason and discharge reason authored by Henke, Elisa's avatar Henke, Elisa
Hide whitespace changes
Inline Side-by-side
Admission-occasion,-admission-reason-and-discharge-reason.md
View page @ d82a4bf2
...@@ -16,16 +16,18 @@ ...@@ -16,16 +16,18 @@
| data_one |visit_occurrence_id| | This id represents the visit_occurrence_id of the visit_occurrence table. | | data_one |visit_occurrence_id| | This id represents the visit_occurrence_id of the visit_occurrence table. |
| |visit_detail_id| | See post processing for visit_detail_id | | |visit_detail_id| | See post processing for visit_detail_id |
| data_two |observation_source_value| | | | data_two |observation_source_value| | |
| fhir_logical_id | fhir_logical_id | | |
| fhir_identifier | fhir_identifier | | |
### Input POST_PROCESS_MAP ### Input POST_PROCESS_MAP
| id| type | data_one | data_two | omop_id | omop_table | | id| type | data_one | data_two | omop_id | omop_table | fhir_logical_id | fhir_identifier |
| ---- | ---- | ---- | ---- |---- | ---- | | ---- | ---- | ---- | ---- |---- | ---- | ---- | ---- |
| 1 | ENCOUNTER| 2020-12-05 07:45:00;2 | 14 |1|discharge_reason| | 1 | ENCOUNTER| 2020-12-05 07:45:00;2 | 14 |1|discharge_reason| cid.001 | cid.001 |
### Output OBSERVATION ### Output OBSERVATION
|observation_id|person_id|observation_concept_id|observation_date|observation_datetime|observation_type_concept_id|value_as_string|visit_occurrence_id|visit_detail_id|observation_source_value| |observation_id|person_id|observation_concept_id|observation_date|observation_datetime|observation_type_concept_id|value_as_string|visit_occurrence_id|visit_detail_id|observation_source_value|fhir_logical_id|fhir_identifier|
| ---- | ---- | ---- | ---- |---- | ---- | ---- | ---- | ---- | ---- | | ---- | ---- | ---- | ---- |---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- |
| 1 | 1 | 4203130 | 2020-12-05 | 2020-12-05 07:45:00 | 44813951 | 14 | 2 | | 14 | | 1 | 1 | 4203130 | 2020-12-05 | 2020-12-05 07:45:00 | 44813951 | 14 | 2 | | 14 | cid.001 | cid.001 |
:information_source: The default observation_type_concept_id for admission reason is: 44810203 (Reason for admission) and for admission occasion: 44810199 (Admission details). :information_source: The default observation_type_concept_id for admission reason is: 44810203 (Reason for admission) and for admission occasion: 44810199 (Admission details).
...@@ -44,7 +46,9 @@ BEGIN ...@@ -44,7 +46,9 @@ BEGIN
44810203 AS observation_type_concept_id, 44810203 AS observation_type_concept_id,
data_two AS value_as_string, data_two AS value_as_string,
Split_part(data_one, ';', 2)::integer AS visit_occurrence_id, Split_part(data_one, ';', 2)::integer AS visit_occurrence_id,
data_two AS observation_source_value data_two AS observation_source_value,
fhir_logical_id,
fhir_identifier
FROM cds_etl_helper.post_process_map FROM cds_etl_helper.post_process_map
WHERE omop_table = 'admission_reason'), adReasonConcepts AS WHERE omop_table = 'admission_reason'), adReasonConcepts AS
( (
...@@ -54,7 +58,7 @@ BEGIN ...@@ -54,7 +58,7 @@ BEGIN
WHERE source_vocabulary_id = 'Admission Reason 1&2') WHERE source_vocabulary_id = 'Admission Reason 1&2')
SELECT * SELECT *
FROM adReasonCodes ar FROM adReasonCodes ar
LEFT JOIN adReasonConcepts arc JOIN adReasonConcepts arc
ON ar.observation_source_value = arc.source_code), ON ar.observation_source_value = arc.source_code),
upsert AS upsert AS
( (
...@@ -74,7 +78,9 @@ BEGIN ...@@ -74,7 +78,9 @@ BEGIN
observation_type_concept_id, observation_type_concept_id,
value_as_string, value_as_string,
visit_occurrence_id, visit_occurrence_id,
observation_source_value observation_source_value,
fhir_logical_id,
fhir_identifier
) )
SELECT person_id, SELECT person_id,
observation_concept_id, observation_concept_id,
...@@ -83,7 +89,9 @@ BEGIN ...@@ -83,7 +89,9 @@ BEGIN
observation_type_concept_id, observation_type_concept_id,
value_as_string, value_as_string,
visit_occurrence_id, visit_occurrence_id,
observation_source_value observation_source_value,
fhir_logical_id,
fhir_identifier
FROM adReasons FROM adReasons
WHERE NOT EXISTS WHERE NOT EXISTS
( (
...@@ -110,7 +118,9 @@ BEGIN ...@@ -110,7 +118,9 @@ BEGIN
44810199 AS observation_type_concept_id, 44810199 AS observation_type_concept_id,
data_two AS value_as_string, data_two AS value_as_string,
Split_part(data_one, ';', 2)::integer AS visit_occurrence_id, Split_part(data_one, ';', 2)::integer AS visit_occurrence_id,
data_two AS observation_source_value data_two AS observation_source_value,
fhir_logical_id,
fhir_identifier
FROM cds_etl_helper.post_process_map FROM cds_etl_helper.post_process_map
WHERE omop_table = 'admission_occasion'), adOccasionConcepts AS WHERE omop_table = 'admission_occasion'), adOccasionConcepts AS
( (
...@@ -120,7 +130,7 @@ BEGIN ...@@ -120,7 +130,7 @@ BEGIN
WHERE source_vocabulary_id = 'Ad Occasion Obs') WHERE source_vocabulary_id = 'Ad Occasion Obs')
SELECT * SELECT *
FROM adOccasionCodes ao FROM adOccasionCodes ao
LEFT JOIN adOccasionConcepts aoc JOIN adOccasionConcepts aoc
ON ao.observation_source_value = aoc.source_code), ON ao.observation_source_value = aoc.source_code),
upsert AS upsert AS
( (
...@@ -140,7 +150,9 @@ BEGIN ...@@ -140,7 +150,9 @@ BEGIN
observation_type_concept_id, observation_type_concept_id,
value_as_string, value_as_string,
visit_occurrence_id, visit_occurrence_id,
observation_source_value observation_source_value,
fhir_logical_id,
fhir_identifier
) )
SELECT person_id, SELECT person_id,
observation_concept_id, observation_concept_id,
...@@ -149,7 +161,9 @@ BEGIN ...@@ -149,7 +161,9 @@ BEGIN
observation_type_concept_id, observation_type_concept_id,
value_as_string, value_as_string,
visit_occurrence_id, visit_occurrence_id,
observation_source_value observation_source_value,
fhir_logical_id,
fhir_identifier
FROM adOccasions FROM adOccasions
WHERE NOT EXISTS WHERE NOT EXISTS
( (
...@@ -177,7 +191,9 @@ BEGIN ...@@ -177,7 +191,9 @@ BEGIN
data_two AS value_as_string, data_two AS value_as_string,
Split_part(data_one, ';', 2)::integer AS visit_occurrence_id, Split_part(data_one, ';', 2)::integer AS visit_occurrence_id,
data_two AS observation_source_value, data_two AS observation_source_value,
SUBSTRING(data_two, 1, 2) AS dis_reason_code SUBSTRING(data_two, 1, 2) AS dis_reason_code,
fhir_logical_id,
fhir_identifier
FROM cds_etl_helper.post_process_map FROM cds_etl_helper.post_process_map
WHERE omop_table = 'discharge_reason'), disReasonConcepts AS WHERE omop_table = 'discharge_reason'), disReasonConcepts AS
( (
...@@ -187,7 +203,7 @@ BEGIN ...@@ -187,7 +203,7 @@ BEGIN
WHERE source_vocabulary_id = 'Dis Reason 1&2 Obs') WHERE source_vocabulary_id = 'Dis Reason 1&2 Obs')
SELECT * SELECT *
FROM disReasonCodes dr FROM disReasonCodes dr
LEFT JOIN disReasonConcepts drc JOIN disReasonConcepts drc
ON dr.dis_reason_code = drc.source_code), ON dr.dis_reason_code = drc.source_code),
upsert AS upsert AS
( (
...@@ -209,7 +225,9 @@ BEGIN ...@@ -209,7 +225,9 @@ BEGIN
observation_type_concept_id, observation_type_concept_id,
value_as_string, value_as_string,
visit_occurrence_id, visit_occurrence_id,
observation_source_value observation_source_value,
fhir_logical_id,
fhir_identifier
) )
SELECT person_id, SELECT person_id,
observation_concept_id, observation_concept_id,
...@@ -218,7 +236,9 @@ BEGIN ...@@ -218,7 +236,9 @@ BEGIN
observation_type_concept_id, observation_type_concept_id,
value_as_string, value_as_string,
visit_occurrence_id, visit_occurrence_id,
observation_source_value observation_source_value,
fhir_logical_id,
fhir_identifier
FROM disReasons FROM disReasons
WHERE NOT EXISTS WHERE NOT EXISTS
( (
......