Generating Patient Summaries from Clinical Reports

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 AND e.type_='found'-- 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' -- alternative ontology 'umls_nci' (UMLS metathesaurus)
                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
),
summary AS (
    SELECT row_number() OVER ( PARTITION BY c.description ORDER BY DATE(dm.chartdate) DESC ) AS rank,
        DATE(dm.chartdate)                                                                AS chartdate,
        CASE fet.type_name
            WHEN 'Disease or Syndrome' THEN 'Abnormalities / Disorders'
            WHEN 'Acquired Abnormality' THEN 'Abnormalities / Disorders'
            WHEN 'Anatomical Abnormality' THEN 'Abnormalities / Disorders'
            WHEN 'Congenital Abnormality' THEN 'Abnormalities / Disorders' --? Add
            WHEN 'Pathologic Function' THEN 'Abnormalities / Disorders'
            WHEN 'Cell or Molecular Dysfunction' THEN 'Abnormalities / Disorders'
            WHEN 'Neoplastic Process' THEN 'Neoplasia / Cancer'
            WHEN 'Injury or Poisoning' THEN 'Injuries / Trauma'
            WHEN 'Sign or Symptom' THEN 'Findings / Symptoms'
            WHEN 'Finding' THEN 'Findings / Symptoms'
            WHEN 'Mental or Behavioral Dysfunction' THEN 'Mental / Behavioral Disorders'
            WHEN 'Diagnostic Procedure' THEN 'Procedures / Care Activities'
            WHEN 'Laboratory Procedure' THEN 'Procedures / Care Activities'
            WHEN 'Laboratory or Test Result' THEN 'Procedures / Care Activities'
            WHEN 'Therapeutic or Preventive Procedure' THEN 'Procedures / Care Activities'
            WHEN 'Health Care Activity' THEN 'Procedures / Care Activities'
            ELSE 'Unknown'
            END                                                                           AS heading,
        c.description                                                                     AS concept,
        fe.text                                                                          AS term
        -- sl.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 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
            JOIN foundentitytype fet ON fet.found_entity_id = fe.id
        -- Uncomment to access patient/chart data if available in documentmetadata:
            JOIN document d ON d.id = e.document_id
            JOIN documentmetadata dm ON d.id = dm.document_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 sl ON sl.id = l.location_id
    WHERE c.ontology = 'snomed'
    AND fet.ontology = 'umls'
    AND dm.subject_id = '11110003'
    AND fe.polarity = 'asserted'
    AND fe.uncertainty = 'certain'
    AND fet.type_name IN ('Disease or Syndrome', 'Acquired Abnormality', 'Cell or Molecular Dysfunction',
                            'Anatomical Abnormality', 'Pathologic Function', 'Neoplastic Process',
                            'Injury or Poisoning',
                            'Sign or Symptom', 'Finding', 'Mental or Behavioral Dysfunction', 'Diagnostic Procedure',
                            'Laboratory Procedure', 'Therapeutic or Preventive Procedure', 'Laboratory or Test Result',
                            'Congenital Abnormality', 'Health Care Activity')
)
SELECT * FROM summary WHERE rank = 1 -- get latest mention of any concept
ORDER BY summary.heading, summary.chartdate DESC;