> For clean Markdown of any page, append .md to the page URL. > For a complete documentation index, see https://docs.emtelligent.com/llms.txt. > For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.emtelligent.com/_mcp/server. # ICD-10 Concepts Experienced by Patients ## Synopsis This query finds ICD-10 mapped entities and returns the ICD-10 code and corresponding Snomed description, that are experienced by patients. ## Processing Requirements This query assumes that at least the following `emtellipro-db-client` processing options were enabled: * `--feature experiencer-relation, snomed-ontology, snomedicd10-ontology` * `--store-sections-and-sentences` For this query, Clinical discharge summaries were processed with the following document type: * `--category Clinical` * `--subcategory Discharge Summary` ## Clinical Context This query is similar to the ICD-10 query earlier in this cookbook except that it uses a CTE to identify cases where the experiencer of the ICD-10 concept is either implicitly or explicitly labeled as being the patient rather than a family member or other person. This query can be modified to find mentions of the concepts of clinical concern who apply to other persons, for example a `72705000|Mother (person)` or `65616008|Son (person)` if desired. ## SQL query #### SQL ```sql -- Find ICD10 Code and corresponding SNOMED CT fully-specified concept name, affecting the patient WITH explicitpatients_ers AS (SELECT er.id AS expid FROM experiencerrelation er JOIN entity e ON er.experiencer_id = e.id -- NOTE: find experience(r) entities JOIN foundentity fe ON fe.id = e.entity_id -- IMPORTANT: e.entity_id not e.id JOIN foundentityconcept fec ON fe.id = fec.found_entity_id JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology WHERE c.ontology = 'snomed' AND e.type_ = 'found' -- IMPORTANT: this condition is required. AND c.concept_id = '116154003'), -- SNOMED CT concept id for patient implicitpatients_ers AS (SELECT er.id AS expid FROM entity e JOIN experiencerrelation er ON er.experiencer_id = e.id -- NOTE: experience(r) entities JOIN assumedentity ae ON ae.id = e.entity_id WHERE e.type_ = 'assumed' AND ae.value = 'patient'), patient_ers AS ( -- experiencer relations where subject is patient SELECT expid FROM implicitpatients_ers UNION SELECT expid FROM explicitpatients_ers ), icd10_mapped_entities AS ( SELECT fe.id, c2.concept_id, c2.description as icd10_description, fe.polarity, fe.uncertainty, fe.text FROM foundentity fe JOIN foundentityconcept fec on fe.id = fec.found_entity_id JOIN concept c2 on c2.concept_id = fec.concept_id and c2.ontology = fec.concept_ontology AND c2.ontology = 'snomed_icd10'), snomed_mapped_entities AS ( SELECT fe.id, c1.concept_id, c1.description as snomed_description, fe.polarity, fe.uncertainty, fe.text FROM foundentity fe JOIN foundentityconcept fec on fe.id = fec.found_entity_id JOIN concept c1 on c1.concept_id = fec.concept_id and c1.ontology = fec.concept_ontology AND c1.ontology = 'snomed'), mapped_entities AS ( SELECT icd10_mapped_entities.id as id, icd10_mapped_entities.concept_id as icd10, icd10_description, snomed_mapped_entities.concept_id as sctid, snomed_description FROM icd10_mapped_entities JOIN snomed_mapped_entities ON icd10_mapped_entities.id = snomed_mapped_entities.id ) SELECT -- enable if documentmetadata table is populated -- dm.subject_id, -- dm.chartdate, mapped_entities.icd10 as icd10, mapped_entities.icd10_description as icd10_description, mapped_entities.sctid as sctid, mapped_entities.snomed_description as snomed_description, fe.polarity, fe.uncertainty, fe.section_name, fe.text as term, s.text as sentence FROM patient_ers JOIN experiencerrelation er ON patient_ers.expid = er.id JOIN entity e ON er.experienced_id = e.id -- NOTE: experience(d) entities JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found' JOIN mapped_entities ON fe.id = mapped_entities.id JOIN foundentitylocation fel on fe.id = fel.found_entity_id JOIN location l on fel.location_id = l.id AND l.type_ = 'sentence' JOIN sentencelocation s on l.location_id = s.id AND l.type_ = 'sentence' JOIN document d on l.document_id = d.id JOIN documentmetadata dm on d.id = dm.document_id WHERE fe.polarity = 'asserted' AND fe.uncertainty = 'certain'; ``` | icd10 | icd10_description | sctid | snomed_description | polarity | uncertainty | section_name | term | sentence | | ----- | ------------------------------------------------------------------------- | --------- | ------------------------------------------ | -------- | ----------- | --------------------------------- | -------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | A04.7 | Enterocolitis due to Clostridium difficile (Disease or Syndrome) | 423590009 | Clostridium difficile colitis (disorder) | asserted | certain | PROCEDURES AND TREATMENT PROVIDED | c diff colitis | Biopsy was consistent with c diff colitis. | | A04.7 | Enterocolitis due to Clostridium difficile (Disease or Syndrome) | 423590009 | Clostridium difficile colitis (disorder) | asserted | certain | PROCEDURES AND TREATMENT PROVIDED | c diff colitis | On the floor he continued to improve clinically with respect to his c diff colitis. | | A04.7 | Enterocolitis due to Clostridium difficile (Disease or Syndrome) | 423590009 | Clostridium difficile colitis (disorder) | asserted | certain | DISCHARGE DIAGNOSIS | C diff colitis | C diff colitis | | A09.9 | Infectious gastroenteritis and colitis, unspecified (Disease or Syndrome) | 64226004 | Colitis (disorder) | asserted | certain | PROCEDURES AND TREATMENT PROVIDED | colitis | He was seen by physiotherapy who felt he was still too weak to be discharged home despite the fact he was medically stable and had completed his treatment for his colitis. | | A09.9 | Infectious gastroenteritis and colitis, unspecified (Disease or Syndrome) | 64613007 | Inflammation of small intestine (disorder) | asserted | certain | FINDINGS | enteritis | Several fluid-filled loops of jejunum are present compatible with mild enteritis. | | A09.9 | Infectious gastroenteritis and colitis, unspecified (Disease or Syndrome) | 62315008 | Diarrhea (finding) | asserted | certain | DISCHARGE DIAGNOSIS | Diarrhea | Diarrhea | | A09.9 | Infectious gastroenteritis and colitis, unspecified (Disease or Syndrome) | 64226004 | Colitis (disorder) | asserted | certain | DIAGNOSIS | colitis | Colon biopsy (rectum): - Mild active colitis and persistent intestinal spirochetosis. | | A09.9 | Infectious gastroenteritis and colitis, unspecified (Disease or Syndrome) | 62315008 | Diarrhea (finding) | asserted | certain | HISTORY OF PRESENT ILLNESS | diarrhea | As a result of the diarrhea, he is fearful of eating and therefore PO intake is markedly reduced. | | A09.9 | Infectious gastroenteritis and colitis, unspecified (Disease or Syndrome) | 62315008 | Diarrhea (finding) | asserted | certain | PROCEDURES AND TREATMENT PROVIDED | diarrhea | The patient started to show signs of improvement with decreasing white blood cell count, decreasing diarrhea and was able to wean off pressors. | | A09.9 | Infectious gastroenteritis and colitis, unspecified (Disease or Syndrome) | 52457000 | Ileitis (disorder) | asserted | certain | DIAGNOSIS | ileitis | Terminal ileum biopsy: - Mild active ileitis. | ## Related links * [Experiencer Relations](/nlp/database/schema/relations/experiencer) * [Entity-level Tables](/nlp/database/schema/core/entity-tables)