> 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 Smoking Status from Text Elements

## Synopsis

The following sample query tries to determine if the patient is a smoker or not from the data extracted from CCD Documents. This query uses entities extracted from the narrative blocks (i.e. textual elements) within report sections.

## Introduction

This query is one of the more complicated queries in the cookbook. A number of factors combine to make this query more challenging and interesting:

1. The inherent difficulty of labelling and classifying smoking behaviour in clinically-relevant ways because of the nuances and variation in how smoking behaviour can be documented.
2. The challenges posed by the structure of the source report and its usage.

### Case Study Background

This data extraction task involved processing CCD documents. CCD files were ingested by the Output Database Client using the CCD plugin, and structured output was uploaded to the Output Database.

Clinical Care Documents (CCD) are XML-based documents that summarize patient-specific clinical information. The contents in CCDs can vary depending on vendor-specific implementation, but generally complies with the document format defined by the HL7 Consolidated Clinical Data Architecture (c-CDA). CCDs are semi-structured data in that the information contained in them are data tables containing lists of clinical terms, and codes, or free-text elements.

The CCDs that were processed in this case study presented a few challenges for data retrieval and interpretation. These challenges are worth considering as these types of data artifacts may also be encountered in other medical reports:

1. **Presence of default sections**: The document template contains required sections that when not populated have default values. For example, the **Smoking Status** section is always provided but may contain template content like “Unknown if ever smoked”, which is sometimes in conflict with indications in other sections where the patient had been explicitly identified as a smoker or non-smoker.
2. **Multiple mentions**: For example, smoking behaviours are mentioned in the **Functional Status** section and the **Smoking status** sections. The **Functional status** section always comes before the smoking section in the document, and the customer has confirmed that the data in the **Functional Status** takes precedence.
3. **Concept of interest is present as semi-structured (HTML-formatted table) vs free-text**: Smoking status is provided in tabular form, where an explicit status value is given in a separate column e.g. `Former Smoker` in one column, and `Status: Active` in another.
4. **Section name itself contains the concept of interest**: The `Smoking Status` section, contains the word Smoking which is a concept of interest for this query.

Hence, our query must account for these considerations, while also implementing a concept-based query strategy that returns useful information about the patient’s current smoking status from a clinician’s point of view. A first step in formulating a concept-based query is to select and organize the Snomed concepts related to smoking behaviours into clinically useful categories. There are also nuances in the way smoking behaviour can be expressed, that will be covered in the Clinical Context section.

## Clinical Context

From a clinical perspective, there are essentially 6 types of smoking situations:

1. A **Smoker** - i.e. someone who currently smokes
2. An **Ex-smoker** - i.e. someone who smoked in the past but says they no longer smoke
3. A **Non-smoker** - someone who says they 'do not smoke'
4. A **Never smoker** - someone who states they have never smoked (note that this is different from someone who says they do not smoke currently, i.e. are a non-smoker)
5. **Unknown** - i.e. we don't know if they ever smoked.
6. A **Passive smoker** - someone who has had exposure to second-hand smoke

Smoking can refer to smoking cigarettes, pipes, tobacco, etc. This detail is, though not often recorded, may sometimes be recorded. Also complicating this is the fact that there are different SNOMED concepts/IDs for `heavy smoker` vs just `smoker`.

We also need to consider the **polarity** here, but really only in the cases where it says someone is a 'smoker'. This is because there could be a sentence like, "There is no evidence that the patient is a smoker". Here, you want the polarity (which should be negated) because the author is saying the patient is not known to have smoked. But in the case of 'ex-smoker', 'non-smoker', 'never-smoker', and 'unknown', because these conditions contain inherent assertions, it is unlikely that polarity is important - because nobody (would) ever write 'The patient is not a non-smoker'. While this cannot be ruled out it is highly unlikely.

With regards to **passive smoking** (i.e. second-hand smoke exposure), we need to query for both cases where the SNOMED substance 'second-hand smoke' is present and has polarity asserted, as well as for the SNOMED finding of 'passive smoker' is asserted. This is because there are often sentences such as:

```text
2nd hand smoke from fiance, past EtOH.
Lives with her daughters, former nurse, widowed,no smoking (but a lot of second hand smoking from husband), no alcohol or drugs
```

## SQL query

The approach to extract smoking status is as follows:

