ICD-10 Concepts Experienced by Patients

-- 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';