|
|
|
:information_source: Information about the admission occasion, admission reason and discharge reason are each written to OBSERVATION table as a new record.
|
|
|
|
|
|
|
|
:information_source: The original data is written in the `cds_etl_helper.POST_PROCESS_MAP` table, which was read from Encounter FHIR resource.
|
|
|
|
|
|
|
|
## Example: Discharge reason
|
|
|
|
### POST_PROCESS_MAP to OBSERVATION
|
|
|
|
| Source | Target | Comments |
|
|
|
|
| :---------------- | :---------------------- | :------------------------------------------------- |
|
|
|
|
||observation_id|This id is generated automatically.|
|
|
|
|
|omop_id|person_id|This id represents the person_id of the person table.|
|
|
|
|
| |observation_concept_id| The observation_source_value is used to search in source_to_concept_map table for the corresponding concept_id.|
|
|
|
|
| data_one |observation_date| |
|
|
|
|
| data_one |observation_datetime| |
|
|
|
|
| |observation_type_concept_id| default value: 44813951 (Discharge details)|
|
|
|
|
| data_two |value_as_string| |
|
|
|
|
| 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 |
|
|
|
|
| data_two |observation_source_value| |
|
|
|
|
|
|
|
|
### Input POST_PROCESS_MAP
|
|
|
|
| id| type | data_one | data_two | omop_id | omop_table |
|
|
|
|
| ---- | ---- | ---- | ---- |---- | ---- |
|
|
|
|
| 1 | ENCOUNTER| 2020-12-05 07:45:00;2 | 14 |1|discharge_reason|
|
|
|
|
|
|
|
|
### 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|
|
|
|
|
| ---- | ---- | ---- | ---- |---- | ---- | ---- | ---- | ---- | ---- |
|
|
|
|
| 1 | 1 | 4203130 | 2020-12-05 | 2020-12-05 07:45:00 | 44813951 | 14 | 2 | | 14 |
|
|
|
|
|
|
|
|
:information_source: The default observation_type_concept_id for admission reason is: 44810203 (Reason for admission) and for admission occasion: 44810199 (Admission details).
|
|
|
|
|
|
|
|
## SQL Script
|
|
|
|
```sql
|
|
|
|
-- upsert in observation for admission reason
|
|
|
|
DO
|
|
|
|
$$
|
|
|
|
DECLARE v_rowcount INT;
|
|
|
|
BEGIN
|
|
|
|
WITH adReasons AS (WITH adReasonCodes AS
|
|
|
|
(
|
|
|
|
SELECT DISTINCT omop_id AS person_id,
|
|
|
|
To_date(Split_part(data_one, ';', 1), 'YYYY-MM-DD') AS observation_date,
|
|
|
|
To_timestamp(Split_part(data_one, ';', 1), 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime,
|
|
|
|
44810203 AS observation_type_concept_id,
|
|
|
|
data_two AS value_as_string,
|
|
|
|
Split_part(data_one, ';', 2)::integer AS visit_occurrence_id,
|
|
|
|
data_two AS observation_source_value
|
|
|
|
FROM cds_etl_helper.post_process_map
|
|
|
|
WHERE omop_table = 'admission_reason'), adReasonConcepts AS
|
|
|
|
(
|
|
|
|
SELECT target_concept_id AS observation_concept_id,
|
|
|
|
source_code
|
|
|
|
FROM source_to_concept_map
|
|
|
|
WHERE source_vocabulary_id = 'Admission Reason 1&2')
|
|
|
|
SELECT *
|
|
|
|
FROM adReasonCodes ar
|
|
|
|
LEFT JOIN adReasonConcepts arc
|
|
|
|
ON ar.observation_source_value = arc.source_code),
|
|
|
|
upsert AS
|
|
|
|
(
|
|
|
|
update 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
|
|
|
|
AND ob.visit_occurrence_id = ar.visit_occurrence_id returning ob.* )
|
|
|
|
INSERT INTO observation
|
|
|
|
(
|
|
|
|
person_id,
|
|
|
|
observation_concept_id,
|
|
|
|
observation_date,
|
|
|
|
observation_datetime,
|
|
|
|
observation_type_concept_id,
|
|
|
|
value_as_string,
|
|
|
|
visit_occurrence_id,
|
|
|
|
observation_source_value
|
|
|
|
)
|
|
|
|
SELECT person_id,
|
|
|
|
observation_concept_id,
|
|
|
|
observation_date,
|
|
|
|
observation_datetime,
|
|
|
|
observation_type_concept_id,
|
|
|
|
value_as_string,
|
|
|
|
visit_occurrence_id,
|
|
|
|
observation_source_value
|
|
|
|
FROM adReasons
|
|
|
|
WHERE NOT EXISTS
|
|
|
|
(
|
|
|
|
SELECT 1
|
|
|
|
FROM upsert
|
|
|
|
WHERE upsert.observation_type_concept_id = adreasons.observation_type_concept_id
|
|
|
|
AND upsert.visit_occurrence_id = adreasons.visit_occurrence_id);
|
|
|
|
get diagnostics v_rowcount = row_count;
|
|
|
|
raise notice 'Upserted % rows in observation.',v_rowcount;
|
|
|
|
END;
|
|
|
|
$$;
|
|
|
|
|
|
|
|
|
|
|
|
-- upsert in observation for admission occasion
|
|
|
|
DO
|
|
|
|
$$
|
|
|
|
DECLARE v_rowcount INT;
|
|
|
|
BEGIN
|
|
|
|
WITH adOccasions AS (WITH adOccasionCodes AS
|
|
|
|
(
|
|
|
|
SELECT DISTINCT omop_id AS person_id,
|
|
|
|
To_date(Split_part(data_one, ';', 1), 'YYYY-MM-DD') AS observation_date,
|
|
|
|
To_timestamp(Split_part(data_one, ';', 1), 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime,
|
|
|
|
44810199 AS observation_type_concept_id,
|
|
|
|
data_two AS value_as_string,
|
|
|
|
Split_part(data_one, ';', 2)::integer AS visit_occurrence_id,
|
|
|
|
data_two AS observation_source_value
|
|
|
|
FROM cds_etl_helper.post_process_map
|
|
|
|
WHERE omop_table = 'admission_occasion'), adOccasionConcepts AS
|
|
|
|
(
|
|
|
|
SELECT target_concept_id AS observation_concept_id,
|
|
|
|
source_code
|
|
|
|
FROM source_to_concept_map
|
|
|
|
WHERE source_vocabulary_id = 'Ad Occasion Obs')
|
|
|
|
SELECT *
|
|
|
|
FROM adOccasionCodes ao
|
|
|
|
LEFT JOIN adOccasionConcepts aoc
|
|
|
|
ON ao.observation_source_value = aoc.source_code),
|
|
|
|
upsert AS
|
|
|
|
(
|
|
|
|
update 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
|
|
|
|
AND ob.visit_occurrence_id = ao.visit_occurrence_id returning ob.* )
|
|
|
|
INSERT INTO observation
|
|
|
|
(
|
|
|
|
person_id,
|
|
|
|
observation_concept_id,
|
|
|
|
observation_date,
|
|
|
|
observation_datetime,
|
|
|
|
observation_type_concept_id,
|
|
|
|
value_as_string,
|
|
|
|
visit_occurrence_id,
|
|
|
|
observation_source_value
|
|
|
|
)
|
|
|
|
SELECT person_id,
|
|
|
|
observation_concept_id,
|
|
|
|
observation_date,
|
|
|
|
observation_datetime,
|
|
|
|
observation_type_concept_id,
|
|
|
|
value_as_string,
|
|
|
|
visit_occurrence_id,
|
|
|
|
observation_source_value
|
|
|
|
FROM adOccasions
|
|
|
|
WHERE NOT EXISTS
|
|
|
|
(
|
|
|
|
SELECT 1
|
|
|
|
FROM upsert
|
|
|
|
WHERE upsert.observation_type_concept_id = adOccasions.observation_type_concept_id
|
|
|
|
AND upsert.visit_occurrence_id = adOccasions.visit_occurrence_id);
|
|
|
|
get diagnostics v_rowcount = row_count;
|
|
|
|
raise notice 'Upserted % rows in observation.',v_rowcount;
|
|
|
|
END;
|
|
|
|
$$;
|
|
|
|
|
|
|
|
|
|
|
|
-- upsert in observation for discharge reason
|
|
|
|
DO
|
|
|
|
$$
|
|
|
|
DECLARE v_rowcount INT;
|
|
|
|
BEGIN
|
|
|
|
WITH disReasons AS (WITH disReasonCodes AS
|
|
|
|
(
|
|
|
|
SELECT DISTINCT omop_id AS person_id,
|
|
|
|
To_date(Split_part(data_one, ';', 1), 'YYYY-MM-DD') AS observation_date,
|
|
|
|
To_timestamp(Split_part(data_one, ';', 1), 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime,
|
|
|
|
44813951 AS observation_type_concept_id,
|
|
|
|
data_two AS value_as_string,
|
|
|
|
Split_part(data_one, ';', 2)::integer AS visit_occurrence_id,
|
|
|
|
data_two AS observation_source_value,
|
|
|
|
SUBSTRING(data_two, 1, 2) AS dis_reason_code
|
|
|
|
FROM cds_etl_helper.post_process_map
|
|
|
|
WHERE omop_table = 'discharge_reason'), disReasonConcepts AS
|
|
|
|
(
|
|
|
|
SELECT target_concept_id AS observation_concept_id,
|
|
|
|
source_code
|
|
|
|
FROM source_to_concept_map
|
|
|
|
WHERE source_vocabulary_id = 'Dis Reason 1&2 Obs')
|
|
|
|
SELECT *
|
|
|
|
FROM disReasonCodes dr
|
|
|
|
LEFT JOIN disReasonConcepts drc
|
|
|
|
ON dr.dis_reason_code = drc.source_code),
|
|
|
|
upsert AS
|
|
|
|
(
|
|
|
|
update observation ob
|
|
|
|
SET observation_source_value = dr.observation_source_value,
|
|
|
|
value_as_string = dr.value_as_string,
|
|
|
|
observation_concept_id = dr.observation_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.* )
|
|
|
|
INSERT INTO observation
|
|
|
|
(
|
|
|
|
person_id,
|
|
|
|
observation_concept_id,
|
|
|
|
observation_date,
|
|
|
|
observation_datetime,
|
|
|
|
observation_type_concept_id,
|
|
|
|
value_as_string,
|
|
|
|
visit_occurrence_id,
|
|
|
|
observation_source_value
|
|
|
|
)
|
|
|
|
SELECT person_id,
|
|
|
|
observation_concept_id,
|
|
|
|
observation_date,
|
|
|
|
observation_datetime,
|
|
|
|
observation_type_concept_id,
|
|
|
|
value_as_string,
|
|
|
|
visit_occurrence_id,
|
|
|
|
observation_source_value
|
|
|
|
FROM disReasons
|
|
|
|
WHERE NOT EXISTS
|
|
|
|
(
|
|
|
|
SELECT 1
|
|
|
|
FROM upsert
|
|
|
|
WHERE upsert.observation_type_concept_id = disReasons.observation_type_concept_id
|
|
|
|
AND upsert.visit_occurrence_id = disReasons.visit_occurrence_id);
|
|
|
|
get diagnostics v_rowcount = row_count;
|
|
|
|
raise notice 'Upserted % rows in observation.',v_rowcount;
|
|
|
|
END;
|
|
|
|
$$;
|
|
|
|
``` |
|
|
\ No newline at end of file |