1. Get the fields we're looking for, including the sentence, which is useful for validation, although less so as these reports don't contain normal 'free text'.
2. Search on multiple SNOMED concepts related to smoking (e.g. 'smoker', 'pipe smoker', 'aggressive non-smoker', etc), with an additional WHEN condition to find cases where the polarity of 'smoker' is asserted
3. Use a SQL CASE statement to group these concepts into the 5 categories above and label them using an ordinal value.
4. In a CTE order the output based on the ordinal value for **smoking_status**

Ordering on the **smoking_status** column, is one way to deal with the occurrence where you get both 'unknown if ever smoked' and 'former smoker' in the same report - setting a sort order so that 'unknown if ever smoked' is always at sorted at the end. We can take the output that is sorted before the 'unknown if ever smoked' output as the more trusted output.

The NLP API treats each row in the CCD tables as a sentence. Hence the qualifier value with the same sentence location ID can be safely be attributed to the smoking term with the same sentence location ID

To extract the Status indicator if given for a smoking term, we have to do the following:

1. In status_terms CTE, search for Snomed qualifier concepts **Active** and **Resolved** entities.
2. Also retrieve the location ID for the sentence in which these entities appear
3. By doing a LEFT JOIN on the smoking_terms CTE, ON the sentence location ID. This identifies the qualifier value associated with the smoking term.

The following query shows the final query which uses two CTEs - smoking_terms and status_terms to generate a data table for analysis.

#### PostgreSQL

```sql
WITH smoking_terms AS (
SELECT fe.id       AS foundentity_id,
    fe.text       AS smoking_term,
    c.description  AS smoking_concept,
    fec.concept_id AS snomed_id,
    fe.polarity    AS polarity,
    fe.uncertainty AS uncertainty,
    fe.section_name,
    regexp_replace(d.filename, '.*/', '')     AS filename,
    l.location_id  AS sentence_loc_id,
    sl.text        AS sentence,
    CASE
        WHEN c.concept_id IN
                ('77176002', '365981007', '59978006', '65568007', '82302008', '428041000124106', '230062009',
                '230064005', '230063004') AND fe.polarity = 'asserted' THEN 'Smoker'
        WHEN c.concept_id IN
                ('8392000', '160618006', '87739003', '360929005', '105541001', '105539002', '360918006',
                '105540000') THEN 'Non-smoker'
        WHEN c.concept_id IN ('8517006', '160620009', '160621008', '281018007', '735128000', '48031000119106')
            THEN 'Ex-smoker'
        WHEN c.concept_id IN ('266919005') THEN 'Never smoked'
        WHEN c.concept_id IN ('266927001') THEN 'Unknown'
        WHEN c.concept_id IN ('43381005', '102409004') AND fe.polarity = 'asserted' THEN 'Passive smoker'
        ELSE 'Undefined'
    END        AS smoking_status,
    CASE
        WHEN c.concept_id IN
                ('77176002', '365981007', '59978006', '65568007', '82302008', '428041000124106', '230062009',
                '230064005', '230063004') THEN 1
        WHEN c.concept_id IN
                ('8392000', '160618006', '87739003', '360929005', '105541001', '105539002', '360918006',
                '105540000') THEN 3
        WHEN c.concept_id IN ('8517006', '160620009', '160621008', '281018007', '735128000', '48031000119106')
            THEN 2
        WHEN c.concept_id IN ('266919005') THEN 4
        WHEN c.concept_id IN ('266927001') THEN 5
        WHEN c.concept_id IN ('43381005', '102409004') THEN 6
        ELSE 7
    END AS smoking_ordinal -- Sortable value to find final smoking status when there are multiple mentions
FROM foundentity fe
        JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
        JOIN concept c ON fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology AND c.ontology='snomed'
        JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
        JOIN location l
            ON fel.location_id = l.id AND (l.type_ = 'sentence') -- IMPORTANT:  location id type must be ‘sentence’
        JOIN sentencelocation sl
            ON l.location_id = sl.id
        JOIN document d ON d.id = fe.document_id
WHERE sl.text NOT LIKE 'Smoking Status%'
-- limit to C-CDA documents: only those have structured metadata
AND EXISTS (SELECT 1 FROM documentstructuredmetadata m WHERE m.document_id = d.id)
AND c.concept_id IN
    ('77176002', '77176002', '365981007', '59978006', '65568007', '82302008', '428041000124106', '230062009',
    '230064005', '230063004',
    '8392000', '160618006', '87739003', '360929005', '105541001', '105539002', '360918006', '105540000',
    '8517006', '160620009', '160621008', '281018007', '735128000', '48031000119106',
    '266919005',
    '266927001',
    '43381005', '102409004')),
status_terms AS (SELECT fe.id          AS foundentity_id,
                        fe.text       AS status_term,
                        c.description  AS status_concept,
                        fec.concept_id AS status_snomed_id,
                        l.location_id  AS sentence_loc_id,
                        sl.text        AS sentence_text,
                        regexp_replace(d.filename, '.*/', '')     AS filename
                FROM foundentity fe
                        JOIN foundentityconcept fec
                                ON fes.found_entity_id = fec.found_entity_id AND fec.concept_ontology = 'snomed'
                        JOIN concept c ON fec.concept_id = c.concept_id AND fec.concept_ontology = c.ontology
                        JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
                        JOIN location l
                                ON fel.location_id = l.id AND (l.type_ = 'sentence') -- IMPORTANT:  location id type must be ‘sentence’
                        JOIN sentencelocation sl
                                ON l.location_id = sl.id
                        JOIN document d ON d.id = l.document_id
                WHERE c.concept_id IN ('55561003', '723506003') -- Snomed ID 'Active (qualifier value)' and 'Resolved (qualifier value)'
                    AND c.ontology = 'snomed'
                    AND sl.text NOT LIKE 'Smoking Status%')
SELECT smoking_terms.foundentity_id             as fe_id,
    smoking_terms.filename,
    smoking_term,
    smoking_concept,
    smoking_status,
    smoking_ordinal,
    status_term,
    smoking_terms.polarity AS term_polarity,
    smoking_terms.uncertainty AS term_uncertainty,
    smoking_terms.section_name,
    REGEXP_REPLACE(sentence, '\n', ' ', 'g') AS sentence
FROM smoking_terms
        LEFT JOIN status_terms ON smoking_terms.sentence_loc_id = status_terms.sentence_loc_id  -- Retrieve the status term in same sentence as smoking
ORDER BY filename, smoking_ordinal, status_term, uncertainty;
```

