Query Composer Request v5.0
This documentation references PopMedNet 5.0 +
For version 4.0.3 documentation, please see PopMedNet User's Guide Version 4.0.3
PopMedNet User's Guide
Based on version 5.0
ESP Query Composer Request
The Electronic Support for Public Health (ESP) Query Composer Request operates against a simplified version of the full ESP data model, called ESP: MDPHnet. The ESP data model is a standardized representation of EHR-based encounter and patient demographic information. It uses a set of complex algorithms to identify selected diseases of public health concern such as acute hepatitis B, Lyme disease, influenza-like illness, and diabetes. The ESP: MDPHnet data model is generated via SQL scripts that execute against the full ESP data model. It provides a mapping layer between the full set of ESP tables and the PopMedNet software.
The ESP Query Composer allows you to define a cohort of patients using complex inclusion and exclusion criteria. For example, the ESP Query Composer allows you to develop custom denominator queries which return counts of patients with at least n number of medical encounters within a specified period and that meet the specified demographic or diagnostic criteria.
To create a Query Composer request, click on the New Request button in the request panel, choose ESP Requests, and then the Query Composer request type. This will display the Request Composer page for Query Composer requests.
The ESP Query Composer utilizes criteria groups to specify the conditions of the query.
At least one criteria group, the primary criteria group, must be specified. Each subsequent criteria group reduces the patient cohort. Criteria groups are determined by terms within each group. ICD-9 Diagnosis and Disease terms within a criteria group are OR-ed, whereas demographic terms (Gender, Race, Age Range, and Zip Code) and Visit terms are AND-ed. Multiple Criteria Groups are AND-ed to determine the final cohort. Criteria groups may also be negated to exclude criteria from the final patient cohort.
On the Request Composer page of the Query Composer request, the primary criteria group is already present, as shown in the figure to the right.
Enter a name for the criteria group. Click Add Terms and select Diagnosis, Condition, Visits, or Tobacco Use or hover over Demographic and select Age Range, Gender, Race, Race-Ethnicity, Location, Custom Location, or Zip Codes. The criteria group must contain at least a Diagnosis, Disease, or Visits term. To remove a term from the query, click the [X] control on the upper right of the term.
See the table below for descriptions of the possible terms to include in a criteria group:
Term | Description |
ICD-9 Codes | Click Select to open the code selector dialog. There are two ways to select codes:
The Search Results panel will populate with a list of codes and their descriptions. Click on a code to add it to the Selected Codes list. Click on the code again in the Selected Codes list to deselect it. Click Save to close the code selector dialog and insert the codes into the query. Note: When ICD-9 codes of 3- or 4-digits are selected, all available codes to the highest degree of specificity will be included in the results. For example, selecting the code 250 is equivalent to a query of 250*; results for all available 250.xx codes will be included. |
Condition | Select a condition from the dropdown menu. Options include Influenza-like Illness, Diabetes: Type 1, Diabetes: Type 2, Diabetes: Gestational Diabetes, Diabetes: Prediabetes, Asthma, Depression, Opioid Prescription, Benzodiazepine Prescription, Concurrent Benzodiazepine-Opioid Prescription, High Opioid Use, The Condition term queries the ESP_DISEASE Table in the ESP: MDPHnet data model. This table contains one record per combination of patient and detected condition. A date associated with a condition is the date at which the condition was detectable. Therefore, within a criteria group, using a restrictive time period in combination with the Condition term will restrict the query to only look for patients who met the condition criteria during that time period. It will not detect patients who met the condition criteria during an earlier or later time period. Similarly, the age associated with a condition is the age as of January 1 of the year in which the condition was detectable. Therefore, within a criteria group, using the Age Term term in combination with the Condition term will restrict the query to only look for patients who met the condition criteria within the age range specified. It will not detect patients who met the condition criteria at an earlier or later age. If the Condition term is in the primary criteria group and Age is selected as a stratification option, patients will be categorized into age strata based on their age as of January 1 of the year in which the condition was detectable. |
Minimum Visits | Enter the minimum number of visits to select patients that have at least n visits matching the specified criteria in the criteria group. If a criteria group contains only a minimum visits term, that group will select for patients with at least n encounters of any type within the health system. |
Observation Period Range | Enter the start period and/or end period using the date selector. |
Age Range | Enter the minimum age and maximum age for the patient population. Age Calculations Currently in the ESP Query Composer, there is a risk of double counting patients across age strata. Age is currently calculated as the age of the patient at the encounter that met the criteria of interest.If the patient has more than one encounter that meets the criteria of interest and their ages at the encounters fall into more than one age strata, they will be double counted if counts are summed across age strata. |
Sex | Select a sex from the dropdown menu for the patient population. Options include male, female, and male and female. |
Race | Select one or more options from the race selector. Options include Unknown, American Indian or Alaska Native, Asian, Black or African American, Native Hawaiian or Other Pacific Islander, and White. |
| Race-Ethnicity | Select one or more options from the race-ethnicity selector. Options include Unknown, American Indian or Alaska Native, Asian, Black or African American, White, and Hispanic. |
| Location | The Location term queries for patients with home zip codes in the location(s) of interest. Click Select to open the location selector dialog. There are two ways to search for pre-set locations:
The Search Results panel will populate with a list of locations. Click on a location to add it to the Selected list. Click on the location again in the Selected list to deselect it. Click Save to close the selector dialog and insert the locations into the query. |
| Custom Location | The Custom Location term queries for patients with home zip codes in the location(s) of interest. In the Name field, enter a name for the custom location. This name will be displayed in the results. In the Zip Codes box, type or paste a comma-delimited set of zip codes. Alternatively, click Select Zip Codes to open the code selector dialog. There are two ways to search for codes:
The Search Results panel will populate with a list of zip codes and their descriptions. Click on a code to add it to the Selected Codes list. Click on the code again in the Selected Codes list to deselect it. Click Save to close the code selector dialog and insert the zip codes into the query. |
Zip Code Selector | Click Select to open the code selector dialog. There are two ways to search for codes:
The Search Results panel will populate with a list of zip codes and their descriptions. Click on a code to add it to the Selected Codes list. Click on the code again in the Selected Codes list to deselect it. Click Save to close the code selector dialog and insert the zip codes into the query. |
| Tobacco Use | Select one or more options from the tobacco use selector. Options include Current, Former, Never, Passive, and Not Available. |
Report Selector (primary criteria group only) | Choose one or more values used to stratify the results. If no option is selected, a single patient count will be returned. Note: this option is only available for the primary criteria group. Results will be stratified according to the criteria in the primary group. For example, if age is selected as a report selector, the results will be stratified according to the age at which the patients were when they met the criteria in the primary group. Options include:
Stratifications * Only these stratification options may be selected to display projected and adjusted results. See ESP Query Composer Projections v5.0 for more details Age is currently calculated as the age of the patient at the encounter that met the criteria of interest.If the patient has more than one encounter that meets the criteria of interest and their ages at the encounters fall into more than one age strata, they will be double counted if counts are summed across age strata. |
Exclude Criteria Group | Check the checkbox to exclude patients meeting the criteria in the criteria group from the final cohort. Note: this option is not available on the primary criteria group. |
To add another criteria group to the query, click Add Group in the upper right of the Criteria Groups panel. To remove a criteria group, click the [X] symbol in the upper right of that criteria group. The following figure displays an example of an ESP Query Composer request with two criteria groups
