Skip to content

Changes

Page history
update: update wiki pages authored by Peng, Yuan's avatar Peng, Yuan
Hide whitespace changes
Inline Side-by-side
Admission-occasion,-admission-reason-and-discharge-reason.md
View page @ 95dfa1e7
...@@ -11,7 +11,7 @@ ...@@ -11,7 +11,7 @@
| |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.|
| data_one |observation_date| :heavy_check_mark: | | | data_one |observation_date| :heavy_check_mark: | |
| data_one |observation_datetime| | | | data_one |observation_datetime| | |
| |observation_type_concept_id| :heavy_check_mark: | default value: 44813951 (Discharge details)| | |observation_type_concept_id| :heavy_check_mark: | default value: 32823 (EHR discharge reason)|
| data_two |value_as_string| | | | data_two |value_as_string| | |
| 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 |
...@@ -22,14 +22,16 @@ ...@@ -22,14 +22,16 @@
### Input POST_PROCESS_MAP ### Input POST_PROCESS_MAP
| id| type | data_one | data_two | omop_id | omop_table | fhir_logical_id | fhir_identifier | | 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| cid.001 | cid.001 | | 1 | ENCOUNTER| 2020-12-05 07:45:00;2 | 14 |1|discharge_reason| enc-cid.001 | enc-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|fhir_logical_id|fhir_identifier| |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 | cid.001 | cid.001 | | 1 | 1 | 4203130 | 2020-12-05 | 2020-12-05 07:45:00 | 32823 | 14 | 2 | | 14 | enc-cid.001 | enc-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: 32819 (EHR admission note) and for admission occasion: 32817 (EHR).
:information_source: The default qualifier_concept_id for admission reason and admission occasion is: 44803020 (Primary reason for admission).
## SQL Script ## SQL Script
```sql ```sql
...@@ -44,8 +46,9 @@ WITH adReasonCodes AS ...@@ -44,8 +46,9 @@ WITH adReasonCodes AS
omop_id AS pp_person_id omop_id AS pp_person_id
, To_date(data_one, 'YYYY-MM-DD') AS observation_date , To_date(data_one, 'YYYY-MM-DD') AS observation_date
, To_timestamp(data_one, 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime , To_timestamp(data_one, 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime
, 44810203 AS observation_type_concept_id , 32819 AS observation_type_concept_id
, data_two AS value_as_string , data_two AS value_as_string
, 44803020 AS qualifier_concept_id
, data_two AS observation_source_value , data_two AS observation_source_value
, fhir_logical_id AS encounter_id , fhir_logical_id AS encounter_id
, fhir_identifier AS encounter_identifier , fhir_identifier AS encounter_identifier
...@@ -68,6 +71,7 @@ WITH adReasonCodes AS ...@@ -68,6 +71,7 @@ WITH adReasonCodes AS
, ar.observation_datetime , ar.observation_datetime
, ar.observation_type_concept_id , ar.observation_type_concept_id
, ar.value_as_string , ar.value_as_string
, ar.qualifier_concept_id
, ar.observation_source_value , ar.observation_source_value
, arc.observation_concept_id , arc.observation_concept_id
, vo.visit_occurrence_id , vo.visit_occurrence_id
...@@ -88,6 +92,7 @@ WITH adReasonCodes AS ...@@ -88,6 +92,7 @@ WITH adReasonCodes AS
FROM adReasons ar FROM adReasons ar
WHERE ob.observation_type_concept_id = ar.observation_type_concept_id WHERE ob.observation_type_concept_id = ar.observation_type_concept_id
AND ob.visit_occurrence_id = ar.visit_occurrence_id AND ob.visit_occurrence_id = ar.visit_occurrence_id
AND ob.qualifier_concept_id = ar.qualifier_concept_id
RETURNING ob.* RETURNING ob.*
) )
INSERT INTO cds_cdm.observation INSERT INTO cds_cdm.observation
...@@ -98,6 +103,7 @@ INSERT INTO cds_cdm.observation ...@@ -98,6 +103,7 @@ INSERT INTO cds_cdm.observation
, observation_datetime , observation_datetime
, observation_type_concept_id , observation_type_concept_id
, value_as_string , value_as_string
, qualifier_concept_id
, visit_occurrence_id , visit_occurrence_id
, observation_source_value , observation_source_value
, fhir_logical_id , fhir_logical_id
...@@ -110,6 +116,7 @@ SELECT ...@@ -110,6 +116,7 @@ SELECT
, observation_datetime , observation_datetime
, observation_type_concept_id , observation_type_concept_id
, value_as_string , value_as_string
, qualifier_concept_id
, visit_occurrence_id , visit_occurrence_id
, observation_source_value , observation_source_value
, fhir_logical_id , fhir_logical_id
...@@ -122,6 +129,7 @@ WHERE NOT EXISTS ...@@ -122,6 +129,7 @@ WHERE NOT EXISTS
FROM upsert FROM upsert
WHERE upsert.observation_type_concept_id = adReasons.observation_type_concept_id WHERE upsert.observation_type_concept_id = adReasons.observation_type_concept_id
AND upsert.visit_occurrence_id = adReasons.visit_occurrence_id AND upsert.visit_occurrence_id = adReasons.visit_occurrence_id
AND upsert.qualifier_concept_id = adReasons.qualifier_concept_id
) )
; ;
get diagnostics v_rowcount = row_count; get diagnostics v_rowcount = row_count;
...@@ -140,8 +148,9 @@ WITH adOccasionCodes AS ...@@ -140,8 +148,9 @@ WITH adOccasionCodes AS
omop_id AS pp_person_id omop_id AS pp_person_id
, To_date(data_one, 'YYYY-MM-DD') AS observation_date , To_date(data_one, 'YYYY-MM-DD') AS observation_date
, To_timestamp(data_one, 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime , To_timestamp(data_one, 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime
, 44810199 AS observation_type_concept_id , 32817 AS observation_type_concept_id
, data_two AS value_as_string , data_two AS value_as_string
, 44803020 AS qualifier_concept_id
, data_two AS observation_source_value , data_two AS observation_source_value
, fhir_logical_id AS encounter_id , fhir_logical_id AS encounter_id
, fhir_identifier AS encounter_identifier , fhir_identifier AS encounter_identifier
...@@ -164,6 +173,7 @@ WITH adOccasionCodes AS ...@@ -164,6 +173,7 @@ WITH adOccasionCodes AS
, ao.observation_datetime , ao.observation_datetime
, ao.observation_type_concept_id , ao.observation_type_concept_id
, ao.value_as_string , ao.value_as_string
, ao.qualifier_concept_id
, ao.observation_source_value , ao.observation_source_value
, ao.encounter_id , ao.encounter_id
, ao.encounter_identifier , ao.encounter_identifier
...@@ -188,6 +198,7 @@ WITH adOccasionCodes AS ...@@ -188,6 +198,7 @@ WITH adOccasionCodes AS
FROM adOccasions ao FROM adOccasions ao
WHERE ob.observation_type_concept_id = ao.observation_type_concept_id WHERE ob.observation_type_concept_id = ao.observation_type_concept_id
AND ob.visit_occurrence_id = ao.visit_occurrence_id AND ob.visit_occurrence_id = ao.visit_occurrence_id
AND ob.qualifier_concept_id = ao.qualifier_concept_id
RETURNING ob.* RETURNING ob.*
) )
INSERT INTO cds_cdm.observation INSERT INTO cds_cdm.observation
...@@ -198,6 +209,7 @@ INSERT INTO cds_cdm.observation ...@@ -198,6 +209,7 @@ INSERT INTO cds_cdm.observation
, observation_datetime , observation_datetime
, observation_type_concept_id , observation_type_concept_id
, value_as_string , value_as_string
, qualifier_concept_id
, visit_occurrence_id , visit_occurrence_id
, observation_source_value , observation_source_value
, fhir_logical_id , fhir_logical_id
...@@ -210,6 +222,7 @@ SELECT ...@@ -210,6 +222,7 @@ SELECT
, observation_datetime , observation_datetime
, observation_type_concept_id , observation_type_concept_id
, value_as_string , value_as_string
, qualifier_concept_id
, visit_occurrence_id , visit_occurrence_id
, observation_source_value , observation_source_value
, fhir_logical_id , fhir_logical_id
...@@ -222,6 +235,7 @@ WHERE NOT EXISTS ...@@ -222,6 +235,7 @@ WHERE NOT EXISTS
FROM upsert FROM upsert
WHERE upsert.observation_type_concept_id = adOccasions.observation_type_concept_id WHERE upsert.observation_type_concept_id = adOccasions.observation_type_concept_id
AND upsert.visit_occurrence_id = adOccasions.visit_occurrence_id AND upsert.visit_occurrence_id = adOccasions.visit_occurrence_id
AND upsert.qualifier_concept_id = adOccasions.qualifier_concept_id
) )
; ;
get diagnostics v_rowcount = row_count; get diagnostics v_rowcount = row_count;
...@@ -240,7 +254,7 @@ BEGIN ...@@ -240,7 +254,7 @@ BEGIN
omop_id AS pp_person_id omop_id AS pp_person_id
, To_date(data_one, 'YYYY-MM-DD') AS observation_date , To_date(data_one, 'YYYY-MM-DD') AS observation_date
, To_timestamp(data_one, 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime , To_timestamp(data_one, 'YYYY-MM-DD HH24:MI:SS') AS observation_datetime
, 44813951 AS observation_type_concept_id , 32823 AS observation_type_concept_id
, data_two AS value_as_string , data_two AS value_as_string
, 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
......