#### Snowflake

```sql
WITH smoking_terms AS (
SELECT fe.id       AS foundentity_id,
    fe.text       AS smoking_term,
    c.description  AS smoking_concept,
    fec.concept_id AS snomed_id,
    fe.polarity    AS polarity,
    fe.uncertainty AS uncertainty,
    fe.section_name,
    regexp_replace(d.filename, '.*/', '')     AS filename,
    l.location_id  AS sentence_loc_id,
    sl.text        AS sentence,
    CASE
        WHEN c.concept_id IN
                ('77176002', '365981007', '59978006', '65568007', '82302008', '428041000124106', '230062009',
                '230064005', '230063004') AND fe.polarity = 'asserted' THEN 'Smoker'
        WHEN c.concept_id IN
                ('8392000', '160618006', '87739003', '360929005', '105541001', '105539002', '360918006',
                '105540000') THEN 'Non-smoker'
        WHEN c.concept_id IN ('8517006', '160620009', '160621008', '281018007', '735128000', '48031000119106')
            THEN 'Ex-smoker'
        WHEN c.concept_id IN ('266919005') THEN 'Never smoked'
        WHEN c.concept_id IN ('266927001') THEN 'Unknown'
        WHEN c.concept_id IN ('43381005', '102409004') AND fe.polarity = 'asserted' THEN 'Passive smoker'
        ELSE 'Undefined'
    END        AS smoking_status,
    CASE
        WHEN c.concept_id IN
                ('77176002', '365981007', '59978006', '65568007', '82302008', '428041000124106', '230062009',
                '230064005', '230063004') THEN 1
        WHEN c.concept_id IN
                ('8392000', '160618006', '87739003', '360929005', '105541001', '105539002', '360918006',
                '105540000') THEN 3
        WHEN c.concept_id IN ('8517006', '160620009', '160621008', '281018007', '735128000', '48031000119106')
            THEN 2
        WHEN c.concept_id IN ('266919005') THEN 4
        WHEN c.concept_id IN ('266927001') THEN 5
        WHEN c.concept_id IN ('43381005', '102409004') THEN 6
        ELSE 7
    END AS smoking_ordinal -- Sortable value to find final status status when there are multiple status
FROM foundentity fe
        JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
        JOIN concept c ON fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology AND c.ontology='snomed'
        JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
        JOIN location l
                ON fel.location_id = l.id AND (l.type_ = 'sentence') -- IMPORTANT:  location id type must be ‘sentence’
        JOIN sentencelocation sl
                ON l.location_id = sl.id
        JOIN document d ON d.id = fe.document_id
WHERE sl.text NOT LIKE 'Smoking Status%'
-- limit to C-CDA documents: only those have structured metadata
AND EXISTS (SELECT 1 FROM documentstructuredmetadata m WHERE m.document_id = d.id)
AND c.concept_id IN
    ('77176002', '77176002', '365981007', '59978006', '65568007', '82302008', '428041000124106', '230062009',
    '230064005', '230063004',
    '8392000', '160618006', '87739003', '360929005', '105541001', '105539002', '360918006', '105540000',
    '8517006', '160620009', '160621008', '281018007', '735128000', '48031000119106',
    '266919005',
    '266927001',
    '43381005', '102409004')),
status_terms AS (SELECT fe.id          AS foundentity_id,
                        fe.text       AS status_term,
                        c.description  AS status_concept,
                        fec.concept_id AS status_snomed_id,
                        l.location_id  AS sentence_loc_id,
                        sl.text        AS sentence_text,
                        regexp_replace(d.filename, '.*/', '')     AS filename
                FROM foundentity fe
                        JOIN foundentityconcept fec
                                ON fe.id = fec.found_entity_id AND fec.concept_ontology = 'snomed'
                        JOIN concept c ON fec.concept_id = c.concept_id AND fec.concept_ontology = c.ontology
                        JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
                        JOIN location l
                                ON fel.location_id = l.id AND (l.type_ = 'sentence') -- IMPORTANT:  location id type must be ‘sentence’
                        JOIN sentencelocation sl
                                ON l.location_id = sl.id
                        JOIN document d ON d.id = l.document_id
                WHERE c.concept_id IN ('55561003', '723506003') -- Snomed ID 'Active (qualifier value)' and 'Resolved (qualifier value)'
                    AND c.ontology = 'snomed'
                    AND sl.text NOT LIKE 'Smoking Status%')
SELECT smoking_terms.foundentity_id             as fe_id,
    smoking_terms.filename,
    smoking_term,
    smoking_concept,
    smoking_status,
    smoking_ordinal,
    status_term,
    smoking_terms.polarity AS term_polarity,
    smoking_terms.uncertainty AS term_uncertainty,
    smoking_terms.section_name,
    REGEXP_REPLACE(sentence, '\n', ' ') AS sentence
FROM smoking_terms
        LEFT JOIN status_terms ON smoking_terms.sentence_loc_id = status_terms.sentence_loc_id  -- Retrieve the status term in same sentence as smoking
ORDER BY filename, smoking_ordinal, status_term, uncertainty;
```

