> 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. # Find Problems with Statuses in Coded Entries ## Synopsis This query shows how to find all SNOMED CT encoded entries that have status information. ## Processing Requirements For this query, Continuity of Care Documents were processed. See processing requirements [Processing Requirements](/nlp/database/cookbook/advanced-queries/ccd/introduction#processing-requirements). ## Clinical Context In this case study, we noticed that coded-entries in the CCDs utilize various coding systems such as SNOMED CT, ICD-10, ICD-9 and other special-purpose ontologies. To explore the observations encoded, we will query for coded-entries mapped to `SNOMED CT` for those coded problems for which status is also coded. ## SQL query #### Snowflake ```sql WITH coded_data AS ( SELECT document_id as docid, regexp_replace(d.filename, '.*/', '') as filename, PARSE_JSON(dm.value):label::string as label, PARSE_JSON(dm.value):parent_reference::string as parent_ref, PARSE_JSON(dm.value):section::string as section_ref, PARSE_JSON(dm.value):display_name::string as display_name, PARSE_JSON(dm.value):text_id::string as text_id, PARSE_JSON(dm.value):text::string as sentence, PARSE_JSON(dm.value) as json FROM document d JOIN documentstructuredmetadata dm ON d.id = dm.document_id ), problem_entries as ( SELECT prob_cd.docid, prob_cd.filename, section.display_name as section, prob_cd.label, prob_cd.parent_ref, prob_cd.text_id, prob_cd.display_name, prob_cd.sentence FROM coded_data prob_cd LEFT JOIN LATERAL (SELECT sec_cd.display_name FROM coded_data sec_cd WHERE prob_cd.section_ref = sec_cd.label AND sec_cd.json:for_elt_tag::string = 'section' AND --> limit to Problem List & History of Past Illness sections ( sec_cd.display_name ILIKE '%problem%' OR sec_cd.display_name ILIKE '%illness%' ) ) as section WHERE prob_cd.json:code_elt_tag::string = 'code' AND prob_cd.json:for_elt_tag::string = 'observation' AND --> limit the observation types to the following ( prob_cd.display_name ILIKE '%Problem%' OR prob_cd.display_name ILIKE '%Complaint%' OR prob_cd.display_name ILIKE '%Diagnosis%' OR prob_cd.display_name ILIKE '%Condition%') ), snomed_concept_entries as ( SELECT DISTINCT concept_cd.parent_ref as parent_ref, concept_cd.display_name as display_name, concept_cd.json:code::string as code, concept_cd.json:code_system::string as code_system, concept_cd.json:code_system_name::string as code_system_name FROM coded_data concept_cd WHERE concept_cd.json:code_elt_tag::string IN ('value') AND concept_cd.json:code_system::string IN ('2.16.840.1.113883.6.96', '2.16.840.1.113883.6.5') ), icd10_concept_entries as ( SELECT DISTINCT concept_cd.parent_ref as parent_ref, concept_cd.display_name as display_name, concept_cd.json:code::string as code, concept_cd.json:code_system::string as code_system, concept_cd.json:code_system_name::string as code_system_name FROM coded_data concept_cd WHERE concept_cd.json:code_elt_tag::string IN ('value', 'translation') AND concept_cd.json:code_system::string = '2.16.840.1.113883.6.90' ), status_value_entries as ( SELECT status_cd.parent_ref, status_cd.display_name as status_label, status_value.display_name as value FROM coded_data status_cd JOIN LATERAL (SELECT val_cd.display_name FROM coded_data val_cd WHERE status_cd.label = val_cd.parent_ref AND val_cd.json:code_elt_tag::string = 'value') as status_value WHERE status_cd.json:code_elt_tag::string = 'code' AND status_cd.display_name ILIKE 'status' ) SELECT pe.docid,pe.filename,pe.section AS section_display_name, pe.text_id, ce.display_name as display_name, ce.code_system_name as snomed_system_name, ce.code_system as snomed_code_system, ce.code as snomed_code, ice.code_system_name as icd10_system_name, ice.code_system as icd10_code_system, ice.code as icd10_code, pe.display_name as obs_type, sve.value as status_text, pe.sentence FROM problem_entries pe LEFT JOIN status_value_entries sve ON pe.label = sve.parent_ref --> also return those entries which have no coded status JOIN snomed_concept_entries ce ON pe.label = ce.parent_ref LEFT JOIN icd10_concept_entries ice ON ce.parent_ref = ice.parent_ref ORDER BY pe.filename, pe.section, pe.text_id; ``` | docid | concept_name | status | link_name | ontology | concept_id | text | | ----- | ------------------------------- | ------------------------------- | --------- | --------- | ---------- | ---------------------------------------------------- | | 139 | Problem | Itchy eyes and nasal congestion | Problem | SNOMED CT | 55607006 | Itchy eyes and nasal congestion \| 02/04/1970 \| Act | | 139 | Itchy eyes and nasal congestion | Itchy eyes and nasal congestion | Problem | SNOMED CT | NI | Itchy eyes and nasal congestion \| 02/04/1970 \| Act | | 139 | Problem | Skin Rashes/Hives | Problem | SNOMED CT | 55607006 | Skin Rashes/Hives \| 03/07/2002 \| Active | | 139 | Skin Rashes/Hives | Skin Rashes/Hives | Problem | SNOMED CT | 64144002 | Skin Rashes/Hives \| 03/07/2002 \| Active | | 139 | Problem | Nausea/Vomiting/Diarrhea | Problem | SNOMED CT | 55607006 | Nausea/Vomiting/Diarrhea \| 09/05/2007 \| Active | ## Next steps Another variation on of this query is the [Find Hypertension using Hierarchical Concept Search in Coded Entries](/nlp/database/cookbook/advanced-queries/ccd/hypertension-hierarchical). Another type of relationship encoded in coded-entries, are relationships between vaccines and vaccination events (see [Find Vaccines & Dates of Administration](/nlp/database/cookbook/advanced-queries/ccd/vaccination-dates)). ## Related links * [Introduction](/nlp/database/cookbook/advanced-queries/ccd/introduction)