These are running notes on relevant tables for diagnosis mapping. Not yet updated w.r.t. milestone:heron-cedarbluff-update.
IMO Docs
See also: IMO Problem (IT) User Manual.pdf attached to message:
- From: Russ Waitman <rwaitman@…>
Subject: Fwd: IMO documentation....
Date: Wed, 26 Sep 2012 13:10:35 +0000 (09/26/2012 08:10:35 AM)
Older Notes
Our first alpha source is the PAT_ENC_DX table where we are pulling in the ICD9_CODE column (epic_i2b2_transform.sql starting at line 175 and ticket:176). We plan to revisit (#273) and bring in the DX_ID column as well or do more thinking as that's more of the internal Epic cross walk key:
Here are tables that contain DX_ID or mentions EDGwith some notes "TABLE_NAME" "COLUMN_NAME" Notes
- "AP_CLAIM_DX" "DX_ID" not relevant
- "AP_PROC_ASSOC_DX" "DX_ID" nothing in it
- "CASE_DX" "DX_ID" nothing in it
- "CHG_REVIEW_DX" "DX_ID" nothing in it
- "CLARITY_EDG" "DX_ID" good stuff
- "CL_EDG_OT" "DX_ID" stuff Appears to keep track of when concepts were loaded in from IMO.
- "CL_SSI_DX" "DX_ID" stuff SSI_ID is the primary key and related to smart sets
- "EDG_CODE_MAPPING" "DX_ID" nothing
- "EDG_MPI_ID" "DX_ID" nothing
- "EDG_MAP" stuff but referencing the CID community ID
- "EDG_SYNONYMS" "DX_ID" small stuff only 189 records and fewer synonyms
- "HPF_ADMIT_DIAG" "DX_ID" nothing
- "HSP_ACCT_DX_LIST" "DX_ID" nothing
- "HSP_ADMIT_DIAG" "DX_ID" good stuff seems to be another source specifically of admission diagnoses
- "HSP_DISCH_DIAG" "DX_ID" nothing
- "HSP_TX_DIAG" "DX_ID" nothing
- "MEDICAL_HX" "DX_ID" good stuff medical history contact
- "ORDER_DX_MED" "DX_ID" ok stuff diagnoses associated with a subset of medication orders that may require a diagnoses
- "ORDER_DX_PROC" "DX_ID" ok stuff diagnoses associated with a subset of procedure orders that may require a diagnoses
- "OR_CASE_DX_CODE" "DX_ID" nothing
- "PAT_ENC_DX" "DX_ID" good stuff what we imported initially
- "PEND_ACTION" "DX_ID" every DX_ID is null.
- "PROBLEM_CONCEPT" "DX_ID" nothing
- "PROBLEM_LIST" "DX_ID" good stuff
- "REFERRAL_DX" "DX_ID" stuff this may be good info on referrals.
- "RESIDENT_SUMMARY" "DX_ID" nothing
- "SMARTSET_DX" "DX_ID" stuff seems to be the intersection of smart sets and diagnoses
- "TX_DIAG" "DX_ID" nothing
- "XV_DIABETIC_PAT_ENC_DX" "DX_ID" ???
- "XV_DIABETIC_PROBLEM_LIST" "DX_ID" ???
The thing we would like to figure out is how Epic and IMO interact to provide greater detail on diagnoses: supposedly IMO is supplementing basic ICD9 coding with SNOMED concepts. If you look at the PAT_ENC_DX many of the ICD9_CODE fields have codes with a character supplementing the ICD9. What does this mean? Where is that extra codification described in the database?
Here are some examples:
- DX_ID 163431 is ICD9 code 784.0AD which is some specialization of a "headache"
- DX_ID 163436 is 625.9H, a specialization of "unspecified symptom associated with female genital organs"
- DX_ID 5385 is 462, "acute pharyngitis" or sore throat
Answer: In CLARITY_EDG, we can see these subspecializations and they map to DX_IMO_ID and DX_ICD9_IMO_IDs but I don't see a snomed code anywhere.
For example, the first DX_ID 163431 maps to "PAIN FACE" as opposed to just headache. 163436 is "PAIN IN PELVIS". For both, the DX_IMO_ID and DX_ICD9_IMO_IDs are identical. There is also a DX_GROUP which may be a useful hierarchy
Basic statistics from CLARITY_EDG:
- "COUNT(DISTINCTDX_ID)" 240500
- "COUNT(DISTINCTPARENT_DX_ID)" 2120
- "COUNT(DISTINCTDX_GROUP)" 64
- "COUNT(DISTINCTICD9_CODE)" 18126
- "COUNT(DISTINCTDX_ICD9_IMO_ID)" 238516
- "COUNT(DISTINCTDX_IMO_ID)" 238516
- "COUNT(DISTINCTDIAGNOSIS_CODE)" 18130