#### MS SQL Server

```sql
WITH smoking_terms AS (
    SELECT fe.id          AS foundentity_id,
        fe.text       AS smoking_term,
        c.description  AS smoking_concept,
        fec.concept_id AS snomed_id,
        fe.polarity    AS polarity,
        fe.uncertainty AS uncertainty,
        fe.section_name,
        regexp_replace(d.filename, '.*/', '')     AS filename,
        l.location_id  AS sentence_loc_id,
        sl.text        AS sentence,
        CASE
            WHEN c.concept_id IN
                    ('77176002', '365981007', '59978006', '65568007', '82302008', '428041000124106', '230062009',
                    '230064005', '230063004') AND fe.polarity = 'asserted' THEN 'Smoker'
            WHEN c.concept_id IN
                    ('8392000', '160618006', '87739003', '360929005', '105541001', '105539002', '360918006',
                    '105540000') THEN 'Non-smoker'
            WHEN c.concept_id IN ('8517006', '160620009', '160621008', '281018007', '735128000', '48031000119106')
                THEN 'Ex-smoker'
            WHEN c.concept_id IN ('266919005') THEN 'Never smoked'
            WHEN c.concept_id IN ('266927001') THEN 'Unknown'
            WHEN c.concept_id IN ('43381005', '102409004') AND fe.polarity = 'asserted' THEN 'Passive smoker'
            ELSE 'Undefined'
            END        AS smoking_status,
        CASE
            WHEN c.concept_id IN
                    ('77176002', '365981007', '59978006', '65568007', '82302008', '428041000124106', '230062009',
                    '230064005', '230063004') THEN 1
            WHEN c.concept_id IN
                    ('8392000', '160618006', '87739003', '360929005', '105541001', '105539002', '360918006',
                    '105540000') THEN 3
            WHEN c.concept_id IN ('8517006', '160620009', '160621008', '281018007', '735128000', '48031000119106')
                THEN 2
            WHEN c.concept_id IN ('266919005') THEN 4
            WHEN c.concept_id IN ('266927001') THEN 5
            WHEN c.concept_id IN ('43381005', '102409004') THEN 6
            ELSE 7
            END        AS smoking_ordinal -- Sortable value to find final status status when there are multiple status
    FROM foundentity fe
            JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
            JOIN concept c
                ON fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology AND c.ontology = 'snomed'
            JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
            JOIN location l
                ON fel.location_id = l.id AND (l.type_ = 'sentence') -- IMPORTANT:  location id type must be ‘sentence’
            JOIN sentencelocation sl
                ON l.location_id = sl.id
            JOIN document d ON d.id = l.document_id
    WHERE sl.text NOT LIKE 'Smoking Status%'
    -- limit to C-CDA documents: only those have structured metadata
    AND EXISTS (SELECT 1 FROM documentstructuredmetadata m WHERE m.document_id = d.id)
    AND c.concept_id IN
        ('77176002', '77176002', '365981007', '59978006', '65568007', '82302008', '428041000124106', '230062009',
        '230064005', '230063004',
        '8392000', '160618006', '87739003', '360929005', '105541001', '105539002', '360918006', '105540000',
        '8517006', '160620009', '160621008', '281018007', '735128000', '48031000119106',
        '266919005',
        '266927001',
        '43381005', '102409004')),
    status_terms AS (SELECT fe.id          as foundentity_id,
                            fe.text       AS status_term,
                            c.description  AS status_concept,
                            fec.concept_id AS status_snomed_id,
                            l.location_id  AS sentence_loc_id,
                            sl.text        AS sentence_text,
                            regexp_replace(d.filename, '.*/', '')     AS filename
                    FROM foundentity fe
                            JOIN foundentityconcept fec
                                    ON fe.id = fec.found_entity_id AND fec.concept_ontology = 'snomed'
                            JOIN concept c ON fec.concept_id = c.concept_id AND fec.concept_ontology = c.ontology
                            JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
                            JOIN location l
                                ON fel.location_id = l.id AND (l.type_ = 'sentence') -- IMPORTANT:  location id type must be ‘sentence’
                            JOIN sentencelocation sl
                                ON l.location_id = sl.id
                            JOIN document d ON d.id = l.document_id
                    WHERE c.concept_id IN ('55561003', '723506003') -- Snomed ID 'Active (qualifier value)' and 'Resolved (qualifier value)'
                        AND c.ontology = 'snomed'
                        AND sl.text NOT LIKE 'Smoking Status%')
SELECT smoking_terms.foundentity_id             as fe_id,
    smoking_terms.filename,
    smoking_term,
    smoking_concept,
    smoking_status,
    smoking_ordinal,
    status_term,
    smoking_terms.polarity                   AS term_polarity,
    smoking_terms.uncertainty                AS term_uncertainty,
    smoking_terms.section_name,
    REPLACE(sentence, '\n', ' ') AS sentence
FROM smoking_terms
        LEFT JOIN status_terms ON smoking_terms.sentence_loc_id = status_terms.sentence_loc_id -- Retrieve the status term in same sentence as smoking
ORDER BY filename, smoking_ordinal, status_term, uncertainty;
```

