> 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 Vaccines & Dates of Administration

## Synopsis

This query retrieves vaccine drug mentions and vaccination events that are encoded in the structured data elements of CCD documents.

## 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, vaccination events are encoded in `SubstanceAdministration` elements in the structured data (as shown in the XML exerpt below). `SubstanceAdministration` elements reference encoded vaccine medications using `CVX` coding system, and records the `effective_time` of the event. This information have no corresponding textual elements, and are therefore not shown to the human reader. Hence, you may want to retrieve data about vaccine administration directly from the structured data. This query shows how to do so.

![Substance administration coded entry](/_fern-img/330c004afd040d0a53407ee17b64a9c31591b77a2b9d6ebc3b1307aa9714df9e.webp)

## SQL query

#### PostgreSQL

```sql
WITH coded_data AS (
SELECT  coded_entries.docid  AS docid,
        coded_entries.filename,
        coded_entries.label,
        coded_entries.class_code,
        coded_entries.parent_ref,
        coded_entries.section_ref,
        coded_entries.display_name,
        coded_entries.text_id,
        coded_entries.sentence,
        coded_times.effective_time,
        coded_entries.code,
        coded_entries.code_system,
        coded_entries.code_system_name,
        coded_times.start_time as start_time,
        coded_times.end_time   as end_time
FROM ( SELECT dm.document_id AS docid, regexp_replace(d.filename, '.*/', '') AS filename,
                dm.value::jsonb ->> 'class_code' as class_code,
                dm.value::jsonb ->> 'label' as label,
                dm.value::jsonb ->> 'parent_reference' as parent_ref,
                dm.value::jsonb ->> 'section' as section_ref,
                dm.value::jsonb ->> 'display_name' as display_name,
                dm.value::jsonb ->> 'text_id' as text_id,
                dm.value::jsonb ->> 'text' as sentence,
                dm.value::jsonb ->> 'code' as code,
                dm.value::jsonb ->> 'code_system' as code_system,
                dm.value::jsonb ->> 'code_system_name' as code_system_name
        FROM document d
            JOIN documentstructuredmetadata dm ON d.id = dm.document_id
) as coded_entries
LEFT JOIN ( SELECT dm.document_id as docid,
                dm.value::jsonb ->> 'label' as label,
                string_agg(time.etime::jsonb ->> 'value', ', ') as effective_time,
                --> also retrieve low-high times for completeness
                string_agg(time.etime::jsonb #>> '{low,value}', ', ') as start_time,
                string_agg(time.etime::jsonb #>> '{high,value}', ', ') as end_time
                FROM documentstructuredmetadata dm
                    JOIN LATERAL (SELECT dm.id, jsonb_array_elements(dm.value::jsonb -> 'effective_time') as etime ) as time ON true
                GROUP BY dm.document_id, label
) as coded_times
ON coded_entries.docid = coded_times.docid AND
    coded_entries.label = coded_times.label
)
SELECT sbadm_event.docid, sbadm_event.filename,
    sbadm_event.section AS section_display_name,
    sbadm_event.text_id,
    cvx_entry.display_name,
    cvx_entry.code_system_name,
    cvx_entry.code_system,
    cvx_entry.code,
    sbadm_event.effective_time,
    sbadm_event.low_time,
    sbadm_event.high_time,
    cvx_entry.sentence
FROM ( SELECT sbadm_cd.docid, sbadm_cd.filename,
            sbadm_cd.label as label,
            sec_cd.display_name as section,
            sbadm_cd.display_name as display_name, --> product
            sbadm_cd.text_id as text_id,
            sbadm_cd.effective_time as effective_time,
            sbadm_cd.end_time as low_time,
            sbadm_cd.start_time as high_time
    FROM coded_data sbadm_cd
    JOIN coded_data sec_cd ON sbadm_cd.section_ref = sec_cd.label
    --> retrieve section info from section entries
    WHERE sbadm_cd.class_code = 'SBADM' --> Substance Administration ACT
) AS sbadm_event
JOIN (
    SELECT cvx_cd.*
    FROM coded_data cvx_cd
    WHERE cvx_cd.code_system_name = 'CVX' AND cvx_cd.parent_ref IS NOT NULL) AS cvx_entry
ON sbadm_event.label = cvx_entry.parent_ref AND --> cvx entries related to sbadm entries
    sbadm_event.docid = cvx_entry.docid
ORDER BY filename;
```

