Skip to content

Changes

Page history
Updated mapping of Admission occasion, admission reason and discharge reason authored by Henke, Elisa's avatar Henke, Elisa
Show whitespace changes
Inline Side-by-side
Admission-occasion,-admission-reason-and-discharge-reason.md
View page @ 45385362
......@@ -8,30 +8,32 @@
| :----------------: | :----------------: | :----------------: | :----------------: |
||observation_id| :heavy_check_mark: | This id is generated automatically.|
|omop_id|person_id| :heavy_check_mark: |This id represents the person_id of the person table.|
| |observation_concept_id| :heavy_check_mark: | The observation_source_value is used to search in source_to_concept_map table for the corresponding concept_id.|
| |observation_concept_id| :heavy_check_mark: | The observation_source_value is used to search in source_to_concept_map table for the corresponding concept_id. The search is based only on the first two digits of the discharge reason code (e.g. 07 for code 079).|
| data_one |observation_date| :heavy_check_mark: | |
| data_one |observation_datetime| | |
| |observation_type_concept_id| :heavy_check_mark: | default value: 32823 (EHR discharge reason)|
| data_two |value_as_string| | |
| data_one |visit_occurrence_id| | This id represents the visit_occurrence_id of the visit_occurrence table. |
| |observation_type_concept_id| :heavy_check_mark: | default value: 32817 (EHR)|
| data_two |value_as_string| | This value represents the third digit of the discharge reason code (e.g. 9 for code 079).|
| |value_as_concept_id| | The observation_source_value is used to search in source_to_concept_map table for the corresponding concept_id. The search is based only on the third digit of the discharge reason code (e.g. 9 for code 079).|
| fhir_logical_id |visit_occurrence_id| | This id represents the logical id of the Encounter FHIR resource which can be used to search the corresponding visit_occurrence entry. |
| |visit_detail_id| | See post processing for visit_detail_id |
| data_two |observation_source_value| | |
| | qualifier_source_value | | default value: Entlassungsgrund |
| fhir_logical_id | fhir_logical_id | | |
| fhir_identifier | fhir_identifier | | |
### Input POST_PROCESS_MAP
| 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| enc-cid.001 | enc-cid.001 |
| 1 | ENCOUNTER| 2020-12-05 07:45:00 | 079 |1|discharge_reason| enc-cid.001 | enc-cid.001 |
### 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|fhir_logical_id|fhir_identifier|
| ---- | ---- | ---- | ---- |---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- |
| 1 | 1 | 4203130 | 2020-12-05 | 2020-12-05 07:45:00 | 32823 | 14 | 2 | | 14 | enc-cid.001 | enc-cid.001 |
|observation_id|person_id|observation_concept_id|observation_date|observation_datetime|observation_type_concept_id|value_as_string|value_as_concept_id|visit_occurrence_id|visit_detail_id|observation_source_value|qualifier_source_value|fhir_logical_id|fhir_identifier|
| ---- | ---- | ---- | ---- |---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- |
| 1 | 1 | 4306655 | 2020-12-05 | 2020-12-05 07:45:00 | 32817 | 9 | 36210399 | 2 | | 079 | Entlassungsgrund | enc-cid.001 | enc-cid.001 |
:information_source: The default observation_type_concept_id for admission reason is: 32819 (EHR admission note) and for admission occasion: 32817 (EHR).
:information_source: The default qualifier_source_value for admission reason is: `Aufnahmegrund` and for admission occasion: `Aufnahmeanlass`.
:information_source: The default qualifier_concept_id for admission reason and admission occasion is: 44803020 (Primary reason for admission).
:information_source: The fields value_as_string and value_as_concept_id remain empty for admission reason and admission occasion as there is no separation of the digits in the respective codes.
## SQL Script
```sql
......@@ -46,9 +48,8 @@ WITH adReasonCodes AS
omop_id AS pp_person_id
, To_date(data_one, 'YYYY-MM-DD') AS observation_date
, To_timestamp(data_one, 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime
, 32819 AS observation_type_concept_id
, data_two AS value_as_string
, 44803020 AS qualifier_concept_id
, 32817 AS observation_type_concept_id
, 'Aufnahmegrund' AS qualifier_source_value
, data_two AS observation_source_value
, fhir_logical_id AS encounter_id
, fhir_identifier AS encounter_identifier
......@@ -61,7 +62,7 @@ WITH adReasonCodes AS
target_concept_id AS observation_concept_id
, source_code
FROM source_to_concept_map
WHERE source_vocabulary_id = 'Admission Reason 1&2'
WHERE source_vocabulary_id = 'Admission Reason'
)
, adReasons AS
(
......@@ -70,8 +71,7 @@ WITH adReasonCodes AS
, ar.observation_date
, ar.observation_datetime
, ar.observation_type_concept_id
, ar.value_as_string
, ar.qualifier_concept_id
, ar.qualifier_source_value
, ar.observation_source_value
, arc.observation_concept_id
, vo.visit_occurrence_id
......@@ -87,12 +87,10 @@ WITH adReasonCodes AS
(
UPDATE cds_cdm.observation ob
SET observation_source_value = ar.observation_source_value
, value_as_string = ar.value_as_string
, observation_concept_id = ar.observation_concept_id
FROM adReasons ar
WHERE ob.observation_type_concept_id = ar.observation_type_concept_id
WHERE ob.qualifier_source_value = ar.qualifier_source_value
AND ob.visit_occurrence_id = ar.visit_occurrence_id
AND ob.qualifier_concept_id = ar.qualifier_concept_id
RETURNING ob.*
)
INSERT INTO cds_cdm.observation
......@@ -102,8 +100,7 @@ INSERT INTO cds_cdm.observation
, observation_date
, observation_datetime
, observation_type_concept_id
, value_as_string
, qualifier_concept_id
, qualifier_source_value
, visit_occurrence_id
, observation_source_value
, fhir_logical_id
......@@ -115,8 +112,7 @@ SELECT
, observation_date
, observation_datetime
, observation_type_concept_id
, value_as_string
, qualifier_concept_id
, qualifier_source_value
, visit_occurrence_id
, observation_source_value
, fhir_logical_id
......@@ -127,9 +123,8 @@ WHERE NOT EXISTS
SELECT
1
FROM upsert
WHERE upsert.observation_type_concept_id = adReasons.observation_type_concept_id
WHERE upsert.qualifier_source_value = adReasons.qualifier_source_value
AND upsert.visit_occurrence_id = adReasons.visit_occurrence_id
AND upsert.qualifier_concept_id = adReasons.qualifier_concept_id
)
;
get diagnostics v_rowcount = row_count;
......@@ -149,8 +144,7 @@ WITH adOccasionCodes AS
, To_date(data_one, 'YYYY-MM-DD') AS observation_date
, To_timestamp(data_one, 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime
, 32817 AS observation_type_concept_id
, data_two AS value_as_string
, 44803020 AS qualifier_concept_id
, 'Aufnahmeanlass' AS qualifier_source_value
, data_two AS observation_source_value
, fhir_logical_id AS encounter_id
, fhir_identifier AS encounter_identifier
......@@ -163,7 +157,7 @@ WITH adOccasionCodes AS
target_concept_id AS observation_concept_id
, source_code
FROM source_to_concept_map
WHERE source_vocabulary_id = 'Ad Occasion Obs'
WHERE source_vocabulary_id = 'Admission Occasion'
)
, adOccasions AS
(
......@@ -172,8 +166,7 @@ WITH adOccasionCodes AS
, ao.observation_date
, ao.observation_datetime
, ao.observation_type_concept_id
, ao.value_as_string
, ao.qualifier_concept_id
, ao.qualifier_source_value
, ao.observation_source_value
, ao.encounter_id
, ao.encounter_identifier
......@@ -193,12 +186,10 @@ WITH adOccasionCodes AS
UPDATE cds_cdm.observation ob
SET
observation_source_value = ao.observation_source_value
, value_as_string = ao.value_as_string
, observation_concept_id = ao.observation_concept_id
FROM adOccasions ao
WHERE ob.observation_type_concept_id = ao.observation_type_concept_id
WHERE ob.qualifier_source_value = ao.qualifier_source_value
AND ob.visit_occurrence_id = ao.visit_occurrence_id
AND ob.qualifier_concept_id = ao.qualifier_concept_id
RETURNING ob.*
)
INSERT INTO cds_cdm.observation
......@@ -208,8 +199,7 @@ INSERT INTO cds_cdm.observation
, observation_date
, observation_datetime
, observation_type_concept_id
, value_as_string
, qualifier_concept_id
, qualifier_source_value
, visit_occurrence_id
, observation_source_value
, fhir_logical_id
......@@ -221,8 +211,7 @@ SELECT
, observation_date
, observation_datetime
, observation_type_concept_id
, value_as_string
, qualifier_concept_id
, qualifier_source_value
, visit_occurrence_id
, observation_source_value
, fhir_logical_id
......@@ -233,9 +222,8 @@ WHERE NOT EXISTS
SELECT
1
FROM upsert
WHERE upsert.observation_type_concept_id = adOccasions.observation_type_concept_id
WHERE upsert.qualifier_source_value = adOccasions.qualifier_source_value
AND upsert.visit_occurrence_id = adOccasions.visit_occurrence_id
AND upsert.qualifier_concept_id = adOccasions.qualifier_concept_id
)
;
get diagnostics v_rowcount = row_count;
......@@ -254,22 +242,31 @@ BEGIN
omop_id AS pp_person_id
, To_date(data_one, 'YYYY-MM-DD') AS observation_date
, To_timestamp(data_one, 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime
, 32823 AS observation_type_concept_id
, data_two AS value_as_string
, 32817 AS observation_type_concept_id
, data_two AS observation_source_value
, SUBSTRING(data_two, 1, 2) AS dis_reason_code
, 'Entlassungsgrund' AS qualifier_source_value
, SUBSTRING(data_two, 1, 2) AS dis_reason_code_1_2
, SUBSTRING(data_two, 3, 1) AS value_as_string
, fhir_logical_id AS encounter_id
, fhir_identifier AS encounter_identifier
FROM cds_etl_helper.post_process_map
WHERE omop_table = 'discharge_reason'
)
, disReasonConcepts AS
, disReasonConceptsFirstSecond AS
(
SELECT
target_concept_id AS observation_concept_id
, source_code
, source_code AS source_code_1_2
FROM cds_cdm.source_to_concept_map
WHERE source_vocabulary_id = 'Dis Reason 1&2 Obs'
WHERE source_vocabulary_id = 'Discharge Reason'
)
, disReasonConceptsThird AS
(
SELECT
target_concept_id AS value_as_concept_id
, source_code AS value_as_string
FROM cds_cdm.source_to_concept_map
WHERE source_vocabulary_id = 'Discharge Reason'
)
, disReasons AS
(
......@@ -278,18 +275,22 @@ BEGIN
, dr.observation_date
, dr.observation_datetime
, dr.observation_type_concept_id
, dr.value_as_string
, dr.qualifier_source_value
, dr.observation_source_value
, dr.encounter_id
, dr.encounter_identifier
, drc.observation_concept_id
, drc.source_code
, drc12.observation_concept_id
, drc12.source_code_1_2
, drc3.value_as_concept_id
, drc3.value_as_string
, vo.visit_occurrence_id
, vo.fhir_logical_id
, vo.fhir_identifier
FROM disReasonCodes dr
INNER JOIN disReasonConcepts drc
ON dr.dis_reason_code = drc.source_code
INNER JOIN disReasonConceptsFirstSecond drc12
ON dr.dis_reason_code_1_2 = drc12.source_code_1_2
LEFT JOIN disReasonConceptsThird drc3
ON dr.value_as_string = drc3.value_as_string
INNER JOIN cds_cdm.visit_occurrence vo
ON dr.encounter_id = vo.fhir_logical_id
)
......@@ -299,15 +300,17 @@ BEGIN
cds_cdm.observation ob
SET
observation_source_value = dr.observation_source_value
, value_as_string = dr.value_as_string
, observation_concept_id = dr.observation_concept_id
, value_as_string = dr.value_as_string
, value_as_concept_id = dr.value_as_concept_id
, observation_date = dr.observation_date
, observation_datetime = dr.observation_datetime
FROM
disReasons dr
WHERE
ob.observation_type_concept_id = dr.observation_type_concept_id
AND ob.visit_occurrence_id = dr.visit_occurrence_id RETURNING ob.*
ob.qualifier_source_value = dr.qualifier_source_value
AND ob.visit_occurrence_id = dr.visit_occurrence_id
RETURNING ob.*
)
INSERT INTO cds_cdm.observation
(
......@@ -316,7 +319,9 @@ INSERT INTO cds_cdm.observation
, observation_date
, observation_datetime
, observation_type_concept_id
, qualifier_source_value
, value_as_string
, value_as_concept_id
, visit_occurrence_id
, observation_source_value
, fhir_logical_id
......@@ -328,7 +333,9 @@ SELECT
, observation_date
, observation_datetime
, observation_type_concept_id
, qualifier_source_value
, value_as_string
, value_as_concept_id
, visit_occurrence_id
, observation_source_value
, fhir_logical_id
......@@ -339,7 +346,7 @@ WHERE NOT EXISTS
SELECT
1
FROM upsert
WHERE upsert.observation_type_concept_id = disReasons.observation_type_concept_id
WHERE upsert.qualifier_source_value = disReasons.qualifier_source_value
AND upsert.visit_occurrence_id = disReasons.visit_occurrence_id
)
;
......@@ -348,4 +355,5 @@ WHERE NOT EXISTS
END;
$$;
```
\ No newline at end of file