| fe_id | smoking_term           | smoking_concept                               | smoking_status | smoking_ordinal | status_term | term_polarity | term_uncertainty | section_name           | sentence                                                                                                 |
| ----- | ---------------------- | --------------------------------------------- | -------------- | --------------- | ----------- | ------------- | ---------------- | ---------------------- | -------------------------------------------------------------------------------------------------------- |
| 20102 | (Never Smoked)         | Never smoked tobacco (finding)                | Never smoked   | 4               | Active      | asserted      | certain          | SOCIAL HISTORY SECTION | • Tobacco Use \| SNOMED-CT \| 266919005 \| Never smoker (Never Smoked) \| 03-31-2010 10:00:00 \| Active. |
| 20101 | Never smoker           | Never smoked tobacco (finding)                | Never smoked   | 4               | Active      | asserted      | certain          | SOCIAL HISTORY SECTION | • Tobacco Use \| SNOMED-CT \| 266919005 \| Never smoker (Never Smoked) \| 03-31-2010 10:00:00 \| Active. |
| 21217 | Smoking Status Unknown | Tobacco smoking consumption unknown (finding) | Unknown        | 5               | —           | asserted      | certain          | SOCIAL HISTORY SECTION | • Smoking Status Unknown \|.                                                                             |

## Next steps

The query returns multiple rows per document. This is intentional to allow the analyst to review the output. However, it is possible to further coallesce the rows returning only 1 entity per document using a window function that orders the rows by smoking_ordinal and returns the first value in the window.

## Related links

* [Entity-level Tables](/nlp/database/schema/core/entity-tables)