#### Snowflake

```sql
WITH coded_data AS (
SELECT  coded_entries.docid  AS docid,
        coded_entries.filename,
        coded_entries.label,
        coded_entries.class_code,
        coded_entries.parent_ref,
        coded_entries.section_ref,
        coded_entries.display_name,
        coded_entries.text_id,
        coded_entries.sentence,
        coded_entries.code,
        coded_entries.code_system,
        coded_entries.code_system_name,
        coded_times.effective_time,
        coded_times.start_time as start_time,
        coded_times.end_time   as end_time
FROM ( SELECT dm.document_id AS docid, regexp_replace(d.filename, '.*/', '') AS filename,
                PARSE_JSON(dm.value):label::string as label,
                PARSE_JSON(dm.value):class_code::string as class_code,
                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):code::string as code,
                PARSE_JSON(dm.value):code_system::string as code_system,
                PARSE_JSON(dm.value):code_system_name::string as code_system_name,
                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
) as coded_entries
LEFT JOIN ( SELECT document_id AS docid,
            PARSE_JSON(dm.value):label::string as label,
            listagg(PARSE_JSON(etime.value):value, ',') as effective_time,
            -- also retrieve start and end time which is sometimes populated
            listagg(PARSE_JSON(etime.value:low):value, ',')   as start_time,
            listagg(PARSE_JSON(etime.value:high):value, ',')  as end_time
        FROM documentstructuredmetadata dm
            JOIN LATERAL FLATTEN(input => PARSE_JSON(dm.value), path=>'effective_time',
                                outer => true) etime
            --> lateral join required because effective_time is an array
        GROUP BY docid, label
) as coded_times
ON coded_entries.docid = coded_times.docid AND
    coded_entries.label = coded_times.label
)
SELECT sbadm_event.docid, sbadm_event.filename,
    sbadm_event.section AS section_display_name,
    sbadm_event.text_id,
    cvx_entry.display_name,
    cvx_entry.code_system_name,
    cvx_entry.code_system,
    cvx_entry.code,
    sbadm_event.effective_time,
    sbadm_event.low_time,
    sbadm_event.high_time,
    cvx_entry.sentence
FROM ( SELECT sbadm_cd.docid, sbadm_cd.filename,
            sbadm_cd.label as label,
            sec_cd.display_name as section,
            sbadm_cd.display_name as display_name, --> product
            sbadm_cd.text_id as text_id,
            sbadm_cd.effective_time as effective_time,
            sbadm_cd.end_time as low_time,
            sbadm_cd.start_time as high_time,
            sbadm_cd.code as code,
            sbadm_cd.code_system as code_system,
            sbadm_cd.code_system_name as code_system_name
    FROM coded_data sbadm_cd
    JOIN coded_data sec_cd ON sbadm_cd.section_ref = sec_cd.label
    --> retrieve section info from section entries
    WHERE sbadm_cd.class_code = 'SBADM' --> Substance Administration ACT
) AS sbadm_event
JOIN (
    SELECT cvx_cd.*
    FROM coded_data cvx_cd
    WHERE cvx_cd.code_system_name = 'CVX' AND cvx_cd.parent_ref IS NOT NULL) AS cvx_entry
ON sbadm_event.label = cvx_entry.parent_ref AND --> cvx entries related to sbadm entries
    sbadm_event.docid = cvx_entry.docid
ORDER BY filename;
```

| code_system_name | code | display_name                                            | effective_time |
| ---------------- | ---- | ------------------------------------------------------- | -------------- |
| CVX              | 135  | Influenza, high dose seasonal                           | 20101024100000 |
| CVX              | 100  | pneumococcal conjugate PCV 7                            | 20101024100000 |
| CVX              | 88   | Influenza                                               | 200606281424   |
| CVX              | 88   | Influenza virus vaccine                                 | 20140510       |
| CVX              | 106  | Tetanus and diphtheria toxoids                          | 20120104       |
| CVX              | 166  | influenza, intradermal, quadrivalent, preservative free | 20150622       |
| CVX              | 88   | influenza, unspecified formulation                      | 20140510       |
| CVX              | 106  | DTaP, 5 pertussis antigens                              | 20120104       |
| CVX              | 166  | influenza, intradermal, quadrivalent, preservative free | 20150622       |