Clarity

Epic 2017
CLR130

SQL Fundamentals
Epic Systems Corporation
1979 Milky Way • Verona, WI 53593 • Voice: (608) 271-9000 • Fax: (608) 271-7237
www.epic.com
[email protected]

This guide is arranged for two-sided copying. Rev: March 6th, 2017
Table of Contents
 Introduction 1•1

 Creating a Basic Query 2•1

 Logical Expressions 3•1

 Functions 4•1

 Grouping and Summarizing Data 5•1

 Adding Multiple Database Objects 6•1

 Adding a Query to Crystal Reports 7•1

 Using Subqueries 8•1

 Capstone Exercise A•1

 Handouts B•1
TOC•2 Table of Contents

Clarity SQL Fundamentals Epic 2017 Training Companion

Lesson 1
Introduction
Introduction 3
Prerequisites and Assumptions 3
How to Use This Training Companion 3
Contact Information 5
After-Class Practice 6
Epic Resources 6
The Big Picture 9
Database Concepts 10
What is SQL? 10
Database Objects 11
Data Types in SQL 12
Database Administration 13
1•2
Introduction

Clarity SQL Fundamentals Epic 2017 Training Companion

1•3
Introduction

Introduction
Welcome to Clarity SQL Fundamentals training!
This course will cover the basics of interpreting and creating SQL queries
including selecting data to display, filtering and grouping records, formatting
data, and proper use of SQL functions. In this class, you will strive to learn the
intricacies of SQL, as well as reinforce investigative techniques and gain exposure
to some of the more common Clarity tables.

Prerequisites and Assumptions

Before undertaking the course of study within this class, you must have
completed the following tasks:
 Attend the RPT100 Introduction to Crystal Reporting class (or have prior
experience with Crystal Reports)
 Attend the CLR110 Clarity Data Model Fundamentals class
This course is designed to be taken before any application-specific Clarity Data
Model training.

How to Use This Training Companion

This training companion is intended as a tool for learning the material presented
in class. Please use it as a place for notes on the topics and additional details
covered in class.
Recommendations for certification success:

 Take thorough notes during class in your companion.

 Review your companion and lecture notes.
 Practice in the system.
 Review questions at the end of each chapter.

Clarity SQL Fundamentals Epic 2017 Training Companion

1•4
Introduction

Informative text boxes throughout the companion will draw your attention to
content of particular interest. Here are examples of types of text boxes you might
encounter:

Critical information for the setup or use of a particular feature. For

example: a particular feature is only available if application X is installed.

Setup information related to the current feature. This provides basic

information about the system build required to get a feature to work
properly.

Extra information related to a given topic. You do not need to know this
information for certification.

Foundation System information or recommendations. You do not need to

know this information for certification.
The Foundation System is regularly updated and improved based on
lessons learned and customer feedback. Log in to the Foundation System
Hosted Environment to see our current setup.

Clinical or healthcare information related to a given scenario. You do not

need to know this information for certification.

Our documents include Epic's standard copyright statement. Please do not remove
it. If you create new materials that contain Epic content, we ask that you include:
"This material contains confidential and copyrighted information of Epic Systems
Corporation" whenever that document contains Epic's screen shots or other
content based on our materials. You should also consider adding your own
copyright statement.
Be sure not to post Epic-related e-learnings or documentation on an unprotected
website that can be accessed by the general public. This includes both materials
that Epic has written and that you have written.

Clarity SQL Fundamentals Epic 2017 Training Companion

1•5
Introduction

Contact Information
The table below provides you with contact information you may need during your
certification process:
Contact Used For
[email protected] The majority of your questions should be
directed to this email address. This address
connects you with our team of Cogito
trainers who can answer your questions on
anything related to reporting training. All
class-related questions should be directed to
this email address and not to the
Certification Environments or UserWeb
addresses listed below. If you aren't sure
about who to contact, use this email
address.
[email protected] Use this email address only if your access
specifically to the Certification
Environments is broken or lost. If you can
access the Certification Environments but
are having trouble logging in to
Hyperspace, contact
[email protected].
[email protected] Use this email address only if your access
specifically to the UserWeb is broken or
lost. If you have a question regarding
Cogito training contact
[email protected].
Direct email for your Epic representative For all exam reviews, contact your Epic
(either the implementers or your technical representative. You can find this
services representative) information by asking a member of your
own project team or by asking
[email protected].
[email protected] Use this email address to ask questions
about requesting, submitting, or taking
exams, as well as proctors for those exams.
[email protected] Use this email address to ask questions
about registering for classes at Epic.
[email protected] Use this email address to inquire about the
status of your paper certification or NVT
stickers.

Clarity SQL Fundamentals Epic 2017 Training Companion

1•6
Introduction

After-Class Practice
After class, you will likely want to practice in the system and complete additional
in-class or if-you-have-time exercises. Please reference the following documents
available on Galaxy for information on how to log in and use our practice system:

Document Used For

An Introduction to Epic’s Certification General access information,
Environments Hyperspace and Text login information,
and FAQs.
Cogito’s Introduction to Epic’s Cogito-specific login and usage
Certification Environments information, specifically practicing in
Crystal Reports and SQL Management
Studio.

Epic Resources
After leaving training at Epic, you will likely get asked questions that you do not
know the answers to. This table outlines supplemental resources that are available
to you that will help you find the information that you need. Here is a list of the
tools available and how they should be used:

Epic’s online documentation portal. Contains

training materials, setup and support guides,
and other useful documents.
https://galaxy.epic.com
Epic-provided platform for e-learning
content. Hosts e-learnings created by Epic as
well as customer built modules.
https://welearning.epic.com
Online collection of tools and information
about Epic and our software. On your front
page you can favorite discussions to
read/participate in.
https://userweb.epic.com

Clarity SQL Fundamentals Epic 2017 Training Companion

1•7
Introduction

The Community Library is Epic’s content

sharing program. Technical Services
routinely extract content data from
customers’ systems, which Epic then hosts
on the UserWeb for customer review and
use.
https://comlib.epic.com
Epic’s web-based issue tracking tool. Use
Sherlock to have direct access to create and
update Support Logs (SLGs), Release
Authorizations, and Reportable Issues.
https://sherlock.epic.com
Epic’s training website encompassing a
variety of training-related tools used by our
organizations. Contains Certification
Tracking, Course Catalog, Exam Requests,
training announcements, and Training
Wheels materials.
https://training.epic.com

The Data Handbook provides reference

information to access data within your
enterprise production database.
https://datahandbook.epic.com
Nova is an Epic-developed web program for
release note management. You can use Nova
to assign and review release notes, select
which enhancements to implement, and
report on release note progress.
https://nova.epic.com

Clarity SQL Fundamentals Epic 2017 Training Companion

1•8
Introduction

Looking for more guidance through these tools?

Check out the UserWeb Overview document which can be found by clicking the
blue button in the top right of your screen as soon as you log into the
https://userweb.epic.com site:

Clarity SQL Fundamentals Epic 2017 Training Companion

1•9
Introduction

The Big Picture

Overview of Epic dataflow

Clarity SQL Fundamentals Epic 2017 Training Companion

1•10
Introduction

Database Concepts

Clarity is a relational database, which is a collection of tables and other objects.

What is SQL?
SQL is an acronym for Structured Query Language. It is generally pronounced
“es-queue-el” or “see-kwell”. SQL can refer to a type of database or a language
used to communicate with that database. SQL has many implementations,
including MS-SQL and Oracle.

Clarity SQL Fundamentals Epic 2017 Training Companion

1•11
Introduction

Database Objects
In your previous Clarity classes, much of the emphasis was placed on discussing
which ‘tables’ to use in reports. Most often, we were actually referring to
extracted tables. Extracted tables are what most business intelligence developers
envision when they think of Clarity tables. Extracted tables extract data from
Epic’s production database, usually from items in Chronicles, and store that data
in columns in Clarity. However, your Clarity database will contain other types of
database objects in addition to extracted tables.

Object Description
Extracted Tables Extracted tables are populated during ETL with data
directly from Chronicles. These make up the majority of a
Clarity database.
Examples: PATIENT, PAT_ENC_HSP, CLARITY_SER
Derived Tables Derived tables are tables populated from queries that run
against other Clarity tables. Using derived tables can
reduce the amount of work that business intelligence
developers must do to report on Clarity data; one derived
table might take the place of several extracted tables.
Derived tables can increase report efficiency; they can be
thought of as “pre-compiled and saved” reports. Derived
tables in Clarity typically start with D_ or F_.
Examples: F_SCHED_APPT, D_MU_MEASURES
Views Views are queries that are stored in the database. A view
can be accessed as if it were a table. Unlike tables,
however, views do not actually store results. This means
that using a view will not necessarily increase the
efficiency of a report. However, views can reduce the
amount of work that business intelligence developers must
do to report on Clarity data as well as reduce the
complexity of a query. Views in Clarity typically start
with a V_.
Examples: V_SCHED_APPT, V_ACCESS_LOG
Stored Procedures A SQL statement stored in your SQL Server so that it can
be run as needed. In Clarity, these are used to create
derived tables and are run after ETL completes.
Example: ESP_F_SCHED_APPT (the script that creates
F_SCHED_APPT)

Clarity SQL Fundamentals Epic 2017 Training Companion

1•12
Introduction

Functions Functions can take in parameters and return results. These

can be used directly by reports, or with custom queries.
Functions will be discussed in more detail in Lesson 4 of
this training companion.
Examples: EFN_DATEDIFF(), EFN_DIN()

Data Types in SQL

The data types used by Chronicles are String, Number, Date, Time, Instant, and
Category. However, when the ETL process transforms this data into a relational
format, it also converts the data into the standard data types used by relational
databases (the ANSI standard). The mapping between Chronicles and Clarity data
types is diagrammed below:

String and Number are the most straightforward: all Strings become VARCHARs,
and all Numbers become NUMERICs or FLOATs.
Dates, Times, and Instants all become DATETIMEs. Recall in Chronicles that
dates, times, and instants are all stored in an “internal format” (number of days
since 12/31/1840 or number of seconds since midnight). These are converted into
actual dates or times during the ETL process. In order to fit into the DATETIME
format, most columns are also are modified to include both a date and a time.
Dates are extracted as midnight on the date. Times are extracted as the time on
1/1/1900. Instants are extracted as-is.
Contact Date Reals, as discussed in Clarity Fundamentals, remain a FLOAT, in
order to preserve the information they contain.

Clarity SQL Fundamentals Epic 2017 Training Companion

1•13
Introduction

Categories may be extracted either as an INTEGER or a VARCHAR, depending

on the category list in question.
Depending on the column’s data type, there may be a secondary characteristic
defined as well. For example, for a VARCHAR, you can define the maximum
length of the string that the column can hold.

Database Administration
This class covers the information needed to create SQL queries to report off of a
Clarity database; it does not cover the information needed to administer that
Clarity database. In this way, the class differs from most other SQL courses in
that there are some very basic topics that this class does not cover. To report off
of Clarity, INSERT, CREATE, DELETE, and UPDATE commands are not needed.
These commands, which are typically involved in the Clarity ETL process, are
used to modify the database and are handled by your database administrator.

Clarity SQL Fundamentals Epic 2017 Training Companion

©2017 Epic Systems Corporation. Confidential
Lesson 2
Creating a Basic Query
Creating a Basic Query 3
By the End of This Lesson, You Will be Able to… 3
The Big Picture 4
Generating a List of Data 5
SELECTing from the Database 6
Exercise 1: Creating a Simple Query 6
Exercise 2: Creating an Additional Simple Query 7
Exercise 3: If Time Permits – Challenge Queries 7
Comments 8
SELECTing Columns 8
Exercise 4: SELECTing Columns 9
Exercise 5: SELECTing Additional Columns 9
Exercise 6: If Time Permits – Medication Administrations 9
Limiting Results Using the SELECT clause 10
Selecting Distinct Values 10
Exercise 7: Select 100 Providers 10
Exercise 8: Explore SELECT DISTINCT 11
Exercise 9: If Time Permits – Note Types 11
Aliasing 11
Style Choices 12
Exercise 10: SELECTing Columns with Aliases 13
Exercise 11: If Time Permits – Getting a List of Hospital Encounters 13
SELECTing Constants 14
2•2
Creating a Basic Query

Exercise 12: Exploring Queries with Constants 15

Exercise 13: If Time Permits – More Exploring Constants 15
Reviewing the Chapter 17
Review Questions 17
Review Key 18
Exercise Answers 19
Exercise 8: Explore SELECT DISTINCT 19
Exercise 12: Exploring Queries with Constants 19
Exercise 13: If Time Permits – More Exploring Constants 19
Study Checklist 20

Clarity SQL Fundamentals Epic 2017 Training Companion

2•3
Creating a Basic Query

Creating a Basic Query

A SQL query is a request for information. Writing SQL queries differs from
writing in other programming languages. In most programming languages, your
code is a set of instructions for the computer to follow. In SQL, your code is just a
list of things you would like returned, and the database will determine how best to
return that information. Using formal terminology, most programming languages
are imperative, while SQL is merely declarative. In this chapter, you’ll learn how
to specify the basics of what you’d like to see on your report.

By the End of This Lesson, You Will be Able to…

 Create and run a query in Microsoft SQL Management Studio

 Describe how the SELECT clause is used
 Describe how the FROM clause is used
 Identify aliases in a SQL query

Clarity SQL Fundamentals Epic 2017 Training Companion

2•4
Creating a Basic Query

The Big Picture

SELECT . . .
FROM . . .

CLARITY

Clarity SQL Fundamentals Epic 2017 Training Companion

2•5
Creating a Basic Query

Generating a List of Data

A SQL query is broken into several different clauses. Each of these clauses
fulfills one or more purposes in the query as a whole. The two SQL clauses that
are typically required for any SQL query to get information from the database are
the SELECT and FROM clauses. A query may also be called a SELECT statement.
Clause Description
SELECT The SELECT clause is where you list the columns that
should appear in your result grid.
FROM The FROM clause is where you specify the data sources
used in your query, typically tables.

The result of any SQL query is a table. This means that you can potentially
write queries and use them in place of other tables. This is discussed
further in Lesson 8 - Using Subqueries.

SQL queries must be written with their clauses in the following order:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
However, when implementing the query, SQL processes the clauses in the
following order:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Because of this, when you are analyzing a SQL query, you may find it helpful to
read the query in this second order, not the order in which it is written.

Clarity SQL Fundamentals Epic 2017 Training Companion

2•6
Creating a Basic Query

SELECTing from the Database

One of the most basic requests that can be made to a database is to ask it to list all
of its contents. This request is produced by the syntax:
SELECT * FROM [TABLE]
This can be thought of as “return every row and every column in the table.” The
asterisk (*) means “all columns,” and there are no limits on the rows returned.
Though SELECT * FROM [TABLE] is a relatively simple query, it can cause
problems if used carelessly. Specifically, if used with large tables, it can
overconsume processing resources. Take care when attempting to run this
in a production database. See “Limiting Results Using the SELECT
clause” later in this lesson for methods to prevent these problems.

Exercise 1: Creating a Simple Query

Following the steps below, create and run a query to show all of the rows and
columns in the CLARITY_SER table.
□ 1. Using the SQL Management Studio connection information provided on
your Classroom Information Sheet, open SQL Management Studio and
establish the database connection.
□ 2. On the Standard SQL toolbar, click the New Query button to create a
new query.
The Query Editor and the SQL Editor Toolbar appear.
□ 3. Make sure the SQL Editor Toolbar shows the correct database, as listed in
the SQL Management Studio connection information provided.

□ 4. Enter the following text into the Query Editor.

SELECT * FROM CLARITY_SER
□ 5. On the SQL Editor Toolbar, click the Execute button (or press F5 on your
keyboard) to run the query.
The Results window appears or refreshes below as the query executes.

Clarity SQL Fundamentals Epic 2017 Training Companion

2•7
Creating a Basic Query

Exercise 2: Creating an Additional Simple Query

Create and run a query to show all of the rows and columns in the
F_SCHED_APPT table. Explore the columns available in this table.

F_SCHED_APPT is a derived table commonly used in Cadence reporting.

This table contains information about patient appointments, with one row
per appointment.

Exercise 3: If Time Permits – Challenge Queries

Create and run queries for each of the below report requests. Recall the tools from
CLR110 Lesson 4: Finding Data in Clarity that you can use to map Chronicles
items to Clarity tables and columns.
 Write a query to show all rows and columns from the extracted table that
extracts I EPT 18400.
 Write a query to show all rows and columns from the table that extracts
one row for every Radar component record (hint: Radar components live
in the IDB master file).
 Write a query to display data from the table that extracts the estimated
date of delivery for a pregnancy episode. The estimated date of delivery is
stored in I HSB 35030.

ʺI [INI] [#]ʺ is shorthand for ʺitem number [#] in the master file [INI].ʺ
Therefore, I EPT .1 means ʺItem .1 in the EPT master file.ʺ
"R [INI] [ID]" is shorthand for "the record with the ID of [ID] in the
master file [INI]." Therefore, R EPT TR8R means "the record with the ID
of TR8R in the EPT master file."

Clarity SQL Fundamentals Epic 2017 Training Companion

2•8
Creating a Basic Query

Comments

Comments are sections of text within your query that are not treated as code; in
fact, they are completely ignored by SQL. Commenting code serves two
purposes: documentation and troubleshooting. Proper documentation with
comments enables both yourself and others to better use, understand, view, and
edit your queries. Comments can start at any point during the query, on their own
line or not. Line comments start with a double-dash (--), which will cause the rest
of the line to become a comment. Block comments start with a forward-slash
followed by an asterisk (/*), which will cause all text to be treated as a comment
until it reaches an asterisk followed by a forward-slash (*/).

SELECT -- Everything to the right of the double-dash is ignored

*
-- Even if it's the only thing on the line
FROM
CLARITY_SER
/* This is a block comment.
It can occupy several lines.
*/

SELECT /* Block Comments

can even
be embedded
in a line */
*
FROM
CLARITY_SER

Comments can also provide a method of troubleshooting, since they can be used
to temporarily disable sections of the code.

SELECTing Columns
Often, in Clarity, using SELECT * FROM [TABLE] will get you plenty of data, but
not much usable data. To provide flexibility, Clarity is structured in such a way
that many times you will not want to include all of the columns available to you.
Instead, you will want to choose specific columns for any given report.
The SELECT list is a comma-separated list of columns to display in your result
grid. To see only patient names and IDs, you could write the following query:
SELECT
PAT_NAME
,PAT_ID
FROM
PATIENT

Clarity SQL Fundamentals Epic 2017 Training Companion

2•9
Creating a Basic Query

Exercise 4: SELECTing Columns

Create and run a query to display the PROV_ID, PROV_NAME, PROV_TYPE,
STAFF_RESOURCE_C, and EXTERNAL_NAME columns from the
CLARITY_SER table.
□ 1. Create a new query and type the following text.
FROM
CLARITY_SER

Recall that the FROM clause is processed before the SELECT clause.
Building the query in the same order as it is processed can help you
ensure that you’re building it correctly. Also, building the FROM clause
first improves completion matching for the rest of the query.

□ 2. Add the following text before the FROM clause.

SELECT
PROV_ID
,PROV_NAME
,PROV_TYPE
,STAFF_RESOURCE_C
,EXTERNAL_NAME

□ 3. Run the query.

Exercise 5: SELECTing Additional Columns

Create and run a query to display the PAT_ENC_CSN_ID, CONTACT_DATE,
APPT_LENGTH, APPT_STATUS_C, DEPARTMENT_ID, and PROV_ID
columns from the F_SCHED_APPT table.

Exercise 6: If Time Permits – Medication Administrations

Using only one table, write a query to show the columns that extract:
 The ID of an order (orders live in the ORD master file)
 The type of administration action associated with the order (I ORD 11080)
 The total dose administered (I ORD 11130)
 The unit of the dose (I ORD 11200)
 The user who administered the dose (I ORD 11110)
 When the dose was administered (I ORD 11030)

Clarity SQL Fundamentals Epic 2017 Training Companion

2•10
Creating a Basic Query

Limiting Results Using the SELECT clause

A SELECT statement can return any or all of the columns within chosen tables. In
your queries so far, you’ve returned every single row for your selected columns.
However, many tables in production databases can have millions of rows or more.
If you attempt to return all rows for these tables, it will be difficult for your
workstation and network to handle all of the data. One of the places in which you
can limit the number of rows returned is in the SELECT clause itself.

These methods work for MS-SQL. For Oracle, you will have to use a
WHERE clause with the ROWNUM value.

The SELECT TOP statement can be used to limit results to a number of rows or a
percentage of total rows. This is typically used for spot checking a report or
previewing data from a large table or complex query. For example, for the
PATIENT table, SELECT TOP 5 * FROM PATIENT would return the first five
rows of every column in the table whereas SELECT TOP 1 PERCENT * FROM
PATIENT would return the first one percent of rows of every column in the table.

Selecting Distinct Values

In addition to selecting an arbitrary number of results, the SELECT statement can
be used to only return unique results from your table. This is accomplished using
the keyword DISTINCT following SELECT.
For example,
SELECT DISTINCT
CUR_PCP_PROV_ID
FROM
PATIENT
would return a list of all of the provider IDs belonging to patients’ current primary
care providers in your database, without repetition. Even though a single provider
may be the PCP of many patients, their ID will only show up once in the results.
This functionality makes SELECT DISTINCT very useful in cases where the
number of results is less important than the different values in the results.
DISTINCT applies to the specific combination of columns in the SELECT list,
and only that set of columns. Changing which columns are included in the
SELECT list may change the number of rows returned by SELECT DISTINCT.

Exercise 7: Select 100 Providers

Using SELECT TOP, generate a report on the first 100 providers returned by the
database from CLARITY_SER. Show each provider’s ID and name.

Clarity SQL Fundamentals Epic 2017 Training Companion

2•11
Creating a Basic Query

Exercise 8: Explore SELECT DISTINCT

□ 1. Using SELECT DISTINCT and the table CLARITY_SER, write a query
to list the values in the column CLINICIAN_TITLE.
□ 2. Do you know how many instances of each value are in the database?

□ 3. Is this an exhaustive list of possible values for this column?

□ 4. What is the difference between SELECT and SELECT DISTINCT if

used with a primary key?

Exercise 9: If Time Permits – Note Types

Write a query that displays all of the distinct note types that have been extracted
to Clarity. Hint: Note Type is stored in I HNO 34033.

Aliasing
Aliasing allows you to assign new and temporary names to tables or columns.
These can be either from the database, in which case you are renaming them, or
they can be entirely new columns or 'tables' that don’t exist in the database.
There are many syntaxes for defining aliases, and several are version dependent.
You can use the AS keyword in an alias to make it explicit, but it is not required
(in Oracle, you can use the AS keyword to alias a column, but not a table). If the
alias has a space in it, it must be enclosed in double quotes or square brackets. In
MS-SQL, single quotes also work for columns (but not tables). We recommend
that you choose one aliasing syntax and stick with it for consistency. However,
you will see queries written by others that use different syntaxes.

SELECT
acc.ACCOUNT_ID AS ID
,acc.ACCOUNT_NAME "account name"
,acc.BIRTHDATE DOB
,acc.SSN [Social Security Number]
FROM
ACCOUNT acc

Clarity SQL Fundamentals Epic 2017 Training Companion

2•12
Creating a Basic Query

We recommend that you alias every table with a non-ambiguous alias, and that
you use those aliases whenever you reference the table or a column from it.

Crystal Reports and other reporting tools that auto-generate SQL code will
sometimes put the first reference to a table in the FROM clause in the
format of <DATABASE>.<SCHEMA>.<TABLE>. This is usually
followed by an alias, as in the example:
Clarity_2017_PRACTICE.dbo.CLARITY_SER CLARITY_SER
Sometimes, you will see quotes around each element, such as:
"Clarity_2017_PRACTICE"."dbo"."CLARITY_SER" "CLARITY_SER"

Style Choices
Consider the following SQL query:
SELECT
fact.PAT_ID "Patient ID"
,fact.PAT_NAME "Patient Name"
,fact.PAT_MRN_ID "Patient MRN"
,fact.AGE_YEARS "Age in Years"
,fact.CUR_PCP_NAME "Primary Care Provider"
FROM
V_PAT_FACT fact

There are several stylistic choices that have been made here, the first of which
is to make the code atomic. This means that each individual piece of code is
placed on its own line. In atomic code, it is easier to see what is happening
within different sections of code and to comment out single lines, temporarily
removing pieces of the query.
Placing commas at the start of the next line, as opposed to at the end of a line,
allows you to easily continue to edit a line without worrying about putting a
comma at the end. SQL ignores all whitespace, so the functionality is the same.
Epic recommends that Clarity be configured to be case-insensitive, so that
there is no difference between PATIENT and patient. However, in a query,
we recommend that you use uppercase for database objects such as tables,
columns, or functions, as well as for reserved words such as SELECT, OR, AS,
and AND. Lowercase should be used for aliases.
These style choices have no actual effect on the SQL query or the results. The
following code would return the same results:
Select Fact.PAT_ID "Patient ID", fact.PAT_NAME "Patient
Name",PAT_MRN_ID "Patient MRN",fact.AGE_YEARS "Age in
Years",CUR_PCP_NAME "Primary Care Provider" from v_pat_fact FACT

Clarity SQL Fundamentals Epic 2017 Training Companion

2•13
Creating a Basic Query

Exercise 10: SELECTing Columns with Aliases

Create and run a query to display the PROV_ID, PROV_NAME, PROV_TYPE,
STAFF_RESOURCE_C, and EXTERNAL_NAME columns from the
CLARITY_SER table. Use the alias “ser” for the CLARITY_SER table. Use the
following aliases for the columns, respectively: Provider ID, Provider Name,
Provider Type, Person or Thing, Printed Name.
□ 1. Open the query you created in Exercise 2-4 – SELECTing Columns.
SELECT
PROV_ID
,PROV_NAME
,PROV_TYPE
,STAFF_RESOURCE_C
,EXTERNAL_NAME
FROM
CLARITY_SER

□ 2. Modify the FROM clause by adding the alias “ser” after the
CLARITY_SER. Your FROM statement should look like this:
FROM
CLARITY_SER ser

□ 3. Modify the SELECT clause by adding the table alias “ser” to each of the
columns. For example, the first column in your SELECT clause should
look like this:
ser.PROV_ID

□ 4. Verify that your query still runs.

□ 5. Modify the SELECT clause by adding the column aliases as indicated in
the exercise summary above. For example, the first column in your
SELECT clause should look like this:
ser.PROV_ID "Provider ID"

□ 6. Add spacing between your column names and column aliases to make
your query easier to read.
Exercise 11: If Time Permits – Getting a List of Hospital
Encounters
Create and run a query to display the PAT_ID, PAT_ENC_CSN_ID,
ADMISSION_PROV_ID, DISCHARGE_PROV_ID, DEPARTMENT_ID,
HOSP_ADMSN_TIME, and HOSP_DISCH_TIME columns from the
PAT_ENC_HSP table. Use the alias “peh” for the PAT_ENC_HSP table.

Clarity SQL Fundamentals Epic 2017 Training Companion

2•14
Creating a Basic Query

SELECTing Constants
You can use the SELECT list to show a constant value, calculated values, or data
from columns in tables. We’ll investigate each of these different uses throughout
the class, but we’ll start with constant values first.
You can use SELECT followed by a comma delimited list of constant values,
such as numbers and strings. If you do not include a FROM clause, this query will
return a single row, with each value stored in its own column. For example,
SELECT
1
,2
,'Cogito Rocks'
produces the output

By itself, this isn’t a particularly useful query since it doesn’t actually pull any
data from the database. However, including constants in the SELECT list
becomes more useful when combined with other data from the database.
In Oracle SQL, to select a constant, you do need a FROM clause. There is a
table named DUAL which exists for this purpose, as in the example:
SELECT 1, 2, 'Cogito Rocks' FROM DUAL;

Selecting constants can be useful for adding labels to the data in your results grid.
Consider the following query:
SELECT
'Providers and Resources'
,'SER ID: ' + ser.PROV_ID "Provider ID"
,'Record Name: ' + ser.PROV_NAME "Provider Name"
FROM
CLARITY_SER ser

This would produce the following result grid:

Clarity SQL Fundamentals Epic 2017 Training Companion

2•15
Creating a Basic Query

Exercise 12: Exploring Queries with Constants

□ 1. Enter the following text into the Query Editor and execute the query.
SELECT
'DEP ID: ' + DEPARTMENT_ID
,'Record Name: ' + DEPARTMENT_NAME
FROM
CLARITY_DEP
□ 2. Did the query run? If not, what error was returned?

SQL won’t combine data of different data types. DEPARTMENT_ID is

numeric, while the constant ‘DEP ID: ’ is a varchar (string).

□ 3. Alter the query to the following and execute it.

SELECT
'DEP ID: '
,DEPARTMENT_ID
,'Record Name: ' + DEPARTMENT_NAME
FROM
CLARITY_DEP
□ 4. Did the query run?

Exercise 13: If Time Permits – More Exploring Constants

□ 1. Enter the following text into the Query Editor and execute the query.
SELECT
NULL
,10+23
,'string' + 5
,'another' + ' string'
,3*5
,55-100
□ 2. Did the query run? If not, what error was returned?

Clarity SQL Fundamentals Epic 2017 Training Companion

2•16
Creating a Basic Query

□ 3. Alter the query to the following and execute it.

SELECT
NULL
,10+23
,'string' + '5'
,'another' + ' string'
,3*5
,55-100
□ 4. Did the query run?

Putting single quotes (' ') around something indicates that it should be
treated as a varchar (string), even if its contents are otherwise numeric.

Clarity SQL Fundamentals Epic 2017 Training Companion

2•17
Creating a Basic Query

Reviewing the Chapter

Review Questions

1. Which clause in a SELECT statement is evaluated first?

2. True or False: It is possible for a SELECT statement to return values

that are not in the database.

3. True or False: Comments must be on their own line of the query.

4. What is the syntax for a block (multi-line) comment?

5. What technique can be used to rename tables and columns in a SQL

query?

6. What is one typical use case for SELECT TOP?

Clarity SQL Fundamentals Epic 2017 Training Companion

2•18
Creating a Basic Query

Review Key
1. Which clause in a SELECT statement is evaluated first?

The FROM clause

2. True or False: It is possible for a SELECT statement to return values

that are not in the database.

True

3. True or False: Comments must be on their own line of the query.

False

4. What is the syntax for a block (multi-line) comment?

/* Comment goes

here */

5. What technique can be used to rename tables and columns in a SQL

query?

Aliasing

6. What is one typical use case for SELECT TOP?

SELECT TOP is used for spot checking results of a query

Clarity SQL Fundamentals Epic 2017 Training Companion

2•19
Creating a Basic Query

Exercise Answers
See the Cogito’s Introduction to Epic’s Certification Environments appendix to
access the answer key files.

Exercise 8: Explore SELECT DISTINCT

□ 2. Do you know how many instances of each value are in the database?
No, you can’t tell how many instances of each value are in the database, only
what the different values are.
□ 3. Is this an exhaustive list of possible values for this column?
No. This is a varchar field, so any short string would be possible. This query only
finds values currently in the database.
□ 4. What is the difference between SELECT and SELECT DISTINCT if
used with a primary key?
There is no difference. A primary key must be unique.

Exercise 12: Exploring Queries with Constants

□ 2. Did the query run? If not, what error was returned?
No. Conversion failed when converting the varchar value ‘string’ to data type
numeric.
□ 4. Did the query run?
Yes

Exercise 13: If Time Permits – More Exploring Constants

□ 2. Did the query run? If not, what error was returned?
No. Conversion failed when converting the varchar value 'string' to data
type int.

□ 4. Did the query run?

Yes

Clarity SQL Fundamentals Epic 2017 Training Companion

2•20
Creating a Basic Query

Study Checklist
 Make sure you can define the following key terms:
 SQL query
 SELECT clause
 FROM clause
 Alias
 Make sure you can perform the following tasks:
 Create a SQL query to list all of the rows and columns in a table
 Add comments to a SQL query
 Create a SQL query to list data from specific columns in a table
 Create a SQL query to limit the number of rows returned
 Create a SQL query to return only unique values for given column
 Create a SQL query that uses aliases

Clarity SQL Fundamentals Epic 2017 Training Companion

2•21
Creating a Basic Query

Clarity SQL Fundamentals Epic 2017 Training Companion

©2017 Epic Systems Corporation. Confidential
Lesson 3
Logical Expressions
Logical Expressions 3
By the End of This Lesson, You Will Be Able to… 3
The Big Picture 4
Logical Expressions 5
Three Value Logic 6
Finding Nulls 7
Negating Expressions 7
Exercise 1: Using Logical Expressions 8
Exercise 2: If Time Permits – More Logical Expressions 10
Applying Logical Expressions 11
The Case for Case – Case/When 11
Exercise 3: Using a CASE Statement 13
Exercise 4: If Time Permits – More CASE Statements 14
 Medications 14
 Ambulatory Encounters 14
 Professional Billing 14
The WHERE Clause 15
Exercise 5: Unknowns 16
Exercise 6: If Time Permits – Write Queries with WHERE 17
 Clinical 17
 Access 17
 Revenue 17
 Cogito 17
3•2
Logical Expressions

Using Multiple Conditions 18

Taking Both – And 18
One or the Other – Or 19
Exercise 7: Combining Criteria with And 20
Exercise 8: Combining Criteria with OR 21
In-Class Exercises 23
Exercise 9: Logical Expressions 23
Exercise 10: Filtering 23
Exercise 11: Filtering your Hospital Admissions Report 23
Exercise 12: If Time Permits – Filtering your Estimated Date of Delivery
Report 24
Exercise 13: If Time Permits – Combine Criteria with NOT 24
Indexing 26
Reviewing the Chapter 28
Review Questions 28
Review Key 29
Exercise Answers 30
Exercise 1: Using Logical Expressions 30
Exercise 2: If Time Permits – More Logical Expressions 32
Exercise 5: Unknowns 33
Exercise 7: Combining Criteria with And 33
Exercise 8: Combining Criteria with OR 34
Exercise 9: Logical Expressions 34
Exercise 13: If Time Permits – Combine Criteria with NOT 35
Study Checklist 36

Clarity SQL Fundamentals Epic 2017 Training Companion

3•3
Logical Expressions

Logical Expressions
Writing queries to select data from a table is often only the first part of writing a
report. Most of the time, there is specific information that you want to get out of
the tables. For instance, the PAT_ENC table contains one row for each patient
encounter. But what if you are only interested in office visits, or telephone
encounters? What if you are asked to write a report to show only patients with a
specific diagnosis in the last year, as opposed to a list of all patients? Consider a
report requesting only hospital encounters where the admitting provider and the
discharge provider were the same person; how would you go about doing that? In
this chapter, you’ll investigate how to express which rows in a table you seek.

By the End of This Lesson, You Will Be Able to…

 Create a display column with values dependent on certain conditions

 Filter results based on one column’s data
 Filter results based on multiple columns’ data
 Describe the purpose of an indexed column
 Investigate the indexed columns of a given table

Clarity SQL Fundamentals Epic 2017 Training Companion

3•4
Logical Expressions

The Big Picture

PATIENT
PAT_ID PAT_NAME
1 Jewel
2 Neil
3 Nathan
4 Felicia

PATIENT
PAT_ID PAT_NAME
1 Jewel

PATIENT
PAT_ID PAT_NAME
2 Neil
3 Nathan
4 Felicia

Clarity SQL Fundamentals Epic 2017 Training Companion

3•5
Logical Expressions

Logical Expressions
The fundamental task in report writing is to ensure that all of the needed data and
no unnecessary data appears on a report. This is accomplished by making
statements about the results you would like to find. These statements are called
logical expressions.

Three traits of logical expressions

Logical expressions are made up of one or more values and an operator. The
values can be constants or fields from the database. The most common form for
logical expressions is the following:
[column] [operator] [value]
Here are a few examples of logical expressions:
o PATIENT.PAT_NAME = 'Johnson, James'
o PAT_ENC.APPT_DTTM >= '01/01/2015'
o HSP_TRANSACTIONS.TX_AMOUNT between 1000 and 9999
o ORDER_MED.DISCON_TIME is null
The data types on both sides of the operator must match or be such that the server
can automatically convert from one to the other. SQL may not know whether
April 20, 1984 is larger than the number 4011980, nor can it tell if the word
“Fifteen” equals the number 15. Below are many types of comparison operators
you may see in logical expressions. Keep in mind that the [value] on the right
side can often be replaced with another [column].
Operator Description
[column] = [value] Equal
[column] <> [value] Not equal
[column] < [value] Less than
[column] <= [value] Less than or equal
[column] > [value] Greater than

Clarity SQL Fundamentals Epic 2017 Training Companion

3•6
Logical Expressions

[column] >= [value] Greater than or equal

[column] BETWEEN [value1] AND Greater than or equal to [value1],
[value2]
less than or equal to [value2].
[column] IN ([value1],[value2],…)
Equal to one of several values
[column] LIKE [pattern] Matches a pattern. Pattern can
[column] NOT LIKE [pattern] include an underscore “_” meaning
“any character” and a percent sign
“%” meaning “any string of
characters”
[column] IS NULL Null value check
[column] IS NOT NULL Non-null value check

Three Value Logic

SQL uses a logical system known as Three Value Logic. All logical expressions
evaluate to either TRUE, FALSE, or UNKNOWN. You may be familiar with the
first two, but it is important to account for the possibility of UNKNOWN.
Consider the following expression:
PATIENT.CITY = 'Verona'
In plain English, this expression says “The patient lives in Verona.” For a patient
who lives in Verona, this statement would be TRUE. For a patient who lives
somewhere other than Verona, this statement would be FALSE. However, several
patients have NULL in CITY. You don’t know if these patients live or don’t live in
Verona. There isn’t enough data to say for sure. When there is insufficient data to
make the comparison, the statement evaluates to UNKNOWN.
Here are some examples from earlier:
PATIENT.PAT_NAME = 'Johnson, James'
--This statement will be TRUE for any patient named
--'Johnson, James', FALSE for any patient with a different name,
--and UNKNOWN for any patient with NULL in the PAT_NAME field.

PAT_ENC.APPT_DTTM >= '01/01/2015'

/*This statement will be TRUE for any encounter on or after
January 1st, 2015, FALSE for any encounter earlier than January
1st, 2015, and UNKNOWN for any encounter with NULL in
APPT_DTTM.*/

HSP_TRANSACTIONS.TX_AMOUNT between 1000 and 9999

--This statement will be TRUE for any transaction between $1000
--and $9999, FALSE for any transaction outside of that range,
--and UNKNOWN for any transaction with NULL in TX_AMOUNT.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•7
Logical Expressions

Finding Nulls
NULL is never equal to anything, even itself. When NULL appears in a
comparison, there isn’t enough data to compute the expression. Consider the
following expression:
PATIENT.CITY = NULL
This expression is never TRUE and never FALSE, but rather is always
UNKNOWN. There is never enough data to perform the comparison; the right
side of the expression is always NULL. To find NULL values, the following
expression would be appropriate:
PATIENT.CITY IS NULL

A special operator called IS is used to check if a field is NULL. The expression

[field] IS NULL will always evaluate to TRUE for NULL fields and FALSE for
populated fields. This expression cannot be UNKNOWN.
Here’s another example from earlier:
ORDER_MED.DISCON_TIME IS NULL
/*This expression will be TRUE for any order without a value for
discontinued time and FALSE for any order with a value for
discontinued time. This expression can never be UNKNOWN.*/

Negating Expressions

When working with logical expressions, the word NOT can be added to reverse
TRUE and FALSE statements. However, UNKNOWN differs from FALSE in
that the statement NOT FALSE evaluates to TRUE, while the statement NOT
UNKNOWN evaluates to UNKNOWN.
Consider this expression from an earlier example:
NOT (PATIENT.CITY = 'Verona')
For the patients who live in Verona, this expression is now FALSE. For patients
who live in a city other than Verona, this statement is now TRUE. But for patients
with a NULL city, this expression is still UNKNOWN. If the patient has no
documented city, you don’t know if the patient lives in Verona. You also don’t
know if the patient does NOT live in Verona.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•8
Logical Expressions

Exercise 1: Using Logical Expressions

Consider the following set of patients. Then, for each logical expression below,
mark TRUE, FALSE, or UNKNOWN for each patient.
PAT_ID PAT_NAME BIRTH_DATE ZIP PAT_STATUS_C
Z1 THIRA, LAURA 1987-03-17 53719 1
Z2 ZARVEN, BRIAN 1985-01-22 48160 1
Z3 CORWIN, ANDY 1990-05-17 53720 <NULL>
Z4 KARA, CONNIE 1986-07-11 48104 2
Z5 <NULL> <NULL> 53719 <NULL>

For example, for the expression PAT_ID = 'Z2':

PAT_ID PAT_ID = 'Z2'
Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

□ 1. PAT_ID <> 'Z3'

PAT_ID PAT_ID <> 'Z3'

Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

□ 2. ZIP LIKE '537__' --2 underscores

PAT_ID ZIP ZIP LIKE '537__'

Z1 53719 TRUE FALSE UNKNOWN
Z2 48160 TRUE FALSE UNKNOWN
Z3 53720 TRUE FALSE UNKNOWN
Z4 48104 TRUE FALSE UNKNOWN
Z5 53719 TRUE FALSE UNKNOWN

Clarity SQL Fundamentals Epic 2017 Training Companion

3•9
Logical Expressions

□ 3. PAT_STATUS_C IS NULL

PAT_ID PAT_STATUS_C PAT_STATUS_C IS NULL

Z1 1 TRUE FALSE UNKNOWN
Z2 1 TRUE FALSE UNKNOWN
Z3 <NULL> TRUE FALSE UNKNOWN
Z4 2 TRUE FALSE UNKNOWN
Z5 <NULL> TRUE FALSE UNKNOWN

□ 4. BIRTH_DATE < '1990-01-01'

PAT_ID BIRTH_DATE BIRTH_DATE < '1990-01-01'

Z1 1987-03-17 TRUE FALSE UNKNOWN
Z2 1985-01-22 TRUE FALSE UNKNOWN
Z3 1990-05-17 TRUE FALSE UNKNOWN
Z4 1986-07-11 TRUE FALSE UNKNOWN
Z5 <NULL> TRUE FALSE UNKNOWN

□ 5. PAT_STATUS_C <> '1'

PAT_ID PAT_STATUS_C PAT_STATUS_C <> '1'

Z1 1 TRUE FALSE UNKNOWN
Z2 1 TRUE FALSE UNKNOWN
Z3 <NULL> TRUE FALSE UNKNOWN
Z4 2 TRUE FALSE UNKNOWN
Z5 <NULL> TRUE FALSE UNKNOWN

□ 6. PAT_STATUS_C <> NULL

PAT_ID PAT_STATUS_C PAT_STATUS_C <> NULL

Z1 1 TRUE FALSE UNKNOWN
Z2 1 TRUE FALSE UNKNOWN
Z3 <NULL> TRUE FALSE UNKNOWN
Z4 2 TRUE FALSE UNKNOWN
Z5 <NULL> TRUE FALSE UNKNOWN

Clarity SQL Fundamentals Epic 2017 Training Companion

3•10
Logical Expressions

Exercise 2: If Time Permits – More Logical Expressions

Consider the following set of patients. Then, for each logical expression below,
mark TRUE, FALSE, or UNKNOWN for each patient.
PAT_ID PAT_NAME BIRTH_DATE ZIP PAT_STATUS_C
Z1 THIRA, LAURA 1987-03-17 53719 1
Z2 ZARVEN, BRIAN 1985-01-22 48160 1
Z3 CORWIN, ANDY 1990-05-17 53720 <NULL>
Z4 KARA, CONNIE 1986-07-11 48104 2
Z5 <NULL> <NULL> 53719 <NULL>

□ 1. PAT_NAME LIKE '%'

PAT_ID PAT_NAME PAT_NAME LIKE '%'

Z1 THIRA, LAURA TRUE FALSE UNKNOWN
Z2 ZARVEN, BRIAN TRUE FALSE UNKNOWN
Z3 CORWIN, ANDY TRUE FALSE UNKNOWN
Z4 KARA, CONNIE TRUE FALSE UNKNOWN
Z5 <NULL> TRUE FALSE UNKNOWN
□ 2. BIRTH_DATE between '1985-01-22' and '1987-03-17'

PAT_ID BIRTH_DATE BIRTH_DATE between '1985-01-22'

and '1987-03-17'
Z1 1987-03-17 TRUE FALSE UNKNOWN
Z2 1985-01-22 TRUE FALSE UNKNOWN
Z3 1990-05-17 TRUE FALSE UNKNOWN
Z4 1986-07-11 TRUE FALSE UNKNOWN
Z5 <NULL> TRUE FALSE UNKNOWN
□ 3. PAT_STATUS_C in (1,2,NULL)

PAT_ID PAT_STATUS_C PAT_STATUS_C in (1,2,NULL)

Z1 1 TRUE FALSE UNKNOWN
Z2 1 TRUE FALSE UNKNOWN
Z3 <NULL> TRUE FALSE UNKNOWN
Z4 2 TRUE FALSE UNKNOWN
Z5 <NULL> TRUE FALSE UNKNOWN

Clarity SQL Fundamentals Epic 2017 Training Companion

3•11
Logical Expressions

Applying Logical Expressions

There are several places you can find logical expressions in a SQL query. In this
lesson, you’ll see two: CASE statements and the WHERE clause.
The Case for Case – Case/When
It is possible, and sometimes necessary, to add in options for what to display.
CASE statements allow you to use logical expressions to determine values to
display in your results grid. There are two formats for a CASE statement. The first
involves choosing each option based on logical expressions.
CASE
WHEN [logical expression]
THEN [value 1]
WHEN [logical expression]
THEN [value 2]
/* ... */
ELSE [default value]
END
/* ... */

The CASE statement will return the [value] associated with the first TRUE
[logical expression]. The case statement typically appears as a column in
the select list. Note the END, which is required with all CASE statements.
You are writing a report on encounters and want to identify
encounters that patients had with their primary care providers.
There is no column in PAT_ENC to show whether a visit was with the patient’s
PCP. However, there is a column for VISIT_PROV_ID, and another column for
PCP_PROV_ID. So, you can use a CASE statement to identify these encounters:
SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,VISIT_PROV_ID
,PCP_PROV_ID
,CASE
WHEN VISIT_PROV_ID = PCP_PROV_ID
THEN 'Visit with PCP'
WHEN VISIT_PROV_ID <> PCP_PROV_ID
THEN 'Visit with other provider'
ELSE 'Missing Data'
END
FROM
PAT_ENC

Clarity SQL Fundamentals Epic 2017 Training Companion

3•12
Logical Expressions

The CASE statement can also specify a column or formula to compare against
possible values. This second format limits you to comparing values against only a
single static value or column. The implicit operator for the comparison is =.

CASE [column]
WHEN [value 1]
THEN [option 1]
WHEN [value 2]
THEN [option 2]
/* ... */
ELSE [default value]
END
/* ... */
For example, the following two queries accomplish the same task of converting
the IS_RESIDENT column in the CLARITY_SER table into a friendlier format.

SELECT
ser.PROV_ID
,ser.IS_RESIDENT
,CASE --Choose one of the following:
WHEN ser.IS_RESIDENT='Y' --The provider is a resident
THEN 'Resident'
WHEN ser.IS_RESIDENT = 'N' --The provider is not a resident
THEN 'Not a Resident'
ELSE 'No Value' --Default value
END "Interpretation" --End, and alias the column
FROM CLARITY_SER ser

In SQL, string constants such as 'Y' and 'Resident' are always placed
within single quotes. Unlike in Crystal Reports syntax, double and single
quotes perform different tasks in SQL. Double quotes are used for table
names, column names, and aliases such as "Provider ID".

Using the second format, the above statement can also be written like this:
SELECT
ser.PROV_ID
,ser.IS_RESIDENT
,CASE ser.IS_RESIDENT --Look at the value of this column
WHEN 'Y' --Is the value 'Y'
THEN 'Resident'
WHEN 'N' --Is the value 'N'
THEN 'Not a Resident'
ELSE 'No Value' --Default value
END "Interpretation" --End, and alias the column
FROM CLARITY_SER ser

Clarity SQL Fundamentals Epic 2017 Training Companion

3•13
Logical Expressions

Exercise 3: Using a CASE Statement

Write a SQL query to display patient IDs, patient names, patient cities, and patient
birth dates. Then add a result column to show whether each patient is a pediatric
patient or an adult patient. For the purposes of this exercise, a pediatric patient is
someone whose birth date is later than the date 18 years ago.

In MS-SQL, date constants are written in single quotes. There are many
different formats accepted, like '1/2/2016' and 'Jan 2, 2016'

Clarity SQL Fundamentals Epic 2017 Training Companion

3•14
Logical Expressions

Exercise 4: If Time Permits – More CASE Statements

Choose at least one of the following queries to write. You will need to do some
research in the Clarity Compass to find the necessary tables and columns.

 Medications
Write a query to display a list of medications. Medications are stored in the ERX
master file. Display the medication ID and the medication name. Then add a
result column for whether the medication is a mixture. A medication is a mixture
if the item ERX 405 is populated (not NULL).

 Ambulatory Encounters
Write a query to display the reason for visit from patient encounters. Display the
encounter CSN and the contact date. Then add a result column for the reason for
visit, but if there is a custom reason for visit, display the custom reason entered
instead. The category item for reason for visit is EPT 18100.

 Professional Billing
Write a query on professional billing transactions. Display the transaction ID, the
original amount as defined by the item ETR 17300, and the post date. Then add a
result column for an aging bucket for the transaction. If the post date was less than
30 days ago, this column should display ‘0-30’. If the post date was 31-60 days
ago, display ’31-60’. If the post date was more than 60 days ago, display ‘more
than 60.’ You may hard-code all relevant dates.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•15
Logical Expressions

The WHERE Clause

The WHERE clause filters data in your query. The WHERE clause holds one
logical expression. You use the logical expression to describe which rows should
be returned in your results.
Your WHERE clause is evaluated for each row of the included tables, and its
logical expression must evaluate to TRUE for a row in order for that row to
appear in your results. If the expression evaluates to FALSE or UNKNOWN, the
data will be excluded from your results grid.
Imagine you wanted to only find patients that lived in Verona. You could use this
query:
SELECT
*
FROM
PATIENT
WHERE
CITY = 'Verona'
Instead of returning all patients, this returns only patients that live in Verona.
Patients who live elsewhere, and patients with a NULL city, will be excluded.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•16
Logical Expressions

Exercise 5: Unknowns
□ 1. Run the following query. How many results do you get?
SELECT
*
FROM
PATIENT
WHERE
(CUR_PCP_PROV_ID = NULL)

□ 2. Run the following query. How many results do you get?

SELECT
*
FROM
PATIENT
WHERE
NOT (CUR_PCP_PROV_ID = NULL)

□ 3. Run the following query. How many results do you get?

SELECT
*
FROM
PATIENT
WHERE
CUR_PCP_PROV_ID IS NULL

□ 4. Run the following query. How many results do you get?

SELECT
*
FROM
PATIENT
WHERE
NOT (CUR_PCP_PROV_ID IS NULL)

□ 5. In SQL, can you use an equal sign (=) to find null values? Why did you
get the same number of results in the first two queries?

Clarity SQL Fundamentals Epic 2017 Training Companion

3•17
Logical Expressions

Exercise 6: If Time Permits – Write Queries with WHERE

Choose at least one of the following queries to write. You will need to do some
research in the Clarity Compass to find the necessary tables and columns.

 Clinical
Write a query that displays a list of the diagnoses in your system. Display the
diagnosis ID, diagnosis name, and the list of current ICD10 codes. Do not include
diagnoses without any ICD-10 codes. ICD-10 codes can be found in the item
EDG 4104.

 Access
Write a query to find admission events (as defined by the item ADT 30). Display
the event ID, the event time, and the patient CSN.

 Revenue
Find all guarantor accounts with a balance in the item EAR 20000. Display the
balance, the account ID, and the account name.

 Cogito
Create a list of all the tables in Clarity that have been deprecated. Display the
table name, table ID, and table introduction. Clarity table metadata is stored in the
E0B master file.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•18
Logical Expressions

Using Multiple Conditions

It is possible to take multiple logical expressions and combine them to generate a
more complex filter by using logical operators between the expressions. As long
as the end result of the compound expression is true, false, or unknown, your
expression can be as complex as you like.
When using a number of logical operators to connect statements in a filter, the use
of parentheses is crucial to ensuring that the query interprets the filter as intended.
When evaluating a complex filter, the innermost parentheses are evaluated first.
Without parentheses, NOT is evaluated first, then AND, then OR.
A common mistake is to try to combine multiple values using logical operators,
for example PATIENT.SEX_C = ‘1’ OR ‘2’. This is not correct. Logical
operators can only be used to combine whole logical expressions. The correct
syntax here would be PATIENT.SEX_C = ‘1’ OR PATIENT.SEX_C = ‘2’.
Taking Both – And
When a report needs multiple filters to be respected simultaneously, the AND
operator may be used. Rows will only be returned from the query if the statements
on both sides of AND evaluate to true for that record.
Consider a report request to show all males of a certain age. In an
upcoming chapter, you’ll look at how to use formulas to find dates relative
to today, but for now, you will just hard code the date. Write a query to
return all males who were born before 1984.

SELECT
pat.PAT_ID
,pat.PAT_NAME
FROM
PATIENT pat
WHERE
pat.SEX_C = '2' --Patient is Male
AND
pat.BIRTH_DATE < '1984-01-01' --Patient was born before 1984
If both pieces of an AND expression are true, then the result of the AND is true. If
either piece is false, then the expression is false. True AND unknown, as well as
unknown AND unknown, will both evaluate to unknown.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•19
Logical Expressions

One or the Other – Or

When a report only needs at least one filter among a set to be respected, the OR
operator may be used. Rows will be returned from the query so long as one or
more of the individual filters evaluate to true.
Patient contacts are given a contact type. The contact types are stored in a
category list to which your organization has made changes. At your
organization, instead of just one “office visit”, the application team has
decided to build out two more, one for new patients and one for VIP
patients. You’ve been asked to write a report of patient encounters that
include all of those types of office visits.

SELECT
pe.PAT_ID
,pe.CONTACT_DATE
,pe.ENC_TYPE_C
FROM
PAT_ENC pe
WHERE
pe.ENC_TYPE_C = 101 --Generic 'Office Visit'
OR pe.ENC_TYPE_C = 1134 --Office Visit for new patient
OR pe.ENC_TYPE_C = 1135 --Office Visit for vip patient

If either piece of an OR expression is true, then the result of the OR is true. If both
pieces are false, then the expression is false. False OR unknown and unknown OR
unknown will evaluate to unknown.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•20
Logical Expressions

Exercise 7: Combining Criteria with AND

Imagine you’ve written the following query:
SELECT
*
FROM
PATIENT pat
WHERE
pat.SEX_C = '2' --Patient is Male
AND
pat.BIRTH_DATE < '1984-01-01'

Consider patients with different combinations of sexes and birth dates. Fill out the
table below with a “Y” if the patient will be included in the results of the above
query or an “N” if the patient will not be included.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•21
Logical Expressions

Exercise 8: Combining Criteria with OR

You’ve changed your query from Exercise 7 to read as follows:
SELECT
*
FROM
PATIENT pat
WHERE
pat.SEX_C = '2' --Patient is Male
OR
pat.BIRTH_DATE < '1984-01-01'

Fill out the same table, remembering the difference between AND and OR logic.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•22
Logical Expressions

In SQL, a combination of logical expressions will evaluate as a whole to TRUE,

FALSE, or UNKNOWN.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•23
Logical Expressions

In-Class Exercises

Exercise 9: Logical Expressions

A, B, C, and D are logical expressions.
A is true B is false
C is true D is unknown
For each combined expression below, determine whether the statement is True,
False, or Unknown.
□ 1. (A AND B) OR C
□ 2. A AND (B OR C)
□ 3. (A AND D) OR (B AND C)
□ 4. (A AND (B OR C) AND D) OR (A OR NOT(B AND D))

Exercise 10: Filtering

□ 1. Write a query to show a list of male providers/resources.
□ 2. Write a query to show a list of non-male providers/resources.
□ 3. Write a query to show a list of surgeries that were scheduled to take place
in 2012. The date a surgery was scheduled to take place is stored in
Chronicles item ORC 40.
□ 4. Write a query to show all specimens (from the table SPEC_DB_MAIN)
where the patient to whom this specimen belongs has the ID of Z24891.

Exercise 11: Filtering your Hospital Admissions Report

Write a query to display a list of hospital encounters with the admission date in
2016 using the PAT_ENC_HSP table. Show the patient’s ID, the admitting
provider’s ID, the discharge provider’s ID, the department ID, the admission time,
and the discharge time. Save this query for the next chapter.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•24
Logical Expressions

Exercise 12: If Time Permits – Filtering your Estimated

Date of Delivery Report
In Exercise 2-3, you wrote a query to find the estimated date of delivery (EDD)
for pregnancy episodes. However, there are many types of episodes in the
EPISODE table. You learn that pregnancy episodes have a type of 2 as defined by
I HSB 30. Write a query to display the EDD for pregnancy episodes only.

Exercise 13: If Time Permits – Combine Criteria with NOT

SQL uses three-value logic. The statement SEX_C = '2' is true for each patient
who is listed as male, false for each patient who is listed as female, and unknown
for each patient whose sex is NULL. The logical operator NOT is often used
before statements like these. NOT makes a true statement false and a false
statement true. But, NOT doesn’t have any effect on unknowns; NOT unknown is
still unknown. With that in mind, imagine you’ve written the following query:
SELECT
*
FROM
PATIENT pat
WHERE
NOT (
pat.SEX_C = '2' --Patient is Male
AND
pat.BIRTH_DATE < '1984-01-01'
)
Fill out the table below with a “Y” or an “N” for whether a patient would be
included in these results.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•25
Logical Expressions

You’ve changed your query from before to read as follows:

SELECT
*
FROM
PATIENT pat
WHERE
NOT (
pat.SEX_C = '2' --Patient is Male
OR
pat.BIRTH_DATE < '1984-01-01')
)
Fill out the table again, accounting for the difference between AND and OR logic.

In this exercise you basically derived the following rules; you may find it
convenient to refer back to these formal statements of them in the future:
 NOT (A OR B) = (NOT A) AND (NOT B)
 NOT (A AND B) = (NOT A) OR (NOT B)

Clarity SQL Fundamentals Epic 2017 Training Companion

3•26
Logical Expressions

Indexing
Indexes can make your reports run faster. In order to use an index, you first have
to include an indexed column in the query. Indexed columns can improve query
performance when used in the WHERE clause. They also can improve performance
if used in the ON statement of a table join, which you will investigate later.
Note the use of the word “can” here, as opposed to “will.” SQL is a
declarative language, meaning that you use it to describe the results you
seek without actually specifying how to go about getting those results.
Using indexes in your queries gives the database an opportunity to
leverage them to improve performance, but there is no guarantee that it
will actually do so.

You can find a list of documented indexes in the Clarity Compass in the Table
Summary screen.

Index information in the Clarity Compass

Note, this is not a full list of indexes, as your Clarity Administrator has the ability
to add new indexes to the database, and the Clarity Compass does not contain an
exhaustive list. In Microsoft SQL, you can run the command sp_help [TABLE]
to find a full list of indexed columns for a given table in the database.

Index information for the PATIENT table using sp_help. You will need to scroll to
find this informaiton in your results grid.
In Oracle, query the table DBA_IND_COLUMNS to find all existing
indexes for the tables in your database.

Clarity SQL Fundamentals Epic 2017 Training Companion

3•27
Logical Expressions

So what exactly is an index? If you wanted to find every patient who has Dr. Whitecoat
as their current general primary care provider, your first thought might be to search
through every row of the PATIENT table and check the CUR_PCP_PROV_ID to see if
Dr. Whitecoat’s ID appeared. This would be possible, but it might take a while. If you
work at a large facility it might take a very long while.

Patient Current General PCP

Amy Green Dr. Whitecoat

Kristi Schuler Dr. Silver

Martin Salame Dr. Whitecoat

Adam Carey Dr. Johnson

Melissa Ebhart Dr. Chen

… …

Information about patients and PCPs

Fortunately, PATIENT.CUR_PCP_PROV_ID is an indexed column, which

means the database can treat as if it were ordered in a pre-sorted form, sorted by
the provider’s ID. In this order, do you need to search the whole table?

Current General PCP Patient

Dr. Johnson Adam Carey

Dr. Whitecoat Amy Green

Dr. Whitecoat Martin Salame

Dr. Silver Kristi Schuler

Dr. Chen Melissa Ebhart

… …

Information about patients and PCPs, sorted by PCP (the index)

Clarity SQL Fundamentals Epic 2017 Training Companion

3•28
Logical Expressions

Reviewing the Chapter

Review Questions

1. What does a filter do to the results of a query?

2. What are the only possible results of evaluating a logical expression?

3. A is true. B is true. C is false. Does the following logic evaluate to be

True or False?
((A and C) or (B or C)) and ((A and B) or C)

Clarity SQL Fundamentals Epic 2017 Training Companion

3•29
Logical Expressions

Review Key

1. What does a filter do to the results of a query?

A filter reduces the number of results by eliminating incorrect or

inappropriate results

2. What are the only possible results of evaluating a logical expression?

True, False, or Unknown

3. A is true. B is true. C is false. Does the following logic evaluate to be

True or False?
((A and C) or (B or C)) and ((A and B) or C)

((True and False) or (True or False)) and ((True and True) or False)
 (False or True) and (True or False)
 True and True
 True

Clarity SQL Fundamentals Epic 2017 Training Companion

3•30
Logical Expressions

Exercise Answers
See the Cogito’s Introduction to Epic’s Certification Environments appendix to
access the answer key files.

Exercise 1: Using Logical Expressions

Consider the following set of patients. Then, for each logical expression below,
mark TRUE, FALSE, or UNKNOWN for each patient.
PAT_ID PAT_NAME BIRTH_DATE ZIP PAT_STATUS_C
Z1 THIRA, LAURA 1987-03-17 53719 1
Z2 ZARVEN, BRIAN 1985-01-22 48160 1
Z3 CORWIN, ANDY 1990-05-17 53720 <NULL>
Z4 KARA, CONNIE 1986-07-11 48104 2
Z5 <NULL> <NULL> 53719 <NULL>

□ 1. PAT_ID <> 'Z3'

PAT_ID PAT_ID <> 'Z3'

Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

□ 2. ZIP LIKE '537__' --2 underscores

PAT_ID ZIP LIKE '537__'

Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

Clarity SQL Fundamentals Epic 2017 Training Companion

3•31
Logical Expressions

□ 3. PAT_STATUS_C IS NULL

PAT_ID PAT_STATUS_C IS NULL

Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

□ 4. BIRTH_DATE < '1990-01-01'

PAT_ID BIRTH_DATE < '1990-01-01'

Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

□ 5. PAT_STATUS_C <> '1'

PAT_ID PAT_STATUS_C <> '1'

Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

□ 6. PAT_STATUS_C <> NULL

PAT_ID PAT_STATUS_C <> NULL

Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

Clarity SQL Fundamentals Epic 2017 Training Companion

3•32
Logical Expressions

Exercise 2: If Time Permits – More Logical Expressions

Consider the following set of patients. Then, for each logical expression below,
mark TRUE, FALSE, or UNKNOWN for each patient.
PAT_ID PAT_NAME BIRTH_DATE ZIP PAT_STATUS_C
Z1 THIRA, LAURA 1987-03-17 53719 1
Z2 ZARVEN, BRIAN 1985-01-22 48160 1
Z3 CORWIN, ANDY 1990-05-17 53720 <NULL>
Z4 KARA, CONNIE 1986-07-11 48104 2
Z5 <NULL> <NULL> 53719 <NULL>

□ 1. PAT_NAME LIKE '%'

PAT_ID PAT_NAME LIKE '%'

Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

□ 2. BIRTH_DATE between '1985-01-22' and '1987-03-17'

PAT_ID BIRTH_DATE between '1985-01-22'

and '1987-03-17'
Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

□ 3. PAT_STATUS_C in (1,2,NULL)

PAT_ID PAT_STATUS_C in (1,2,NULL)

Z1 TRUE FALSE UNKNOWN
Z2 TRUE FALSE UNKNOWN
Z3 TRUE FALSE UNKNOWN
Z4 TRUE FALSE UNKNOWN
Z5 TRUE FALSE UNKNOWN

Clarity SQL Fundamentals Epic 2017 Training Companion

3•33
Logical Expressions

Exercise 5: Unknowns
□ 1. Run the following query. How many results do you get? 0
□ 2. Run the following query. How many results do you get? 0
□ 3. Run the following query. How many results do you get? (Several. Since
the training database updates periodically, the record count may vary.)
□ 4. Run the following query. How many results do you get? (Several. Since
the training database updates periodically, the record count may vary.)
□ 5. In SQL, should you use an equal sign (=) to find null values? Why did you
get the same number of results in the first two queries? No, you should use
the IS operator instead. If you use =, the result will always be unknown,
and the opposite of unknown is unknown. Unknown results are excluded
from the WHERE clause.

Exercise 7: Combining Criteria with AND

Clarity SQL Fundamentals Epic 2017 Training Companion

3•34
Logical Expressions

Exercise 8: Combining Criteria with OR

Exercise 9: Logical Expressions

□ 1. True
□ 2. True
□ 3. Unknown
□ 4. True

Clarity SQL Fundamentals Epic 2017 Training Companion

3•35
Logical Expressions

Exercise 13: If Time Permits – Combine Criteria with NOT

Clarity SQL Fundamentals Epic 2017 Training Companion

3•36
Logical Expressions

Study Checklist
 Make sure you can define the following key terms:
 Logical Expression
 CASE statement
 WHERE clause
 Make sure you can perform the following tasks:
 Write an expression that compares a column to a value or another
column
 Combine multiple comparisons using logical operators
 View the indexes for a Clarity table
 Make sure you fully understand and can explain the following concepts:
 The difference between SELECT TOP and the WHERE clause
 Logical operators and complex logic
 Index

Clarity SQL Fundamentals Epic 2017 Training Companion

3•37
Logical Expressions

Clarity SQL Fundamentals Epic 2017 Training Companion

©2017 Epic Systems Corporation. Confidential
Lesson 4
Functions
Functions 3
By the End of This Lesson, You Will be Able to… 3
The Big Picture 4
Using Functions in SQL 5
Exercise 1: Functions and Arguments 5
Using Functions in the SELECT Statement 6
Using Functions in the WHERE Clause 7
SQL Functions 8
Exercise 2: Discovering Functions 8
 Conversion Functions 8
 Date and Time Functions 10
 String Functions 13
 Null Functions 14
 Functions from this lesson 16
Exercise 3: Using Functions with Dates 18
Exercise 4: If Time Permits – Hospital Admissions Report 19
Exercise 5: If Time Permits – Expanding the Hospital Admissions Report 19
Epic Standard SQL Functions 20
Date and Time Functions 20
String Functions 22
IntraConnect Functions 23
Advantages and Disadvantages of Epic Standard SQL Functions 24
4•2
Functions

Reference 25
Replace Epic Standard SQL Functions with Database-Specific Code 25
After-Class Exercises 27
Exercise 1: Patients with Upcoming Birthdays 27
Reviewing the Chapter 29
Review Questions 29
Review Key 30
Exercise Answers 31
Exercise 1: Functions and Arguments 31
Exercise 2: Discovering Functions 31
 Conversion Functions 31
 Date and Time Functions 32
 String Functions 34
 Null Functions 35
Exercise 3: Using Functions with Dates 36
Study Checklist 36

Clarity SQL Fundamentals Epic 2017 Training Companion

4•3
Functions

Functions
Generating reports by simply pulling data from the database is useful, but limited.
In this lesson, you will investigate different functions used in SQL queries. These
will allow you to add more functionality and flexibility to your reports, by
combining and manipulating data from the database in interesting ways.

By the End of This Lesson, You Will be Able to…

 Define the usages of various SQL functions

 Investigate the purpose of new SQL functions
 Use Epic Standard SQL functions
 Describe the advantages and disadvantages of Epic Standard SQL functions

Clarity SQL Fundamentals Epic 2017 Training Companion

4•4
Functions

The Big Picture

Clarity SQL Fundamentals Epic 2017 Training Companion

4•5
Functions

Using Functions in SQL

Many reports will require only that you select data from the database. However,
SQL gives you the ability to modify the data as you use it. This ability is provided
through the use of functions. You can think of a function as a snippet of code that
takes some number of inputs, also called arguments, and returns some sort of
output, also called a result.
Some functions don’t take any arguments, but will still return a value. For
example, CURRENT_TIMESTAMP returns the current date and time from the system.
This is incredibly useful in comparing dates and lengths of time, such as in reports
where you want to find all patients within a certain age range, or all new charges
from the last month.

Exercise 1: Functions and Arguments

□ 1. MONTH is a function that takes in one argument, a date, and returns the
month value from the date. Fill in the chart below. (Dates are given in the
format month/day/year.)
Date MONTH(date)
'01/01/2013 12:42'
'June 16 1964'
'9-4-85'
'May 31, 1990'
'10-21-2015'
□ 2. Some functions take multiple arguments. The DATEADD function takes
three: an interval (also called a datepart), a number, and a date. Here are
some of the more common intervals:
Datepart Meaning
d Day
m Month
ww Week
yy or yyyy Year
hh Hour
n Minute

Clarity SQL Fundamentals Epic 2017 Training Companion

4•6
Functions

DATEADD will take the number of the intervals (as defined by the
datepart) and add it to (or subtract it from) the date. Fill in the blanks in
the queries below:

select DATEADD(d,15,'01/01/2013 12:42') --Result = _______________________

select DATEADD(m,______,'June 16 1964') --Result = 1964-07-16 00:00:00.000

select DATEADD(______,-5,'9-4-85') --Result = 1980-09-04 00:00:00.000

select DATEADD(d,-3,'May 31,1990') --Result = _______________________

select DATEADD(yy,______,'2015-10-21') --Result = 1985-10-21 00:00:00.000

Using Functions in the SELECT Statement

When you use a function in the SELECT list, you are creating a new column for
your results. For example, in the query:
SELECT
CURRENT_TIMESTAMP "Current Date"

you create a column called Current Date, which contains the value of the date
and time on the server when the query is run.
A small number of functions which take no arguments, such as
CURRENT_TIMESTAMP, do not require parentheses.

You could also run another query, such as

SELECT
pat.PAT_ID
,MONTH(pat.BIRTH_DATE) "Birth Month"
FROM
PATIENT pat

In this query, the MONTH function will run once per row in the PATIENT table;
for each patient, the query will return the patient’s ID and the month in which the
patient was born.
Functions require arguments of the correct data type in the correct order. For
example, the DATEADD function requires one datepart, one number, and one
date or datetime, in that order. Any invalid input will cause an error, and the query
will not run.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•7
Functions

Using Functions in the WHERE Clause

Using functions will allow you to filter your results on more than just the raw
data—which may or may not be necessary. For instance, consider a report request
to show only patient encounters that took place in May 2015. There are several
ways to solve this problem, but each can alter the efficiency of the query.
CONTACT_DATE is an indexed column, so it can make your queries more
efficient if used in the WHERE clause. To take advantage of the index, you
should compare CONTACT_DATE to other dates:
SELECT
*
FROM
PAT_ENC pe
WHERE
pe.CONTACT_DATE >= '5/1/2015'
and pe.CONTACT_DATE < '6/1/2015'
It may be tempting to use a function for this filter. Consider the following query:
SELECT
*
FROM
PAT_ENC pe
WHERE
YEAR(pe.CONTACT_DATE) = 2015
and MONTH(pe.CONTACT_DATE) = 5

This finds the year-part of the contact date and matches it to 2015, then finds the
month-part of the contact date and matches it to 5.
The second query will be considerably less efficient than the first. The first query
is searching based on the value of CONTACT_DATE, which is indexed.
Therefore, it does not need to evaluate every row of PAT_ENC. The second query
is searching based on the output of the functions YEAR(CONTACT_DATE) and
MONTH(CONTACT_DATE), which are not indexed. Therefore, it will need to evaluate
the output of the functions for every row of PAT_ENC.
As a general rule, avoid using indexed columns inside functions in the WHERE
clause. The query will not use an index if the column is an argument to a function.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•8
Functions

SQL Functions

Exercise 2: Discovering Functions

In this exercise, you’ll see several new functions in real-world applications. In
Microsoft SQL Management Studio, placing your cursor in a function name and
pressing the F1 key will bring up online help text.
The F1 help key does not work in the Certification Environments. You can
find the same help text by searching for any function name on
https://msdn.microsoft.com/

 Conversion Functions
Conversion functions change data from one type to another. In Clarity, some
numeric data is stored as strings, and to use the data in a calculation, it must be
converted into numbers. Also, to format numeric data, it needs to be converted
into strings. The process of changing data types is often referred to as “casting”.
You are creating a SQL query that will be used in a Crystal Report.
To eventually group the report, you want one column that displays
the name of a department with its department ID in square brackets.

□ 1. Try to run the following query:

SELECT
DEPARTMENT_NAME + ' [' + DEPARTMENT_ID + ']'
FROM
CLARITY_DEP

□ 2. What error is returned?

________________________________________________

DEPARTMENT_NAME holds VARCHAR data, but DEPARTMENT_ID

holds NUMERIC data. In SQL, you can’t combine different types of data.

□ 3. Modify your query to:

SELECT
DEPARTMENT_NAME + ' [' + CAST(DEPARTMENT_ID as varchar) + ']'
FROM
CLARITY_DEP

Clarity SQL Fundamentals Epic 2017 Training Companion

4•9
Functions

□ 4. What does the CAST function do?

________________________________________________
CAST() is a function that works on both MS-SQL and Oracle. Another
function, CONVERT(), serves a similar purpose but works only on MS-
SQL. The syntax for CONVERT() is:
CONVERT(varchar,DEPARTMENT_ID)

Your organization needs a list of all patients and their dates of birth.
The BIRTH_DATE field is a datetime in Clarity, but it often doesn’t
extract a time from Chronicles and defaults to midnight. You want to
change the format of this date to mm/dd/yyyy, with no time listed.
□ 5. Run the following query:
SELECT
PAT_NAME
,BIRTH_DATE
FROM
PATIENT

The BIRTH_DATE column is a datetime. Datetime data is always

displayed the same way, with a time down to the millisecond. To change
the formatting, this date needs to be changed into a string (varchar).
□ 6. Modify your query to:

SELECT
PAT_NAME
,CONVERT(varchar, BIRTH_DATE)
FROM
PATIENT
Now, the BIRTH_DATE column looks different, but it still displays a
time of 12:00AM. You need to find a format that will hide the time part.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•10
Functions

□ 7. Place your cursor in the word CONVERT, then press F1.

 If you see the Online Help Consent dialogue, click “Yes.”

The F1 help key does not work in the Certification Environments. If you
are working there, search for 'CONVERT()' on
https://msdn.microsoft.com/ and select the first result.

□ 8. In the CONVERT help text, scroll down to the Remarks section.

□ 9. Which of the Date and Time Styles would return a date in the format
mm/dd/yyyy?

________________________________________________
Change your query to display the BIRTH_DATE in this format:

SELECT
PAT_NAME
,CONVERT(varchar, BIRTH_DATE, 101)
FROM
PATIENT

 Date and Time Functions

You need to generate a list of encounters in the next 30 days.
□ 10. Write and run the following query:

SELECT
CURRENT_TIMESTAMP

□ 11. What does the CURRENT_TIMESTAMP function return?

________________________________________________

Clarity SQL Fundamentals Epic 2017 Training Companion

4•11
Functions

The CURRENT_TIMESTAMP function is an ANSI-SQL function that

works in MS-SQL and Oracle. In MS-SQL, you may also see the function
GETDATE(), which returns the same result.

□ 12. Create a new query that displays the CSN, Contact Date, and Department
ID for all rows of the PAT_ENC table. Your query will look similar to the
following:
SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,DEPARTMENT_ID
FROM
PAT_ENC

□ 13. Try adding the following WHERE clause:

WHERE
CONTACT_DATE = CURRENT_TIMESTAMP

This query returns no results. An equals sign is not appropriate here; this
query looks for contacts at the exact millisecond the query is run.
When filtering based on dates, you will often need to use a range.
CURRENT_TIMESTAMP might work as a lower bound for the range, but the
upper bound needs to be the date 30 days from now.
□ 14. Remove the WHERE clause, then modify your query to the following:
SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,DEPARTMENT_ID
,DATEADD(d,30,CURRENT_TIMESTAMP)
FROM
PAT_ENC

□ 15. What does DATEADD(d,30,CURRENT_TIMESTAMP)return?

________________________________________________
Remove this from your SELECT list. For your report, you need contacts
within the next thirty days.
□ 16. Add the following WHERE clause to the end of your query:

WHERE
CONTACT_DATE between CURRENT_TIMESTAMP and
DATEADD(d,30,CURRENT_TIMESTAMP)

Clarity SQL Fundamentals Epic 2017 Training Companion

4•12
Functions

You need to add a column to your results grid to show how many
days away these contacts are.

□ 17. Type DATEDIFF() into your SELECT list. Place your cursor in the function
name, then open online help by pressing F1.
The F1 help key does not work in the Certification Environments. If you
are working there, search for 'DATEDIFF()' on
https://msdn.microsoft.com/ and select the first result.

□ 18. What arguments does the DATEDIFF() function take?

________________________________________________
□ 19. Add a column to your query to display how many days away each
upcoming encounter is.
SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,DEPARTMENT_ID
,DATEDIFF(d,CURRENT_TIMESTAMP,CONTACT_DATE)
FROM
PAT_ENC
WHERE
CONTACT_DATE between CURRENT_TIMESTAMP and DATEADD(d,30,
CURRENT_TIMESTAMP)

You now need to find all of the encounters that occurred last year up
to today's date last year.

□ 20. Clear out your existing WHERE clause. Type DATEFROMPARTS() into your
WHERE clause and press F1 (or search for it on the Microsoft website). This
function will be useful for finding these encounters.
What is the datatype of all of the arguments for this function?

__________________________________

What is the return type for this function?

__________________________________

□ 21. Change your WHERE clause to find contacts that occurred last year up to
today's date last year. You can also use the functions YEAR(), MONTH(),
DAY(), and CURRENT_TIMESTAMP.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•13
Functions

Here is one possible solution:

SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,DEPARTMENT_ID
,DATEDIFF(d,CURRENT_TIMESTAMP,CONTACT_DATE)
FROM
PAT_ENC
WHERE
CONTACT_DATE <= DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP)-1,
MONTH(CURRENT_TIMESTAMP),DAY(CURRENT_TIMESTAMP))
and YEAR(CONTACT_DATE) = YEAR(CURRENT_TIMESTAMP)-1

 String Functions
You've created a formula that finds the average charge amount for
the inpatient pharmacy. However, it's not creating a number that
looks like a dollar amount.
□ 22. Type the following new query into SQL Management Studio:
SELECT AVG(CHARGE)
FROM V_RX_CHARGES

The AVG()function is an aggregate function, which will be covered in the

next chapter.

□ 23. Type STR()into your SELECT list and press F1, or search for the function
on Microsoft’s website. Look through the resulting help page to find
information on the STR() function.
In the Microsoft SQL function help text, optional variables appear in
square brackets.

□ 24. You want to turn your dollar amount into a string, 5 characters long, with
2 decimals. Use the STR() function on your average charge amount to
create this string.
Your select list should now include the following:
STR(AVG(CHARGE),5,2)

□ 25. Since the dollar amount is now a string, you may also concatenate a dollar
sign to the front of it. Your query should now look like the following:
SELECT
AVG(CHARGE)
,'$' + STR(AVG(CHARGE),5,2)
FROM V_RX_CHARGES

Clarity SQL Fundamentals Epic 2017 Training Companion

4•14
Functions

You need to display each provider's name and title in a single field.

□ 26. Type the following new query in SQL Management Studio:

SELECT
PROV_NAME
,CLINICIAN_TITLE
FROM
CLARITY_SER

□ 27. You want to put PROV_NAME and CLINICIAN_TITLE into the same field.
Try modifying your SELECT list to the following:
SELECT
PROV_NAME + ', ' + CLINICIAN_TITLE
This doesn’t work, because the concatenation operator (+) can’t handle
one of its operands being NULL. If just one of the values being
concatenated is NULL, the concatenation returns NULL.
This problem is specific to MS-SQL. In Oracle, the double-pipe operator
( || ) can concatenate fields even when one is NULL.

□ 28. Type CONCAT()into your SELECT list and press F1. Look through the
resulting help page to find information on the CONCAT() function.
Instead of using the + operator to concatenate PROV_NAME and
CLINICIAN_TITLE, use the CONCAT() function.
The CONCAT() function is only available in MS-SQL. In Oracle, use the
double-pipe operator ( || ).

Modify your query to the following:

SELECT
CONCAT(PROV_NAME,', ',CLINICIAN_TITLE)
FROM
CLARITY_SER

 Null Functions
Different patients should receive different mailings, depending on
which location they visit. Some patients will have a primary location
specified; so for these patients you want to find the ID of the primary
location. If the patient doesn’t have a primary location specified, you
want to use their city to find the closest location. If the patient
doesn’t have a city either, you should display a default string.

This could be accomplished using a CASE statement, but there is a SQL

function that will make this easier.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•15
Functions

□ 29. Type the following new query into SQL Management Studio and run it:
SELECT
COALESCE(CITY,'*Unknown Location')
FROM
PATIENT

□ 30. Change the function arguments to the following:

COALESCE(CUR_PRIM_LOC_ID,CITY,'*Unknown Location')
You will likely see an error. If so, what error is returned?

__________________________________________________________
Location IDs are numeric, while cities are strings. COALESCE() can take
as many arguments as needed, but they all must be the same data type.
□ 31. Fix the problem using another function from an earlier part of this
exercise. Feel free to modify your query further if you’d like.
Your final query may look similar to the one below:

SELECT
COALESCE(CAST(CUR_PRIM_LOC_ID as varchar),CITY,'*Unknown
Location')
FROM
PATIENT

This is the end of Exercise 2. Exercise 4-3 begins on page 4-18.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•16
Functions

 Functions from this lesson

In this exercise, you explored a number of functions. For reference, here are the
functions discussed:
Conversion
CONVERT(data_type, Returns an expression in a new data_type. Optional
expression, [style]) style when converting dates to strings.
MS-SQL only.

CAST(expression AS Returns an expression in a new data_type.

data_type)

Dates and Times

GETDATE() Returns the current date and time.
MS-SQL only.

CURRENT_TIMESTAMP Returns the current date and time. Does not need
parentheses.

DATEADD(datepart, Returns a datetime that is number more dateparts

number, date) than the date. Example:
DATEADD(d,7,'1/1/2015') returns 1/8/2015.

DATEFROMPARTS(year, Creates a date value given three numbers for year,

month, day) month, and day.

MONTH(date) Returns the number of the month in a given date.

1=January, etc.

YEAR(date) Returns the year number in a given date.

DAY(date) Returns the day of month in a given date.

DATEDIFF(datepart, Returns the number of datepart boundaries crossed

startdate, enddate) between the startdate and enddate. Example:
DATEDIFF(YYYY,'12/31/2013','1/1/2014') = 1

Strings
STR(float_expression, Returns the number in float_expression as a string,
[length,decimals]) with a defined total length and number of decimals.

CONCAT(string_value1, Returns a string, made up of string_value1,

string_value2, string_value2, and any number of other strings.
[string_value3,...]) Treats NULL as an empty string.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•17
Functions

Null
COALESCE(input1, Returns the first non-null input.
input2[,input3, ...])

Here are a few more functions that may be useful. Keep in mind that there are
many more functions in SQL than can be covered here. If you have time, try
writing queries using these functions.
LEN(string_expression)
LEN(string_expression) Returns the number of characters in any
string_expression.
CHARINDEX(expressionToFind, Returns the number of the character in
expressionToSearch, expressionToSearch where the first
[start_location])
occurrence of expressionToFind begins.
Example: CHARINDEX('c','abcde') = 3.
SUBSTRING(expression, Returns part of a string, starting at the start
start, length)
position, and continuing for a given length.
ISNULL(check_expression, If check_expression is NULL, returns
replacement_value) replacement_value. Otherwise, returns
check_expression.
Similar to COALESCE(), however it can only
handle two expressions. Is processed more
quickly than COALESCE().
In Oracle, this function is called NVL( ).

Clarity SQL Fundamentals Epic 2017 Training Companion

4•18
Functions

Exercise 3: Using Functions with Dates

What would be the output of the following queries? Fill in the grids below.
SELECT
peh.HOSP_ADMSN_TIME as HAT
,YEAR(peh.HOSP_ADMSN_TIME) as Y
,MONTH(peh.HOSP_ADMSN_TIME) as M
,DAY(peh.HOSP_ADMSN_TIME) as D
FROM
PAT_ENC_HSP peh

HAT Y M D
2013-03-14
2013-06-23

SELECT
peh.HOSP_ADMSN_TIME as HAT
,peh.HOSP_DISCH_TIME as HDT
,DATEADD(D,30, peh.HOSP_DISCH_TIME) as DA
,DATEDIFF(D, peh.HOSP_ADMSN_TIME, peh.HOSP_DISCH_TIME) as DD
FROM
PAT_ENC_HSP peh

HAT HDT DA DD
2013-03-14 2013-03-31
2013-06-23 NULL

SELECT
peh.HOSP_ADMSN_TIME as HAT
,peh.HOSP_DISCH_TIME as HDT
,COALESCE(peh.HOSP_DISCH_TIME, peh.HOSP_ADMSN_TIME,
CURRENT_TIMESTAMP) as C
FROM
PAT_ENC_HSP peh

HAT HDT C
2013-03-14 2013-03-31
2013-06-23 NULL
NULL NULL

Clarity SQL Fundamentals Epic 2017 Training Companion

4•19
Functions

Exercise 4: If Time Permits – Hospital Admissions Report

Modify your query from Exercise 3-11 so that it displays the admission time in
the format of MM/DD/YYYY, discharge time in the format MM/DD/YYYY, and
the length of stay (measured in days). Note that length of stay is not stored
anywhere in Clarity, and must be calculated using a function.

Exercise 5: If Time Permits – Expanding the Hospital

Admissions Report
Modify the query from the previous exercise to:
 Display default values instead of NULL for admission provider, discharge
provider, and department
 Increase the accuracy of DATEDIFF by measuring in hours (hh) and dividing
by 24.0
Since DATEDIFF counts boundaries crossed, measuring in days can
sometimes yield misleading results.
The DATEDIFF in days between '8-1-2015 11:59 PM' and '8-2-2015 12:01
AM' would be 1, because one day-boundary was crossed.
When using DATEDIFF, it may be best to use a smaller datepart than you
think is necessary.

 Only include discharged patients (HOSP_DISCH_TIME has a value)

Clarity SQL Fundamentals Epic 2017 Training Companion

4•20
Functions

Epic Standard SQL Functions

When writing SQL queries, you will sometimes encounter syntax and functions
that are database-specific. For example, concatenation on Microsoft SQL Server
is achieved using a plus ‘+’ character, but on Oracle concatenation is achieved
using a double-pipe ‘||’.

Different Epic organizations will use different Clarity database types (Microsoft
SQL Server, Oracle, Teradata, etc). This requires different code to be developed
depending on the underlying database. To help avoid this in Epic-released reports,
Epic created several User Defined Functions (UDFs) that make our SQL code
database-independent. The Epic Standard SQL Functions are a library of UDFs
developed and released by Epic.

This section aims to make you aware of these Epic Standard SQL Functions and
able to interpret their use in Epic-released reports. Generally, you should not use
Epic Standard SQL Functions in your custom-created reports built for use within
your organization. You are aware of your organization’s database type and can
create code using that specific syntax, which will be more efficient than using the
Epic functions that check the platform each time they execute.

Epic Standard SQL Functions are easy to identify in Epic released queries; they
are all stored in the EPIC_UTIL schema. The following is a short description of
Epic Standard SQL functions you might see in Epic-released reports.

Date and Time Functions

These functions are used to convert dates and times to different formats.
Function Purpose Syntax
Get the current system date EPIC_UTIL.EFN_CURR_
EFN_CURR_DTTM and time. DTTM()

Convert a date and time to EPIC_UTIL.EFN_TRUNC

EFN_TRUNC_DT that date at midnight. _DT(date)

Adds # of (days, months, EPIC_UTIL.EFN_

years, seconds, minutes, DATEADD(interval,
EFN_DATEADD hours) to the date. num_intervals, date)
Return the difference EPIC_UTIL.EFN_
between the dates in the DATEDIFF(interval,
EFN_DATEDIFF interval specified. startDate, endDate)

Clarity SQL Fundamentals Epic 2017 Training Companion

4•21
Functions

The intervals used by EFN_DATEADD and EFN_DATEDIFF are

different than those used by the built-in SQL functions.
Interval Value
Years ‘y’ or ‘Y’
Months ‘m’ or ‘M’
Days ‘d’ or ‘D’
Hour ‘h’ or ‘H’
Minute ‘n’ or ‘N’
Second ‘s’ or ‘S’
EPIC_UTIL.EFN_
Return the number of BUSINESS_DATEDIFF(
business days between the startDate, endDate,
EFN_BUSINESS_ start and end date for the deptID,resourceYN,staffYN
DATEDIFF selected department. )
Convert a date String to a
date based on the format of
the string. Useful for relative EPIC_UTIL.EFN_DIN(
EFN_DIN dates. dateString)
Convert a date to a string in EPIC_UTIL.EFN_DOUT(
EFN_DOUT the defined format. date, format)

EFN_UTC_TO_ Convert UTC time to local EPIC_UTIL.EFN_UTC_

LOCAL time. TO_LOCAL(UtcTime)

EFN_LOCAL_TO_ Convert local time to UTC EPIC_UTIL.EFN_LOCAL

UTC time. _TO_UTC(LocalTime)

In Epic 2012+, you can use CURRENT_TIMESTAMP as an alternative to

EFN_CURR_DTTM on the SQL Server and Oracle versions.
CURRENT_TIMESTAMP returns the same value as
EFN_CURR_DTTM. Note, in Oracle with ODBC there may be issues
when using coalesce on CURRENT_TIMESTAMP and a date column, so
be sure to test your usage thoroughly.

In Epic 2015+, if you are using EFN_DATEDIFF to calculate patient age,

check to see if you can use V_PAT_FACT instead.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•22
Functions

String Functions

These functions are used to calculate information about and modify strings.

Function Purpose Syntax

EPIC_UTIL.EFN_CONCA
EFN_CONCAT Concatenate two strings.
T(string1, string2)

Return the length of the

EFN_LEN EPIC_UTIL.LEN(string)
string.

Find the first occurrence of a EPIC_UTIL.INSTR(stringT

EFN_INSTR
string inside another string. oSearch, string, startPos)

Given a string, a start

position, and the length of EPIC_UTIL.EFN_SUBSTR
EFN_SUBSTR
the substring, return the (string, startPos, len)
substring.

Return the name and ID of EPIC_UTIL.EFN_NAME_

the lookup record when WID(dataID, lookupID,
EFN_NAME_WID
using a left outer join with lookupName,
the dataID. lookupGenericDescription)

EPIC_UTIL.EFN_NAME_
Return the name with or
WEXTID(dataID,
without your defined
EFN_NAME_ lookupID, lookupName,
external ID of the lookup
WEXTID lookupExternalLabel,
record when using a left
lookupGenericDescription,
outer join with the dataID.
showLabel)

Return the specified piece of EPIC_UTIL.EFN_PIECE(i

EFN_PIECE the input string given a nputString,delimiter,positio
delimiter. n)

Clarity SQL Fundamentals Epic 2017 Training Companion

4•23
Functions

In Epic 2014+, do not use EFN_CONCAT, EFN_NAME_WID,

EF_NAME_WEXTID for development. SQL Server 2012 is the target
Microsoft platform, and contains a new CONCAT() function. You should
start using this function to do string concatenation rather than
EFN_CONCAT (and associated uses of EFN_NAME_WID and
EFN_NAME_WEXTID). CONCAT is also supported in Oracle, so it is
now a platform-agnostic way of doing string concatenation without
incurring the performance penalty of using EFN_ functions. Please note
that to keep it platform-agnostic, you must pass two and only two
arguments to CONCAT(); SQL Server allows more than 2 arguments, but
Oracle does not.

IntraConnect Functions

IntraConnect is an information-sharing system that allows physically separate

healthcare information systems to exchange data within a single organization. If
your organization is using IntraConnect, you may be translating IDs, in which
case the following function may be useful.

Function Purpose Syntax

Indicates whether
IntraConnect ID translation
is turned on. Used when ID
lookup of an Epic-released
record is needed. Returns ‘Y’
EFN_IDS_ if IDs are translated, ‘N’ if EPIC_UTIL.EFN_IDS_
TRANSLATED_YN not. TRANSLATED_YN()

In Epic 2015, use V_CID_TRANSLATION_YN instead of

EFN_IDS_TRANSLATED_YN.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•24
Functions

Advantages and Disadvantages of Epic Standard SQL

Functions
There are clear advantages to Epic’s use of Standard SQL Functions:
 It allows Epic to create code that works for all Epic organizations,
regardless of database type.
 It allows Epic to create one set of Epic-released Crystal Reports and one
set of BusinessObjects Universes, without creating sets for each database
type.
However, you should not use Epic Standard SQL Functions within the custom
reports you create for your organization, because of the following disadvantages:
 Your organization’s Clarity servers will only use a single database type, so
their use is unnecessary.
 Epic Standard SQL Functions can have potentially negative performance
impacts, particularly on SQL Server.
In fact, if you have reports that are taking a particularly long time to run, one
recommended practice is replacing any Epic Standard SQL Functions used in the
report with vendor-specific functions.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•25
Functions

Reference
Replace Epic Standard SQL Functions with Database-
Specific Code
Let’s take a look at the syntax you could use to make several of the Epic Standard
SQL Functions specific to your database.
First, let’s take a look at EFN_CONCAT. This Epic Standard SQL Function is
used to concatenate two columns.
You want to display the first and middle names of patients for display on a
report, concatenated together without the patients’ last names. There are
separate columns in the PATIENT table for FIRST_NAME,
MIDDLE_NAME, and LAST_NAME.
You’ll develop the database-specific function to do so in Microsoft SQL
Management studio. First, create a query to display the data as-is and
when formatted using the EFN_CONCAT function:
SELECT
pat.PAT_FIRST_NAME
,pat.PAT_MIDDLE_NAME
,EPIC_UTIL.EFN_CONCAT(pat.PAT_FIRST_NAME
,pat.PAT_MIDDLE_NAME)
FROM
PATIENT pat
Another limitation of the Epic Standard SQL function is that without
nesting multiple function calls, it only concatenates two strings, meaning
that there will be no space between the patient’s first name and middle
initial. Replacing the EFN_CONCAT function with database-specific
concatenation will be more flexible.
Concatenation for Oracle is achieved using a double-pipe “||”, and in SQL
server using a plus “+”. In training we have SQL Server, so we would
replace the function with:
SELECT
pat.PAT_FIRST_NAME
,pat.PAT_MIDDLE_NAME
,pat.PAT_FIRST_NAME + ' ' + pat.PAT_MIDDLE_NAME
FROM
PATIENT pat

Clarity SQL Fundamentals Epic 2017 Training Companion

4•26
Functions

Next, let’s take a look at EFN_TRUNC_DT. This function is used to remove the
time from a field that stores both date and time, so it returns the correct date with
time showing midnight.
MyChart, Epic’s web-based chart system, has messaging functionality that
allows providers to send patients information, such as lab results or visit
follow-up, instantly and securely. When reporting on these MyChart
messages, one piece of data that can be displayed is the date and time a
message is created.
This value is stored as a date-time field, in
MYC_MESG.CREATED_TIME. Instead of displaying both date and time,
you would like just the date portion of this field.
You’ll develop the database-specific function to do so in Microsoft SQL
Management studio. First, create a query to display the data as-is and when
formatted using the EFN_TRUNC_DT function:
SELECT
myc.CREATED_TIME
,EPIC_UTIL.EFN_TRUNC_DT(myc.CREATED_TIME)
FROM
MYC_MESG myc

Refer to the list of SQL functions earlier in this chapter. Which would you
use to replace the EFN_TRUNC_DT function for your organization’s
database?
In training, we have SQL Server, so we’ll use the CONVERT function. If
you have Oracle at your organization you would use the TRUNC function.
Since what you want is a datetime format, you’ll have to use the
CONVERT function twice, once to remove the time field completely, then
again to add it back in so it will default to midnight:
SELECT
myc.CREATED_TIME
,CONVERT(datetime,CONVERT(date,myc.CREATED_TIME))
FROM
MYC_MESG myc

Clarity SQL Fundamentals Epic 2017 Training Companion

4•27
Functions

After-Class Exercises

Exercise 1: Patients with Upcoming Birthdays

Write a query that finds all patients who are celebrating a birthday in the next
week. Display the patient's name, their date of birth in MM/DD/YYYY format,
and the age they will be turning. Make sure your query takes the following into
account:
- Patients may have been born in any year, not just this year.
- Some patients may have been born on February 29th.
- This report should work during the last week of the calendar year.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•28
Functions

This page intentionally left blank.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•29
Functions

Reviewing the Chapter

Review Questions

1. True or false: All functions require the use of parentheses, regardless of

whether they take any arguments.

2. True or false: Functions can be used in the SELECT clause.

3. True or false: Functions can be used in the WHERE clause.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•30
Functions

Review Key
1. True or false: All functions require the use of parentheses, regardless of
whether they take any arguments.

False. One example: CURRENT_TIMESTAMP.

2. True or false: Functions can be used in the SELECT clause.

True

3. True or false: Functions can be used in the WHERE clause.

True

Clarity SQL Fundamentals Epic 2017 Training Companion

4•31
Functions

Exercise Answers
See the Cogito’s Introduction to Epic’s Certification Environments appendix to
access the answer key files.

Exercise 1: Functions and Arguments

□ 1. MONTH is a function that takes in one argument, a date, and returns the
month value from the date. Fill in the chart below. (Dates are given in the
format month/day/year.)
Date MONTH(date)
'01/01/2013 12:42' 1
'June 16 1964' 6
'9-4-85' 9
'May 31, 1990' 5
'2015-10-21' 10
□ 2.
Datepart Number Date DATEADD(,,)
d 15 '01/01/2013' 2013-01-16
m 1 'June 16 1964' 1964-07-16
yyyy or yy -5 '9-4-85' 1980-09-04
d -3 'May 31, 1990' 1990-05-28
yy -30 '2015-10-21' 1985-10-21

Exercise 2: Discovering Functions

 Conversion Functions
□ 1. Try to run the following query:
SELECT
DEPARTMENT_NAME + ' [' + DEPARTMENT_ID + ']'
FROM
CLARITY_DEP
□ 2. What error is returned?
Error converting data type varchar to numeric.

Clarity SQL Fundamentals Epic 2017 Training Companion

4•32
Functions

□ 3. Modify your query to:

SELECT
DEPARTMENT_NAME + ' [' + CAST(DEPARTMENT_ID as varchar) + ']'
FROM
CLARITY_DEP

□ 4. What does the CAST function do?

Changes data from one data type to another.
□ 5. Run the following query:
SELECT
PAT_NAME
,BIRTH_DATE
FROM
PATIENT

□ 6. Modify your query like to:

SELECT
PAT_NAME
,CONVERT(varchar, BIRTH_DATE)
FROM
PATIENT

□ 7. Place your cursor in the word CONVERT, then press F1.

□ 8. In the CONVERT help screen, scroll down to the Remarks section.
□ 9. Which of the Date and Time Styles would return a date in the format
mm/dd/yyyy?
101 _

 Date and Time Functions

□ 10. Write and run the following query:

SELECT
CURRENT_TIMESTAMP
□ 11. What does the CURRENT_TIMESTAMP function return?
The current date and time
□ 12. Create a query that displays the CSN, Contact Date, and Department ID
for all rows of the PAT_ENC table. Your query will look similar to the
following:
SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,DEPARTMENT_ID
FROM
PAT_ENC

Clarity SQL Fundamentals Epic 2017 Training Companion

4•33
Functions

□ 13. Try adding the following WHERE clause:

SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,DEPARTMENT_ID
FROM
PAT_ENC
WHERE
CONTACT_DATE = CURRENT_TIMESTAMP

□ 14. Remove the WHERE clause, then modify your query to the following:
SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,DEPARTMENT_ID
,DATEADD(d,30,CURRENT_TIMESTAMP)
FROM
PAT_ENC

□ 15. What does DATEADD(d,30,CURRENT_TIMESTAMP) return?

The date and time exactly thirty days from now.____
□ 16. Add the following WHERE clause to the end of your query:

WHERE
CONTACT_DATE between CURRENT_TIMESTAMP and
DATEADD(d,30,CURRENT_TIMESTAMP)

□ 17. Type DATEDIFF() into your SELECT list. Place your cursor in the function
name, then open online help by pressing F1.

□ 18. What arguments does the DATEDIFF() function take?

Datepart, startdate, enddate. _
□ 19. Add a column to your query to display how many days away each
upcoming encounter is.
SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,DEPARTMENT_ID
,DATEDIFF(d,CURRENT_TIMESTAMP,CONTACT_DATE)
FROM
PAT_ENC
WHERE
CONTACT_DATE between CURRENT_TIMESTAMP and DATEADD(d,30,
CURRENT_TIMESTAMP)

Clarity SQL Fundamentals Epic 2017 Training Companion

4•34
Functions

□ 20. Clear out your existing WHERE clause. Type DATEFROMPARTS() into your
WHERE clause and press F1 (or search for it on the Microsoft website). This
function will be useful for finding these encounters.
What is the datatype of all of the arguments for this function?
Integer
What is the return type for this function?
Date
□ 21. Change your WHERE clause to find contacts that occurred last year up to
today's date last year. You will also need the functions YEAR(), MONTH(),
DAY(), and CURRENT_TIMESTAMP.
SELECT
PAT_ENC_CSN_ID
,CONTACT_DATE
,DEPARTMENT_ID
,DATEDIFF(d,CURRENT_TIMESTAMP,CONTACT_DATE)
FROM
PAT_ENC
WHERE
CONTACT_DATE <= DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP)-1,
MONTH(CURRENT_TIMESTAMP),DAY(CURRENT_TIMESTAMP))
and YEAR(CONTACT_DATE) = YEAR(CURRENT_TIMESTAMP)-1

 String Functions
□ 22. Type the following new query into SQL Management Studio:
SELECT AVG(CHARGE)
FROM V_RX_CHARGES

□ 23. Type STR()into your SELECT list and press F1, or search for the function
on Microsoft’s website. Look through the resulting help page to find
information on the STR() function.
□ 24. You want to turn your dollar amount into a string, 5 characters long, with
2 decimals. Use the STR() function on your average charge amount to
create this string.
SELECT
AVG(CHARGE)
,STR(AVG(CHARGE),5,2)
FROM V_RX_CHARGES
□ 25. Since the dollar amount is now a string, you may also concatenate a dollar
sign to the front of it. Your query should now look like the following:
SELECT
AVG(CHARGE)
,'$' + STR(AVG(CHARGE),5,2)
FROM V_RX_CHARGES

Clarity SQL Fundamentals Epic 2017 Training Companion

4•35
Functions

□ 26. Type the following query into SQL Management Studio:

SELECT
PROV_NAME
,CLINICIAN_TITLE
FROM
CLARITY_SER

□ 27. You want to put PROV_NAME and CLINICIAN_TITLE into the same field.
Try modifying your SELECT list to the following:
SELECT
PROV_NAME + ', ' + CLINICIAN_TITLE

□ 28. Type CONCAT() into your SELECT list and press F1. Look through the
resulting help page to find information on the CONCAT() function.
Instead of using the + operator to concatenate PROV_NAME and
CLINICIAN_TITLE, use the CONCAT() function. Modify your query to the
following:
SELECT
CONCAT(PROV_NAME,', ',CLINICIAN_TITLE)
FROM
CLARITY_SER

 Null Functions
□ 29. Type the following new query into SQL Management Studio and run it:
SELECT
COALESCE(CITY,'*Unknown Location')
FROM
PATIENT

□ 30. Change the function arguments to the following:

COALESCE(CUR_PRIM_LOC_ID,CITY,'*Unknown Location')
You will likely see an error. If so, what error is returned?
Error converting data type varchar to numeric. _
□ 31. Fix the problem using another function from an earlier part of this
exercise. Feel free to modify your query further if you’d like.
Your final query may look similar to the one below:
SELECT
COALESCE(CAST(CUR_PRIM_LOC_ID as varchar),CITY,'*Unknown
Location')
FROM
PATIENT

Clarity SQL Fundamentals Epic 2017 Training Companion

4•36
Functions

Exercise 3: Using Functions with Dates

What would be the output of the following queries? Fill in the grids below.
HAT Y M D
2013-03-14 2013 3 14
2013-06-23 2013 6 23

HAT HDT DA DD
2013-03-14 2013-03-31 2013-04-30 17
2013-06-23 NULL NULL NULL

HAT HDT C
2013-03-14 2013-03-31 2013-03-31
2013-06-23 NULL 2013-06-23
NULL NULL *YYYY-MM-DD
Where *YYYY-MM-DD is the date that the query is run.

Study Checklist
 Make sure you can define the following key terms:
 SQL function
 Argument
 Conversion functions
 Date and Time functions
 String functions
 Null functions
 Epic Standard SQL Functions
 Make sure you can perform the following tasks:
 Replace Epic Standard SQL Functions with database-specific
functions in queries and reports
 Find and use documentation for common SQL functions

Clarity SQL Fundamentals Epic 2017 Training Companion

4•37
Functions

 Make sure you fully understand and can explain the following concepts:
 Benefits of using functions
 Using functions in the WHERE clause efficiently
 Advantages and disadvantages of Epic Standard SQL functions

Clarity SQL Fundamentals Epic 2017 Training Companion

©2017 Epic Systems Corporation. Confidential
Lesson 5
Grouping and Summarizing
Data
Grouping and Summarizing Data 3
By the End of This Lesson, You Will Be Able to… 3
Big Picture 4
Sorting 5
Exercise 1: Sorting Patients 5
Aggregating Results 6
Exercise 2: Aggregate Functions 6
Exercise 3: Limitations of Aggregate Functions 7
Grouping 8
What GROUP BY Does 9
Exercise 4: GROUP BY 11
Exercise 5: If Time Permits – Hospital Admissions Report 12
Displaying Names when Grouping By IDs 13
Exercise 6: Displaying Names with GROUP BY 14
Exercise 7: If Time Permits – More names with GROUP BY 15
Filtering Groups with HAVING 16
Exercise 8: Filtering on Aggregate Data 16
Exercise 9: If Time Permits – Further Expanding the Hospital Admissions
Report 16
Reviewing the Chapter 17
Review Questions 17
5•2
Grouping and Summarizing Data

Review Key 18
Exercise Answers 19
Exercise 2: Aggregate Functions 19
Exercise 3: Limitations of the Aggregate Functions 19
Exercise 4: GROUP BY 20
Study Checklist 22

Clarity SQL Fundamentals Epic 2017 Training Companion

5•3
Grouping and Summarizing Data

Grouping and Summarizing Data

As stated earlier, the result of a SQL query is a table. Sometimes these tables can
be very large, with hundreds of thousands, if not millions, of results. It is
important that you be able to meaningfully organize these results.

By the End of This Lesson, You Will Be Able to…

 Sort data using the ORDER BY clause

 Use SQL aggregate functions to summarize data
 Describe the syntax and consequences of the GROUP BY clause
 Filter based on aggregate data using the HAVING clause

Clarity SQL Fundamentals Epic 2017 Training Companion

5•4
Grouping and Summarizing Data

Big Picture
PAT_ID PROV_ID
Z15669 TRN041
Z1599 E1063
Z1603 E1022
Z1752 1066
Z2629 E1063
Z2633 E1022
Z2677 1066
Z3249 E1022
Patients from database

PAT_ID PROV_ID
Z1752 1066
Z2677 1066
Z1603 E1022
Z2633 E1022
Z3249 E1022
Z1599 E1063
Z2629 E1063
Z15669 TRN041
Sorted by provider

PROV_ID Count of patients

1066 2
E1022 3
E1063 2
TRN041 1
Grouped and aggregated

Clarity SQL Fundamentals Epic 2017 Training Companion

5•5
Grouping and Summarizing Data

Sorting
Frequently, report requestors want their data organized and grouped together. One
such request would be to have a list of encounters, ‘grouped by department’. In
Crystal Reports, this is done using the Group Expert, and results in another level
of report in which you can place information. In SQL, however, we control that
level of grouping using the ORDER BY clause, and sort the data together into a
meaningful sequence. For example, you may want to sort in alphabetical order or
from highest to lowest outstanding balance. In SQL, we can control the order in
which information is resulted by using the ORDER BY clause. A column does not
need to appear in the SELECT clause to be able to sort by it.
In Crystal Reports, when you group a report by a field, the equivalent SQL
command Crystal actually uses is ORDER BY; Crystal then uses the fact
that the data is ordered to insert the new levels of the report.

ORDER BY is followed by the columns that you want to sort in ascending value, in
order of precedence. To do a reverse sort, add the DESC keyword after the
column. This is particularly effective at sorting dates, as in the following example,
where the DESC keyword will put the most recent dates at the top of the set.
SELECT
*
FROM
PAT_ENC pe
ORDER BY
pe.CONTACT_DATE DESC

You are also able to use column aliases from the SELECT statement list in the
ORDER BY clause. This is useful for sorting by the output of functions.

Exercise 1: Sorting Patients

□ 1. Write a query to sort patients based on ZIP code.
□ 2. Write a query to sort patients based on city.
□ 3. Write a report that shows the patient id, patient name, patient age, and the
patient’s current general provider. Sort by provider ID and then by patient
name. Hint: Use the database object V_PAT_FACT.
□ 4. Write a report that displays procedure orders (stored in the
ORDER_PROC table) by their ordering date, putting the most recent
orders at the top of the list.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•6
Grouping and Summarizing Data

Aggregating Results
Aggregate functions perform a specific operation over all rows in a group.
Aggregate functions differ from regular functions in that they take many rows'
worth of input and return one row of output.

Exercise 2: Aggregate Functions

□ 1. Imagine you stored the following data set as a table:
PAT_NAME SEX_C CONTACT_DATE Age
Adams, Jon 2 2005-05-28 00:00:00.000 35
Amiens, Wendy 1 NULL NULL
Brown, Mary 1 2012-05-16 00:00:00.000 56
Crabtree, Dawn 1 2009-10-14 00:00:00.000 36
Fryer, Frank NULL 2013-04-14 00:00:00.000 20
Smith, Donna 1 2011-11-04 00:00:00.000 16
Younger, Denise NULL 2014-03-14 00:00:00.000 20

You use the above table in the FROM clause of a new query. Using the above
data, fill in the “Result” column below

Function Description Example Result

COUNT(*) Counts all rows COUNT(*)

COUNT(Age)
COUNT(value) Counts all non-null COUNT(SEX_C)
values
COUNT(PAT_NAME)
Averages all non-null
AVG(value) AVG(Age)
values
Returns the highest
MAX(value) MAX(CONTACT_DATE)
value
Returns the lowest
MIN(value) MIN(SEX_C)
value
Returns the sum (total)
SUM(value) SUM(Age)
of all non-null values

Clarity SQL Fundamentals Epic 2017 Training Companion

5•7
Grouping and Summarizing Data

Exercise 3: Limitations of Aggregate Functions

□ 1. Look at the following query.
SELECT
pat.PAT_ID
FROM
PATIENT pat

How many columns would you expect this query to return?

□ 2. Assuming that there are 50 rows in the PATIENT table, how many rows
would you expect this query to return?

□ 3. Look at the following query.

SELECT
COUNT(*)
FROM
PATIENT pat

How many columns would you expect this query to return?

□ 4. Assuming that there are 50 rows in the PATIENT table, how many rows
would you expect this query to return?

□ 5. Try to run the following query.

SELECT
pat.PAT_ID
,COUNT(*)
FROM
PATIENT pat
What error is returned?

The last query in this exercise is attempting to perform two mutually exclusive
actions. First, it is attempting to generate a list of the patient IDs that are stored in
the patient table. Then it takes the single value of a count of all the rows in the
table. SQL makes no assumptions about how you want to combine these values,
and aborts the attempt to do so.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•8
Grouping and Summarizing Data

Grouping
In your previous Crystal Reports class, you created a report which grouped
patients by their Primary Care Provider (PCP). Grouping in Crystal Reports is
accomplished through the use of the Group Expert. Once the data is grouped, you
are able to display summary information, such as the count of patients per PCP,
while also displaying detailed information about each of the patients.

Crystal report displaying patients grouped by PCP

Grouping in a SQL query is accomplished through the use of the GROUP BY

clause. One of the main differences between grouping in SQL and grouping in
Crystal is that the GROUP BY clause essentially collapses the results into the
groups listed, no longer displaying the detailed information about those individual
patients. Think of grouping in SQL like grouping in Crystal, but with the ‘Details’
section completely suppressed.
SELECT
CUR_PCP_PROV_ID
,COUNT(PAT_ID) 'Count of Patients'
FROM
PATIENT
GROUP BY
CUR_PCP_PROV_ID

Clarity SQL Fundamentals Epic 2017 Training Companion

5•9
Grouping and Summarizing Data

Results of the above query grouping patients by PCP

You are still able to aggregate the data, but aren’t able to view individual patient
information. You are only able to display the columns that are included in the
GROUP BY clause.

What GROUP BY Does

Imagine you had the following in your PATIENT table:

If you were to GROUP BY the CUR_PCP_PROV_ID column, your query would

create buckets for each unique value of CUR_PCP_PROV_ID.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•10
Grouping and Summarizing Data

Grouping by CUR_PCP_PROV_ID. Note that a GROUP BY clause will change

what is possible in the SELECT list.
The rows from the original table, PATIENT, now belong to one of the groups.

Once all of the data is filed into a group, your SELECT list now refers to the
groups, not the individual rows. So, there are rules about what you can SELECT
when you have a GROUP BY clause:
- You can SELECT the field(s) in your GROUP BY clause, i.e. the ‘labels’
from the groups, and
- You can SELECT any aggregate functions
o The aggregate functions will only apply to rows within each group.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•11
Grouping and Summarizing Data

Exercise 4: GROUP BY
Use the following query to answer the questions below.
SELECT
op.AUTHRZING_PROV_ID
,op.PROC_ID
,COUNT(op.ORDER_PROC_ID) '# Orders'
,COUNT(DISTINCT op.PAT_ID) '# Pats'
FROM
ORDER_PROC op
GROUP BY
op.AUTHRZING_PROV_ID
,op.PROC_ID
ORDER BY
op.AUTHRZING_PROV_ID
,op.PROC_ID

□ 1. What table is the query using?

□ 2. How many columns will be in the results?

□ 3. Which columns are the results grouped by?

□ 4. Will each provider (AUTHRZING_PROV_ID) be listed only once?

□ 5. Will each procedure (PROC_ID) be listed only once?

□ 6. What is being counted in the ‘# orders’ column?

□ 7. What is being counted in the ‘# pats’ column?

When you use GROUP BY, you can aggregate on any columns in the tables
involved in the query. However, you can only display columns that are not in an
aggregate function if you include that column in the GROUP BY clause.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•12
Grouping and Summarizing Data

Exercise 5: If Time Permits – Hospital Admissions Report

Write a query to show a count of hospital encounters for each admitting provider.
Also show the average length of stay for that provider’s patients.
 Note: Many of the Length of Stays may be NULL. This is because of
encounters with NULL for either their admit or discharge times. For now,
this is fine. You will address this in a following exercise.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•13
Grouping and Summarizing Data

Displaying Names when Grouping By IDs

When your query has a GROUP BY clause, aggregate functions in your SELECT
list will consider values for each group. Imagine your database stored the
following data set in V_PAT_FACT:

You want to show a count of patients by provider. If you ran the following query:
SELECT
CUR_PCP_PROV_ID
,COUNT(*)
FROM
V_PAT_FACT
GROUP BY
CUR_PCP_PROV_ID
you would see a count of patients for each CUR_PCP_PROV_ID. But what if you
wanted to display the provider name?
If you ran the following query:
SELECT
CUR_PCP_PROV_ID
,CUR_PCP_NAME
,COUNT(*)
FROM
V_PAT_FACT
GROUP BY
CUR_PCP_PROV_ID
you’d get an error. You can’t have anything in your SELECT list that isn’t in your
GROUP BY clause or an aggregate function.
Consider the contents of the groups that are created:

Clarity SQL Fundamentals Epic 2017 Training Companion

5•14
Grouping and Summarizing Data

There’s only one unique CUR_PCP_NAME in each group, but SQL doesn’t
know that. If you want to display CUR_PCP_NAME, you have to either add it to
your GROUP BY clause or put it inside an aggregate function. As it turns out,
both are viable options. Often using the aggregate function will be more efficient,
but this can depend on several factors.
You will come across seemingly superfluous aggregate functions in SELECT
lists. Consider the following query:
SELECT
CUR_PCP_PROV_ID
,MAX(CUR_PCP_NAME)
,COUNT(*)
FROM
V_PAT_FACT
GROUP BY
CUR_PCP_PROV_ID
This will run without error. It will return the provider ID, provider name, and a
count of that provider’s patients. Since all of the CUR_PCP_NAME values are
the same in each group, the maximum (or minimum) will be the provider name.
Another solution would be the following:
SELECT
CUR_PCP_PROV_ID
,CUR_PCP_NAME
,COUNT(*)
FROM
V_PAT_FACT
GROUP BY
CUR_PCP_PROV_ID,
CUR_PCP_NAME
Since there is only one CUR_PCP_NAME per CUR_PCP_PROV_ID, adding this
additional column to the GROUP BY clause will not increase the number of
groups. You must still group by CUR_PCP_PROV_ID, since CUR_PCP_NAME
is not necessarily unique. This solution may be less efficient in some cases.

Exercise 6: Displaying Names with GROUP BY

Write a query based on the view V_SCHED_APPT. You want to see a count of
visits by visit type. Visit types are stored in the PRC master file.
GROUP BY the PRC_ID. In your results grid, display the visit type ID
(PRC_ID), the visit type name (PRC_NAME, which is a field in the view), and a
count of encounters.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•15
Grouping and Summarizing Data

Exercise 7: If Time Permits – More names with GROUP BY

Expand your query from the previous query. Now, you want to GROUP BY
service area, location, department, and provider. Use the IDs of each of these in
your GROUP BY clause. Add the names of each of these to your SELECT list.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•16
Grouping and Summarizing Data

Filtering Groups with HAVING

In an earlier exercise from this chapter, we wrote a report to look at providers and
the orders they place. If we only wanted to see particular procedures or particular
providers, we would just include a filter using a WHERE clause. However, what if
we want to filter on the aggregate functions? For example, if we wanted to only
show providers that had ordered the same procedure for more than 100 patients?
The WHERE clause does not handle aggregate functions, so instead we use the
HAVING clause.
SELECT
op.AUTHRZING_PROV_ID
,op.PROC_ID
,COUNT(op.ORDER_PROC_ID) "# Orders"
,COUNT(DISTINCT op.PAT_ID) "# Patients"
FROM
ORDER_PROC op
GROUP BY
op.AUTHRZING_PROV_ID
,op.PROC_ID
HAVING
COUNT(DISTINCT op.PAT_ID)>100

Exercise 8: Filtering on Aggregate Data

Write a query to show a count of visit diagnoses by patient encounter. Only
include patient encounters that have 5 or more visit diagnoses listed. Display the
encounter CSN and a count of visit diagnoses. Then add the patient’s ID.
Hint: Check the PAT_ENC_DX table in the Clarity Compass.

Exercise 9: If Time Permits – Further Expanding the

Hospital Admissions Report
Expand your query from Exercise 5-5 to…
 Group the data by admission year and month in addition to by provider
 Provide a count of unique patients that each provider has admitted to the
hospital
 Fix the issue with the LOS formula for encounters with patients that have
not been discharged. Use the CURRENT_TIMESTAMP if there is no
discharge date. Also filter out any contacts with an admission date in the
future.
 Filter out contacts that do not have an admission date.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•17
Grouping and Summarizing Data

Reviewing the Chapter

Review Questions

1. True or False: grouping in SQL is equivalent to grouping in Crystal.

2. What keyword can you use to change the direction of a sort in SQL?

Clarity SQL Fundamentals Epic 2017 Training Companion

5•18
Grouping and Summarizing Data

Review Key
1. True or False: grouping in SQL is equivalent to grouping in Crystal

False. Grouping in SQL collapses data, grouping in Crystal organizes

data.

2. What keyword can you use to change the direction of a sort in SQL?

DESC

Clarity SQL Fundamentals Epic 2017 Training Companion

5•19
Grouping and Summarizing Data

Exercise Answers
See the Cogito’s Introduction to Epic’s Certification Environments appendix to
access the answer key files.

Exercise 2: Aggregate Functions

Function Example Result

COUNT(*) COUNT(*) 7
COUNT(Age) 6
COUNT(value) COUNT(SEX_C) 5
COUNT(PAT_NAME) 7
30 (If Age is an int, AVG(Age) will return an int.
AVG(value) AVG(Age)
It will return 30.5 if Age is a numeric field.)
MAX(value) MAX(CONTACT_DATE) 3-14-2014
MIN(value) MIN(SEX_C) 1
SUM(value) SUM(Age) 183

Exercise 3: Limitations of the Aggregate Functions

□ 1. Look at the following query.

How many columns would you expect this query to return?

1

□ 2. Assuming that there are 50 rows in the PATIENT table, how many rows
would you expect this query to return?
50

□ 3. Look at the following query.

How many columns would you expect this query to return?

1

Clarity SQL Fundamentals Epic 2017 Training Companion

5•20
Grouping and Summarizing Data

□ 4. Assuming that there are 50 rows in the PATIENT table, how many rows
would you expect this query to return?
1

□ 5. Try to run the following query.

□ 6. What error is returned?

Column 'PATIENT.PAT_ID' is invalid in the select
list because it is not contained in either an
aggregate function or the GROUP BY clause.

Exercise 4: GROUP BY
□ 1. What table is the query using?
ORDER_PROC

□ 2. How many columns will be in the results?

4

□ 3. Which columns are the results grouped by?

AUTHRZING_PROV_ID and PROC_ID

□ 4. Will each provider (AUTHRZING_PROV_ID) be listed only once?

No

□ 5. Will each procedure (PROC_ID) be listed only once?

No

□ 6. What is being counted in the ‘# orders’ column?

The non-null order IDs. (ORD .1). The number of times a given provider
has ordered a given procedure.

□ 7. What is being counted in the ‘# pats’ column?

Clarity SQL Fundamentals Epic 2017 Training Companion

5•21
Grouping and Summarizing Data

The number of unique patients for whom each provider has ordered that
procedure.

Clarity SQL Fundamentals Epic 2017 Training Companion

5•22
Grouping and Summarizing Data

Study Checklist
 Make sure you can define the following key terms:
 ORDER BY clause
 Aggregate functions
 GROUP BY clause
 HAVING clause
 Make sure you can perform the following tasks:
 Write a SQL query with sorting
 Write a SQL query with aggregate functions
 Write a SQL query with grouping
 Make sure you fully understand and can explain the following concepts:
 Grouping in a SQL query vs. grouping in Crystal Reports

Clarity SQL Fundamentals Epic 2017 Training Companion

5•23
Grouping and Summarizing Data

Clarity SQL Fundamentals Epic 2017 Training Companion

©2017 Epic Systems Corporation. Confidential
Lesson 6
Adding Multiple Database
Objects
Adding Multiple Database Objects 3
By the End of This Lesson, You Will Be Able to… 3
Big Picture Diagram 4
Deciding to Include Multiple Database Objects 5
Creating a Join 6
Join Database Objects in a Query 7
Exercise 1: Join Multiple Database Objects 8
 Locate Necessary Data 9
 Build SQL Query 9
Exercise 2: If Time Permits – Using a Database Object Multiple Times in One
Query 11
Beyond the Basics - Additional Join Criteria 12
Exercise 3: If Time Permits – Additional Join Criteria 13
 Clinical 13
 Revenue/Access 14
Exercise 4: If Time Permits – Expanding the Hospital Admissions Report 16
Exercise 5: If Time Permits – Further Expanding the Hospital Admissions
Report 16
Appending Tables 17
Limitations of the Union Command 21
Union vs. Union all 21
Exercise 6: Procedure and Medication Orders 23
6•2
Adding Multiple Database Objects

Exercise 7: If Time Permits – Count of Procedure and Medication Orders 24

After-Class Exercises 25
Exercise 1: Interpreting a FROM clause 25
Exercise 2: Diagram a Report 27
Reviewing the Chapter 28
Review Questions 28
Review Key 29
Exercise Answers 30
Exercise 1: Join Multiple Database Objects 30
 Locate Necessary Data 30
 Build SQL Query 30
After-Class Exercise Answers 31
Exercise 1: Interpreting a FROM clause 31
Exercise 2: Diagram a Report 32
Study Checklist 33

Clarity SQL Fundamentals Epic 2017 Training Companion

6•3
Adding Multiple Database Objects

Adding Multiple Database Objects

Thus far, your queries have included only one table. Tables in Clarity generally
contain information about one concept. For example, CLARITY_DEP contains
information about departments, CLARITY_SER contains information about
providers, and PAT_ENC contains information about patient encounters.
Most Clarity reports require information from multiple tables. For example, you
may need to display provider name from CLARITY_SER, along with the
department name where they commonly work, which is in CLARITY_DEP.
Many database objects may be required for even a simple query. Once the objects
you require are identified, you need to determine how the tables relate to one
another. This act of logically relating one table to another is called a join. This
chapter will discuss key concepts behind joining tables, and the practical steps
necessary to include data from multiple data sources in one query.

By the End of This Lesson, You Will Be Able to…

 Determine when to use multiple data sources in a SQL query

 Create joins between database objects in a SQL query
 Identify when to append tables together
 Complete the steps of appending tables

Clarity SQL Fundamentals Epic 2017 Training Companion

6•4
Adding Multiple Database Objects

Big Picture Diagram

Adding Multiple Data Sources in Crystal Reports

Adding Multiple Data Sources in a SQL Query

Clarity SQL Fundamentals Epic 2017 Training Companion

6•5
Adding Multiple Database Objects

Deciding to Include Multiple Database Objects

Very rarely will you find everything you need for a report in a single table. The
first steps to writing a Clarity report are identifying which tables to use and how
to join them together. SQL queries start with one table. If your query needs
information found in additional tables, you need to link out to those tables by
joining the tables using foreign keys. A join is a means of combining fields from
two tables by using values related to each other in some way.
In the Clarity Data Model Fundamentals class you learned about a resource you
can use to determine how various tables can be joined –the Foreign Key
Information section of the Clarity Compass.
Epic’s application-specific Data Model classes will have more information about
the tables most commonly used for reporting on your application, and how to
choose and join the correct tables for a given report.
In this class, you will discuss the mechanics of how to join tables together, so that
you can use information from multiple tables on one SQL query.

As you write a query, make sure you have a reason and a purpose for each
database object you include in the query. Generally, if you can write a
query with fewer tables, the query will run faster.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•6
Adding Multiple Database Objects

Creating a Join
When you are creating a join in a SQL query, you need to know several things
about the join before you can create it. You need to know:
 The starting table of the join
 The ending table of the join
 The columns that act as foreign keys for the join
 The join type that should be used
You are creating a report to display patients and the names of their current
general primary care providers.
For this report, you will need two tables: the PATIENT table for the patients’
names, and the CLARITY_SER table for the providers’ names. To determine
which columns you should link together, you could use the Clarity Compass to
investigate the columns in each table. When viewing the Foreign Key Information
for the PATIENT table, you see the column you’ll use to link to the
CLARITY_SER table:

Foreign key information for the PATIENT table.

Once you know which columns to join, you need to decide on the join type to use.
Join types are described in detail in the Clarity Data Model Fundamentals course.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•7
Adding Multiple Database Objects

Join Database Objects in a Query

In a SQL query, you use the JOIN statement to join database objects. The JOIN
statement is part of the FROM clause. For example:
SELECT
[columns]
FROM [table 1]
[JOIN TYPE] [table 2]
ON [expression]

For an inner join, the server goes through every combination of rows from the two
tables and evaluates the expression for each pair. If the expression is true, then the
combined row is included in the result set. The expression is typically of the form:
[table 1.foreign key] = [table 2.column]

Using this pattern with the query described above to join PATIENT and
CLARITY_SER, you would create the following query.
SELECT
pat.PAT_NAME
,ser.PROV_NAME
FROM PATIENT pat
LEFT OUTER JOIN CLARITY_SER ser
ON pat.CUR_PCP_PROV_ID = ser.PROV_ID

When writing queries with multiple database objects, it’s very important to
use table aliases whenever referring to columns. Otherwise, you will likely
encounter an “ambiguous column name” error.
For example, if you add COUNTY_C to the SELECT list without a table alias,
SQL won’t know if you want to select PATIENT.COUNTY_C (patients’
counties) or CLARITY_SER.COUNTY_C (providers’ counties).

You also want to display the counties in which the patients live. Counties
are stored as category numbers in the PATIENT table, and you need a
ZC_ table to find the name of the counties.
First, identify the table and columns used in this join. This can be achieved using
the Clarity Compass in Hyperspace.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•8
Adding Multiple Database Objects

To add another join to a SQL query, simply repeat the same syntax in your FROM
clause, starting with the next join type:
SELECT
pat.PAT_NAME
,ptcounty.NAME "Patient County"
,ser.PROV_NAME
FROM PATIENT pat
LEFT OUTER JOIN CLARITY_SER ser
ON pat.CUR_PCP_PROV_ID = ser.PROV_ID
LEFT OUTER JOIN ZC_COUNTY ptcounty
ON pat.COUNTY_C = ptcounty.COUNTY_C

You also want to display the county in which the provider works.
To display two different counties on the same row of your result grid, you’ll need
two copies of ZC_COUNTY. To do this, join to the table again, but use a
different alias with the second join:
SELECT
pat.PAT_NAME
,ptcounty.NAME "Patient County"
,ser.PROV_NAME
,provcounty.NAME "Provider County"
FROM PATIENT pat
LEFT OUTER JOIN CLARITY_SER ser
ON pat.CUR_PCP_PROV_ID = ser.PROV_ID
LEFT OUTER JOIN ZC_COUNTY ptcounty
ON pat.COUNTY_C = ptcounty.COUNTY_C
LEFT OUTER JOIN ZC_COUNTY provcounty
ON ser.COUNTY_C = provcounty.COUNTY_C

Exercise 1: Join Multiple Database Objects

After a patient encounter, when a clinician has completed documentation and
orders for the patient, that encounter is supposed to be closed. You’ve been asked
to write a query to find office visits that have been left open.
You’ll display the encounter’s contact serial number, name of the patient who had
the encounter, department name, encounter type, visit provider’s name, and the
flag indicating whether the encounter is closed.
If you would like to try creating this query without guidance, keep in mind the
steps of joining tables:
 The starting table of the join
 The ending table of the join
 The columns that will act as foreign keys for the join

Clarity SQL Fundamentals Epic 2017 Training Companion

6•9
Adding Multiple Database Objects

If you would like additional resources in creating your query, follow the steps
below.

 Locate Necessary Data

□ 1. Using the control-click method and Record Viewer, you’ve determined
the INI and Item Number for all the data you will need on this report:
 Open/Closed Encounter Flag: EPT 18120
 Encounter Department ID: EPT 7070
 Encounter Type: EPT 30
 Visit Provider ID: EPT 7040
 Encounter CSN: EPT 8
 Encounter Patient ID: EPT .1
Using the Clarity Compass, find one Clarity table that extracts all of these
items. This will be your starting table.
Write down the name of your starting table:
___________________________________
Write down the name of each column you will need from this table:
Field name Master Item Clarity column
file INI number
Open/Closed EPT 18120
Encounter Flag
Encounter EPT 7070
Department ID
Encounter Type EPT 30
Visit Provider ID EPT 7040
Encounter CSN EPT 8
Encounter EPT .1
Patient ID

 Build SQL Query

□ 2. Open SQL Management Studio and create a new query.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•10
Adding Multiple Database Objects

□ 3. Begin building your query by creating the start of your FROM clause.
Include the main database object you identified above as the first table
you list in your FROM clause. This should be the PAT_ENC table. Give
this table an alias.
□ 4. For provider, patient, and department you’ll need more than just the IDs –
you want to display the names of those attributes.
a. What additional table will you need to display the patient name?
_____________________________
b. What additional table will you need to display the visit provider’s
name?
_____________________________
c. What additional table will you need to display the visit
department’s name?
_____________________________
If you don’t remember these tables from earlier, search the Clarity
Compass for the items EPT .2, SER .2, and DEP .2.

□ 5. Use the Clarity Compass to determine the proper columns on which to

perform your joins. If it helps, you can use the below tables to organize.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•11
Adding Multiple Database Objects

For this report, you may use inner joins. All of the tables are extracted daily and
there should be matching data in the destination tables.
□ 6. Finish building your FROM clause using the following generic structure:
FROM [table 1]
INNER JOIN [table 2]
ON [table 1.foreign key] = [table 2.column]
INNER JOIN [table 3]
ON [table 1.foreign key] = [table 3.column]
INNER JOIN [table 4]
ON [table 1.foreign key] = [table 4.column]

□ 7. Now create your SELECT list. Your results grid should display each of
the following attributes:
 Encounter CSN
 Encounter Department Name
 Encounter Patient Name
 Visit Provider Name
 Open/Closed Encounter Flag
 Encounter Type (Note: if you would like to display the name of the
encounter type, you will need to join to an additional table)

□ 8. Finally, include the required filtering in your query. You will need to
include a WHERE clause.
 Filter on the Open/Closed Encounter Flag, including all rows
where that flag contains an ‘N’.
 Also filter on the encounter type column; you’ll need to filter to
only the category value that corresponds to “office visit.”

Exercise 2: If Time Permits – Using a Database Object

Multiple Times in One Query
Your organization would like to see encounters in which a patient has an office
visit with a provider other than their primary care provider.
Create a query based on the PAT_ENC table that finds encounters with providers
who were not the patient’s primary care provider. Include the names of both the
visit provider as well as the patient’s primary care provider (hint: This is stored in
the PCP_PROV_ID column in the PAT_ENC table.) Also display the encounter
date, the patient’s name, and the name of the department.
If you have additional time, remove the encounter date, patient name, and PCP
name, group by visit provider and department, then add a count of how many
encounters each provider has had in which they saw patients for whom they were
not the primary care provider.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•12
Adding Multiple Database Objects

Beyond the Basics - Additional Join Criteria

This information is beyond the basics. It is not required for the Clarity
Report Writing Basics exam. However, this information will be used in
application-specific Clarity Data Model classes and their respective exams.

The ON statement in a SQL join can include any logical expression. Whenever
the expression is true for any pair of rows, those two rows will be combined in
your result grid.
You are creating a report on In Basket messages. In Basket messages can
be sent to organization staff as well as patients. You want to link an In
Basket message to the patient or staff member who received it.
The table IB_RECEIVER stores who received each In Basket message. The
column IB_RECEIVER.RECIPIENT stores the ID of the recipient. This is a
patient (EPT) ID if the message was sent to a patient, or a staff (EMP) ID if the
message was sent to staff. This column is used to link to PATIENT and
CLARITY_EMP. Consider the following query:
SELECT
*
FROM IB_RECEIVER ibr
LEFT OUTER JOIN PATIENT pat
ON ibr.RECIPIENT = pat.PAT_ID
LEFT OUTER JOIN CLARITY_EMP emp
ON ibr.RECIPIENT = emp.USER_ID
This query would return results, but some would be inaccurate. Each value in
IB_RECEIVER.RECIPIENT will be treated as both a patient ID and staff ID. If a
patient and staff member happened to have the same ID, both would be returned.
There is another column in IB_RECEIVER which can solve this problem. If
IB_RECEIVER.REGISTRY_ID = 8, the recipient was a patient. If
IB_RECEIVER.REGISTRY_ID = 1, the recipient was a staff member. This query
would be appropriate:
SELECT
*
FROM IB_RECEIVER ibr
LEFT OUTER JOIN PATIENT pat
ON
(
ibr.RECIPIENT = pat.PAT_ID
and ibr.REGISTRY_ID = 8 --recipient is a patient
)
LEFT OUTER JOIN CLARITY_EMP emp
ON
(
ibr.RECIPIENT = emp.USER_ID
and ibr.REGISTRY_ID = 1 --recipient is a staff member
)

Clarity SQL Fundamentals Epic 2017 Training Companion

6•13
Adding Multiple Database Objects

The ON statement tells SQL when to perform a join. When REGISTRY_ID = 8 is

in the ON statement of the join to PATIENT, it tells SQL that PATIENT should
be used when REGISTRY_ID is 8, meaning the recipient was a patient. It won’t
check the PATIENT table if the message was sent to a staff member.
More information on reporting on In Basket messages can be found in
CLR205: Clarity Data Model – EpicCare Ambulatory.

Exercise 3: If Time Permits – Additional Join Criteria

Choose one of the following examples to work through.
 Clinical
You are creating a report on patients’ temperature readings. Temperature readings
are recorded on the flowsheet, a clinical tool for tracking values over time.
The table that stores flowsheet measurements is IP_FLWSHT_MEAS. The
column that stores temperature values or temperature sources is
IP_FLWSHT_MEAS.MEAS_VALUE.
Depending on whether a row in the table corresponds to a temperature or a
temperature source, you will either look at MEAS_VALUE directly or link it to a
category table. The column MEAS_VALUE will store temperature values for
temperature rows, and will link to
ZC_PHYS_TEMP_SRC.PHYS_TEMP_SRC_C for temperature source rows.
A row in this table is a temperature value if
IP_FLWSHT_MEAS.FLO_MEAS_ID = '6'. If
IP_FLWSHT_MEAS.FLO_MEAS_ID = '7', the row is a temperature source.
You want to write a query to show the temperature values or names of
temperature sources. Write the ON statement for this query:
SELECT
meas.FSD_ID
,meas.RECORDED_TIME
,meas.MEAS_VALUE
,zctemp.NAME
FROM
IP_FLWSHT_MEAS meas
LEFT OUTER JOIN ZC_PHYS_TEMP_SRC zctemp
ON
(

)
WHERE
meas.FLO_MEAS_ID in ('6','7')
ORDER BY meas.FSD_ID,meas.RECORDED_TIME

Clarity SQL Fundamentals Epic 2017 Training Companion

6•14
Adding Multiple Database Objects

More information on reporting on Flowsheet data can be found in

CLR230: Shared Clinical Clarity Data Model.

 Revenue/Access
You are creating a report on Hospital Accounts. In Resolute Hospital Billing, you
are unable to send bills or claims for hospital accounts that have Stop Bills or
DNB checks placed on them.
The table that stores the history of both Stop Bills and DNB checks is called
HSP_ACCT_DNB_SB_HX. The column in this table that stores Stop Bill reason
or DNB check ID is HSP_ACCT_DNB_SB_HX.HX_DNB_SB.
Depending on whether one row in the table corresponds to a Stop Bill or DNB
check, you will have to link out to one of two places. The column
HSP_ACCT_DNB_SB_HX.HX_DNB_SB will link to
ZC_STOPBILL_RSN_HA.STOPBILL_RSN_HA_C for Stop Bill rows, and
CLARITY_LPP.LPP_ID for DNB check rows.
A row in this table is a Stop Bill row if
HSP_ACCT_DNB_SB_HX.HX_DNB_SB_C = 2. If
HSP_ACCT_DNB_SB_HX.HX_DNB_SB_C = 1, the row is a DNB check.
You want to write a query to show the names of the Stop Bill reasons or names of
DNB checks for accounts with either. Write the ON statements for the two joins
in this query:
SELECT
hx.HSP_ACCOUNT_ID
,hx.HX_DNB_SB_ADD_INST
,stopbill.NAME "Stop Bill Reason"
,dnb.LPP_NAME "DNB Check Name"

FROM HSP_ACCT_DNB_SB_HX hx
--Link to get Stop Bill reason
LEFT OUTER JOIN ZC_STOPBILL_RSN_HA stopbill
ON
(

)
--Link to get DNB check name
LEFT OUTER JOIN CLARITY_LPP dnb
ON
(

)
WHERE
hx.HX_DNB_SB_C IN (1,2)

The results of the above query would look like the following screenshot.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•15
Adding Multiple Database Objects

Results of a query using multiple join criteria

The above example could be altered further to combine the two reasons
into a single column, by altering the rows:
,stopbill.NAME "Stopbill Reason"
,dnb.LPP_NAME "DNB Check Name"
and replacing them with either a CASE:
,CASE hx.HX_DNB_SB_C
WHEN 2
THEN stopbill.NAME
WHEN 1
THEN dnb.LPP_NAME
END AS "Reason"
or, since one is populated when the other is null, a COALESCE() function:
,COALESCE(stopbill.NAME,dnb.NAME) "Reason"

More information on reporting on Hospital Billing can be found in

CLR301: Clarity Data Model – Resolute Hospital Billing.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•16
Adding Multiple Database Objects

Exercise 4: If Time Permits – Expanding the Hospital

Admissions Report
Modify your hospital admissions report from Exercise 4-4 or 4-5 so that it:
 Displays names instead of IDs for department, patient, and admitting and
discharge providers
 Sorts by admission date, displaying the most recent admissions first, and
then by patient name

Exercise 5: If Time Permits – Further Expanding the

Hospital Admissions Report
Modify your hospital admissions report to make sure that it:
 Uses Case/When to display different default values based on nulls
o *Unspecified – no ID in the left table
o *Unknown [ID] – no ID in the right table, but ID in the left
o *Unnamed [ID] – ID in the left and right tables, but no name
o Name[ID] – ID in left and right tables, name in the right table
NOTE: Department IDs are stored as numbers, not strings. You will have to either
use a function to concatenate or use a function to convert the number to a
string before using the concatenation operator.
 Sorts by length of stay, then admission date with most recent first, and
then by patient name
 Only includes discharged patients (HOSP_DISCH_TIME has a value)
 Measure the number of days with DATEDIFF by measuring in hours (hh)
and dividing by 24.0

This is the end of the Beyond the Basics section.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•17
Adding Multiple Database Objects

Appending Tables
Occasionally you may want to query two tables together as one. In SQL you are
able to append two tables together using UNION or UNION ALL commands. A
UNION can allow you to quickly combine two separate groups of results.
The UNION command comes between the two queries you would like to append.
For example:
SELECT [columns]
FROM [table 1]
WHERE [condition]

UNION

SELECT [columns]
FROM [table 2]
WHERE [condition]

Imagine you wanted to combine the patients in your system with a last
name of ‘Smith’ to those patients with a last name of ‘Baker.’
To begin, you could break this into two separate queries.
SELECT
pat.PAT_NAME
,pat.BIRTH_DATE
FROM PATIENT pat
WHERE pat.PAT_NAME like 'Smith,%'

SELECT
pat.PAT_NAME
,pat.BIRTH_DATE
FROM PATIENT pat
WHERE pat.PAT_NAME like 'Baker,%'

When these queries are run, you see the two results in separate windows.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•18
Adding Multiple Database Objects

Results of two queries before they are appended

If you wanted these results to be together in one table you could add the UNION
command between the two queries. Running this query would return the same
results, combined together in one result set.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•19
Adding Multiple Database Objects

Results of two queries with UNION command appending results

Appending the results of two queries is particularly useful if you are trying to
compare two populations.
Imagine you wanted to compare the number of encounters that have
happened at your organization so far this year to the total number from
last year.
To begin, you could break this into two separate queries.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•20
Adding Multiple Database Objects

 To determine how many encounters have happened in the current year, you
could use the following query:
SELECT 'This Year (in progress)'
,COUNT(*)"Number of Encounters"
FROM PAT_ENC
WHERE
YEAR(CONTACT_DATE) > (YEAR(CURRENT_TIMESTAMP)-1)
Which would return

 To find the number of encounters from the previous year, you could use:
SELECT 'Last Year (total)'
,COUNT(*) "Number of Encounters"
FROM PAT_ENC
WHERE
YEAR(CONTACT_DATE) = (YEAR(CURRENT_TIMESTAMP)-1)
Which would return

 If you wanted to display these two rows together, you could do so using the
UNION command, placed between the two queries.
SELECT 'This Year (in progress)'
,COUNT(*) "Number of Encounters"
FROM PAT_ENC
WHERE
YEAR(CONTACT_DATE) > (YEAR(CURRENT_TIMESTAMP)-1)

UNION

SELECT 'Last Year (total)'

,COUNT(*) "Number of Encounters"
FROM PAT_ENC
WHERE
YEAR(CONTACT_DATE) = (YEAR(CURRENT_TIMESTAMP)-1)

Now that the two queries have a UNION command between them, you see the
previous two rows together in one set of results:

Clarity SQL Fundamentals Epic 2017 Training Companion

6•21
Adding Multiple Database Objects

Limitations of the Union Command

Not all results can be appended using the UNION command. There are a few
requirements of the datasets in order for UNION to function properly.
 Each SELECT statement within the UNION must have the same number of
columns.
 The columns of each SELECT statement within the UNION must have
similar data types.
 The columns of each SELECT statement within the UNION must be in the
same order.
 The UNION command returns only distinct values by default. To allow
duplicate values, use the ALL keyword with UNION.

To view the data type of a column used in your query in SQL Management
Studio, place your mouse over the column name in your query and the
tooltip that appears will give you this information in the format:
COLUMN_NAME ([Primary key], [Data type (length)], [Allow nulls])

Union vs. Union all

Let’s explore this last requirement with an example. By default, the UNION
command will return only distinct values. This means that any repeated values
will be filtered out of the results you see. Making your query take the extra step of
only selecting distinct values can impact performance.
If you know your two queries will only be returning unique values, you can add
the ALL keyword on to your UNION command. Then you save the extra step of
selecting distinct values, which can allow your query to run faster.
Imagine you wanted the list of all of your organization’s service areas and
departments for use in a report. You’d like to display their names and IDs.
To create such a query, the correct tables to use would be CLARITY_SA
and CLARITY_DEP.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•22
Adding Multiple Database Objects

The table CLARITY_SA extracts data from the EAF master file, and it will
contain one row for your facility, then an additional row for each service area.
The table CLARITY_DEP extracts data from the DEP master file, and it will
contain one row for each department at your organization. There should be no
repeated records between these two tables, so if you were to append queries from
these two tables you could reasonably expect to use UNION ALL for this.
To verify this, let’s start by creating the two queries you will UNION together.

Query to list service area names and IDs:

SELECT SERV_AREA_ID
,SERV_AREA_NAME
,'From CLARITY_SA'
FROM CLARITY_SA

Query to list department names and IDs:

SELECT DEPARTMENT_ID
,DEPARTMENT_NAME
,'From CLARITY_DEP'
FROM CLARITY_DEP

Execute the two queries within one window. The results you receive should look
similar to what is shown below.

Two separate queries, service area and department

Clarity SQL Fundamentals Epic 2017 Training Companion

6•23
Adding Multiple Database Objects

Try adding a UNION command between the two queries. Now update it to
UNION ALL. You should find the number of results did not change, since there
are no rows in the top query which are exactly the same as in the bottom query.

Exercise 6: Procedure and Medication Orders

If you wish to report on orders placed in Epic, they are stored in the ORD master
file in Chronicles. When these orders are extracted to Clarity, they are split into
two separate tables, ORDER_PROC and ORDER_MED. ORDER_PROC
contains all of the procedure orders that have been placed in Epic, while
ORDER_MED contains all medication orders. If you would like to report on both
procedure and medication orders placed in Epic, you will need to use both tables.
You need to create a query to list all orders that have been placed since January 1,
2016 (filtering on the ORDERING_DATE column). You will display the order’s
ID, the ID of the patient for which order was placed, and a string indicating
whether that order was a medication or procedure order.
HINT: For the most efficient query possible, would you use UNION or UNION
ALL to append your results?
Format your results so they appear similar to the screenshot below. Note that your
data will not match the data in this screenshot.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•24
Adding Multiple Database Objects

Exercise 7: If Time Permits – Count of Procedure and

Medication Orders
Now you would like to draw some conclusions from the query you created in the
previous exercise. You would like to see how many of each type of order patients
have had placed in the past year.
You will no longer need to display order ID; instead you will group your
procedure order and medication order results by patient. Display the count of each
type of order each patient has had since January 1, 2016.
Format your results so they appear similar to the screenshot below. Note that your
data will not match the data in this screenshot.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•25
Adding Multiple Database Objects

After-Class Exercises

Exercise 1: Interpreting a FROM clause

In this exercise you will see the FROM clause taken from the standard Epic-
released Clarity report Provider Access Report by Department. Examine the
FROM clause and answer the following questions.
FROM
(
"Clarity_2017_PRACTICE"."dbo"."ACCESS_PROV" "ACCESS_PROV"
LEFT JOIN "Clarity_2017_PRACTICE"."dbo"."CLARITY_SER"
"CLARITY_SER" ON "ACCESS_PROV"."PROV_ID" =
"CLARITY_SER"."PROV_ID"
)
LEFT JOIN "Clarity_2017_PRACTICE"."dbo"."CLARITY_DEP"
"CLARITY_DEP" ON "ACCESS_PROV"."DEPARTMENT_ID" =
"CLARITY_DEP"."DEPARTMENT_ID"
□ 1. What tables are used by the report?
_________________________________________________
_________________________________________________
_________________________________________________
_________________________________________________
_________________________________________________
□ 2. Fill in the diagram on the next page based on the joins used by the report.
For example, the inner join between PATIENT.PAT_ID and
PAT_ENC_HSP.PAT_ID would be written as

Based on the SQL query above, what joins are used by the Provider
Access Report by Department report?

Clarity SQL Fundamentals Epic 2017 Training Companion

6•26
Adding Multiple Database Objects

Clarity SQL Fundamentals Epic 2017 Training Companion

6•27
Adding Multiple Database Objects

Exercise 2: Diagram a Report

Below is the FROM clause from a report. Use the clause to create a diagram of
the joins used by the report.
FROM (((((((("Clarity_2017_PRACTICE"."dbo"."PAT_ENC_HSP"
"PAT_ENC_HSP" LEFT OUTER JOIN
"Clarity_2017_PRACTICE"."dbo"."PATIENT" "PATIENT" ON
"PAT_ENC_HSP"."PAT_ID"="PATIENT"."PAT_ID") LEFT OUTER JOIN
"Clarity_2017_PRACTICE"."dbo"."CLARITY_ADT" "CLARITY_ADT" ON
"PAT_ENC_HSP"."PAT_ENC_CSN_ID"="CLARITY_ADT"."PAT_ENC_CSN
_ID") LEFT OUTER JOIN
"Clarity_2017_PRACTICE"."dbo"."HSP_ATND_PROV" "HSP_ATND_PROV"
ON
"PAT_ENC_HSP"."PAT_ENC_CSN_ID"="HSP_ATND_PROV"."PAT_ENC_C
SN_ID") LEFT OUTER JOIN
"Clarity_2017_PRACTICE"."dbo"."CLARITY_DEP" "CLARITY_DEP" ON
"CLARITY_ADT"."DEPARTMENT_ID"="CLARITY_DEP"."DEPARTMENT
_ID") LEFT OUTER JOIN "Clarity_2017_PRACTICE"."dbo"."CLARITY_SER"
"CLARITY_SER" ON
"HSP_ATND_PROV"."PROV_ID"="CLARITY_SER"."PROV_ID") LEFT
OUTER JOIN "Clarity_2017_PRACTICE"."dbo"."ZC_BIRTH_STATUS"
"ZC_BIRTH_STATUS" ON
"PATIENT"."BIRTH_STATUS_C"="ZC_BIRTH_STATUS"."BIRTH_STATUS
_C") LEFT OUTER JOIN "Clarity_2017_PRACTICE"."dbo"."PATIENT"
"PATIENT_mom" ON
"PATIENT"."MOTHER_PAT_ID"="PATIENT_mom"."PAT_ID"

Clarity SQL Fundamentals Epic 2017 Training Companion

6•28
Adding Multiple Database Objects

Reviewing the Chapter

Review Questions

1. True or False: In a SQL query, if you want to include multiple database

objects, one way would be to specify how they will be joined together
in the FROM clause of your query.

2. True or False: The UNION command can be used to append any two
tables together.

3. How would you alter a UNION command to return duplicate results

(removing the need to select distinct values)?

Clarity SQL Fundamentals Epic 2017 Training Companion

6•29
Adding Multiple Database Objects

Review Key
1. True or False: In a SQL query, if you want to include multiple database
objects, one way would be to specify how they will be joined together
in the FROM clause of your query.

True

2. True or False: The UNION command can be used to append any two
tables together.

False. The two database objects need to have the same number of
columns with the same data types in the same order.

3. How would you alter a UNION command to return duplicate results

(removing the need to select distinct values)?

Add ALL to the end, using the UNION ALL command.

Clarity SQL Fundamentals Epic 2017 Training Companion

6•30
Adding Multiple Database Objects

Exercise Answers
See the Cogito’s Introduction to Epic’s Certification Environments appendix to
access the answer key files.

Exercise 1: Join Multiple Database Objects

 Locate Necessary Data

Write down the name of your starting table:
______PAT_ENC_______
Field name Master Item Clarity column
file INI number
Open/Closed EPT 18120 ENC_CLOSED_YN
Encounter Flag
Encounter EPT 7070 DEPARTMENT_ID
Department ID
Encounter Type EPT 30 ENC_TYPE_C
Visit Provider ID EPT 7040 VISIT_PROV_ID
Encounter CSN EPT 8 PAT_ENC_CSN_ID
Encounter EPT .1 PAT_ID
Patient ID

 Build SQL Query

What additional table will you need to display the patient name?
PATIENT
What additional table will you need to display the visit provider’s name?
CLARITY_SER
What additional table will you need to display the visit department’s
name?
CLARITY_DEP

Clarity SQL Fundamentals Epic 2017 Training Companion

6•31
Adding Multiple Database Objects

After-Class Exercise Answers

Exercise 1: Interpreting a FROM clause

□ 1. What tables are used by the report?
ACCESS_PROV
CLARITY_SER
CLARITY_DEP
□ 2. Based on the SQL query above, what joins are used by the Provider
Access Report by Department report?

Clarity SQL Fundamentals Epic 2017 Training Companion

6•32
Adding Multiple Database Objects

Exercise 2: Diagram a Report

Clarity SQL Fundamentals Epic 2017 Training Companion

6•33
Adding Multiple Database Objects

Study Checklist
 Make sure you can define the following key terms:
 Join
 Foreign Key
 Append
 Make sure you can perform the following tasks:
 Join multiple database objects in the FROM clause of a SQL query
 Use multiple join criteria in the FROM clause of a SQL query
 Append multiple database objects using the UNION command
 Make sure you fully understand and can explain the following concepts:
 Determine when multiple join criteria are needed
 Identify when to use the UNION command and the UNION ALL
commands

Clarity SQL Fundamentals Epic 2017 Training Companion

©2017 Epic Systems Corporation. Confidential
Lesson 7
Adding a Query to Crystal
Reports
Adding a Query to Crystal Reports 3
By the End of This Lesson, You Will Be Able to… 3
The Big Picture 4
Adding a Query to Crystal Reports 5
Exercise 1: Adding a SQL Query to a Crystal Report 5
Exercise 2: Adding Parameters to a Command Object 7
 Correcting the Order of the Parameters 8
After Class Exercise 9
 Create a SQL Query 9
 Add Your Query to Crystal Reports as a Command Object 11
 Format your report in Crystal 12
Exercise 1: Add Parameters to a Command Object 13
Reviewing the Chapter 14
Review Questions 14
Review Key 15
Study Checklist 16
7•2
Adding a Query to Crystal Reports

Clarity SQL Fundamentals Epic 2017 Training Companion

7•3
Adding a Query to Crystal Reports

Adding a Query to Crystal Reports

You can now write SQL queries that are complex and efficient. However, the data
in your results grid isn’t formatted as a report, it just appears as a list of data. To
be able to present the data in your results grid, you will need to bring that data
into a report formatting tool. Crystal Reports allows you to copy and paste your
own query into a new report.

By the End of This Lesson, You Will Be Able to…

 Add your query to Crystal Reports as a Command object

 Add parameters to the Command object
 Format your report in Crystal

Clarity SQL Fundamentals Epic 2017 Training Companion

7•4
Adding a Query to Crystal Reports

The Big Picture

Clarity SQL Fundamentals Epic 2017 Training Companion

7•5
Adding a Query to Crystal Reports

Adding a Query to Crystal Reports

Recall that the result of a SQL query is a table. In previous chapters, we’ve
discussed many ways to influence the contents of that table, as well as some
things to keep in mind to generate the table in an efficient manner. However, the
end result is still a table, while many report requestors are looking to be able to
see the results and intuitively comprehend them. Other tools are needed to
accomplish this, and a common tool used by Epic customers for this purpose is
Crystal Reports. Another benefit of running your query through Crystal is that
you gain the ability to tie your query into the distribution system used at your
organization for Crystal reports, including Epic-Crystal Integration.
To create a Crystal report based on a SQL query you wrote, you must create
something called a Command Object. This looks like a table in the Crystal report,
but is actually a SQL query that runs when you run the report.
Command Objects should be treated as all or nothing. When joining a
Command Object to other database objects (tables, views, etc), Crystal
will often perform a Cartesian product of the results and filter it locally.
This results in extremely inefficient queries that can have a negative
impact on performance. If you need additional tables in a Command
Object-based report, you should edit the Command Object.

A Command Object gives you more control over what actions are
performed on the server. It does not automatically make things faster.
Whatever is not done on the server will be performed locally by Crystal
Reports. A poorly written Command Object will likely result in worse
performance then having Crystal generate the SQL statement for you.

Exercise 1: Adding a SQL Query to a Crystal Report

□ 1. Open Crystal Reports.
□ 2. Navigate to File > New > Blank Report.
□ 3. Use the information on your Classroom Information Sheet to connect to
the training Clarity database.
□ 4. Expand the Clarity option.
□ 5. Select Add Command and click the > button
□ 6. In your Save Folder from your Classroom Information Sheet, you should
have a file called 'Exercise 7-1 Command Object.sql.' Open that file in
SQL Management Studio.

Clarity SQL Fundamentals Epic 2017 Training Companion

7•6
Adding a Query to Crystal Reports

Add Command in the Database Expert

Add Command to Report

Clarity SQL Fundamentals Epic 2017 Training Companion

7•7
Adding a Query to Crystal Reports

□ 7. Run the query. Make sure you have results.

□ 8. Select the entire query. Right-click and choose copy.
□ 9. Back in Crystal Reports, paste your query into the Add Command to
Report window.
□ 10. Click OK.
□ 11. Click OK in the Database Expert.
□ 12. In the Field Explorer, expand your database fields. You should see your
command object and the 7 columns from your query.

□ 13. Use the Group Expert to group the report by Department and by
Admitting Provider.
□ 14. Add the PAT_NAME, HOSP_ADMSN_TIME, HOSP_DISCH_TIME,
and Length of Stay fields to the details section of the report.
□ 15. Format as desired.

Exercise 2: Adding Parameters to a Command Object

□ 1. Open the report from Exercise 1.
□ 2. Open the Database Expert.
□ 3. Right-click your command object and select Edit Command.
 You will be creating date range parameters and using them as a
filter for your results.
□ 4. Click Create.
□ 5. Create a StartDate parameter with the following values:
Parameter Name: StartDate

Clarity SQL Fundamentals Epic 2017 Training Companion

7•8
Adding a Query to Crystal Reports

Prompting Text: Enter a starting date for your admission date range:
Value Type: Date
Default Value: 1/1/2016
□ 6. Create an EndDate parameter with the following values:
Parameter Name: EndDate
Prompting Text: Enter an ending date for your admission date range:
Value Type: Date
Default Value: 1/1/2017
Do not close the command object window at this point!
If you leave the command object window without using the parameters,
the parameters will be deleted.

□ 7. Modify the WHERE clause to use your new parameters.

WHERE
peh.HOSP_ADMSN_TIME >= {?StartDate}
AND
peh.HOSP_ADMSN_TIME < {?EndDate}

Instead of typing {?StartDate} and {?EndDate}, you can double-click a

parameter from the parameter list.

Click OK.
□ 8. The Enter Values screen should appear. Notice that the parameters don’t
have any defaults in them yet. The defaults that you added are in the
dropdown menu. Select your default values.
At this point, your parameters appear in reverse order. You'll correct this
later on.

□ 9. Select OK and run the report.

 Correcting the Order of the Parameters

By default, parameters will be presented in alphabetical order. As you’ve seen,
this is problematic for “StartDate” and “EndDate.”
□ 10. In the Field Explorer, right-click Parameter Fields.
□ 11. Choose Set Parameter Order.
□ 12. Use the arrow buttons to swap the EndDate and StartDate parameters.
□ 13. Continue to format your report as desired.

Clarity SQL Fundamentals Epic 2017 Training Companion

7•9
Adding a Query to Crystal Reports

After Class Exercise

In this after class exercise, you will be using a pattern in SQL known as a
window function. This function allows you to append aggregate data as an
additional column without collapsing the data as with the GROUP BY
clause. In this exercise, you will have a column that shows how many rows
have the same DEPARTMENT_ID as the row it is on.
Window functions are considered Beyond the Basics for this course.

 Create a SQL Query

□ 1. Create a query using the main Cadence appointment table,
F_SCHED_APPT:
SELECT
*
FROM
F_SCHED_APPT appt

□ 2. Add in filtering to ensure you only return appointments where

F_SCHED_APPT.CONTACT_DATE is between January 1, 2005 and
January 1, 2012.
□ 3. Create the window function to return the number of appointments per
department. Your query should now look like:
SELECT
COUNT(*) OVER
(PARTITION BY appt.DEPARTMENT_ID) AS dep_cnt
FROM
F_SCHED_APPT appt
WHERE
appt.CONTACT_DATE between '1/1/2012' and '1/1/2017'

□ 4. You also need to display appointment date and time, as well as order the
results by that field. You’ll need to add the APPT_DTTM column to the
SELECT list, as well as add an ORDER BY clause:
SELECT
appt.APPT_DTTM
,COUNT(*) OVER
(PARTITION BY appt.DEPARTMENT_ID) AS dep_cnt
FROM
F_SCHED_APPT appt
WHERE
appt.CONTACT_DATE between '1/1/2012' and '1/1/2017'
ORDER BY
appt.DEPARTMENT_ID
,appt.APPT_DTTM

Clarity SQL Fundamentals Epic 2017 Training Companion

7•10
Adding a Query to Crystal Reports

□ 5. You need to display patient name and department name. These columns
may be in F_SCHED_APPT. What tools could you use to look these up?

□ 6. Use one of these tools to determine the other two tables you’ll have to
include in your query and the columns on which you’ll need to link them.
Tables you’ll need:
F_SCHED_APPT, _____________________, ____________________
Add these additional two tables to your FROM clause, including the proper linking.
Your query should now look like:
SELECT
appt.APPT_DTTM
,COUNT(*) OVER
(PARTITION BY appt.DEPARTMENT_ID) AS dep_cnt
FROM
F_SCHED_APPT appt
INNER JOIN PATIENT pat
ON appt.PAT_ID = pat.PAT_ID
LEFT OUTER JOIN CLARITY_DEP dep
ON appt.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE
appt.CONTACT_DATE between '1/1/2012' and '1/1/2017'
ORDER BY
appt.DEPARTMENT_ID
,appt.APPT_DTTM
□ 7. Add the rest of the necessary display information to your Select
statement.
SELECT
pat.PAT_NAME
,dep.DEPARTMENT_NAME
,appt.APPT_DTTM
,COUNT(*) OVER
(PARTITION BY appt.DEPARTMENT_ID) AS dep_cnt
FROM
F_SCHED_APPT appt
INNER JOIN PATIENT pat
ON appt.PAT_ID = pat.PAT_ID
INNER JOIN CLARITY_DEP dep
ON appt.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE
appt.CONTACT_DATE between '1/1/2005' and '1/1/2017'
ORDER BY
appt.DEPARTMENT_ID
,appt.APPT_DTTM

Clarity SQL Fundamentals Epic 2017 Training Companion

7•11
Adding a Query to Crystal Reports

 Add Your Query to Crystal Reports as a Command Object

□ 8. Highlight your query and copy it by pressing Ctrl+C on your keyboard.
□ 9. Open Crystal Reports.
□ 10. Navigate to File > New > Blank Report.
□ 11. Connect to the training Clarity database.
□ 12. Expand the Clarity option.
□ 13. Select Add Command and click the > button.

□ 14. Paste your query into the Add Command to Report window:

Clarity SQL Fundamentals Epic 2017 Training Companion

7•12
Adding a Query to Crystal Reports

□ 15. Click OK.

□ 16. Add the four fields to the Details section of your report by clicking and
dragging them from the Field Explorer (if you do not see the Field
Explorer, navigate to View > Field Explorer). Your Design tab should
look similar to what is below.

 Format your report in Crystal

□ 17. Continue to format your report as desired.

Clarity SQL Fundamentals Epic 2017 Training Companion

7•13
Adding a Query to Crystal Reports

Exercise 1: Add Parameters to a Command Object

In this exercise, you’ll build off of the report you created. Your report currently
shows appointments between 1/1/2012 and 1/1/2017. To increase report
flexibility, replace these dates with parameters to allow the user to filter the report
at run time.

Clarity SQL Fundamentals Epic 2017 Training Companion

7•14
Adding a Query to Crystal Reports

Reviewing the Chapter

Review Questions

1. What feature can be added to a SQL query using a command object

from Crystal Reports?

2. True or False: Formatting doesn’t matter when pasting a SQL query

into a command object.

Clarity SQL Fundamentals Epic 2017 Training Companion

7•15
Adding a Query to Crystal Reports

Review Key
1. What feature can be added to a SQL query using a command object
from Crystal Reports?

Parameters

2. True or False: Formatting doesn’t matter when pasting a SQL query

into a command object.

True

Clarity SQL Fundamentals Epic 2017 Training Companion

7•16
Adding a Query to Crystal Reports

Study Checklist
 Make sure you can define the following key terms:
 Command Object
 Parameter
 Make sure you can perform the following tasks:
 Add SQL to a Crystal Report using a command object

Clarity SQL Fundamentals Epic 2017 Training Companion

7•17
Adding a Query to Crystal Reports

Clarity SQL Fundamentals Epic 2017 Training Companion

©2017 Epic Systems Corporation. Confidential
Lesson 8
Using Subqueries
Using Subqueries 3
By the End of This Lesson, You Will Be Able to… 3
The Big Picture 4
Subqueries 5
Subquery in SELECT Clause 5
Exercise 1: Create a Subquery to Return Each Patient’s Most Recent
Appointment 7
Exercise 2: If Time Permits – Office Visit Breakdown by Department 7
Correlated Subquery 8
Subquery in WHERE Clause 9
Subquery in FROM Clause 10
In-Class Activity: Percentage of Encounters by Encounter Type 10
 Count of all encounters per department 10
 Count of encounters of each type per department 11
 Calculate percentage 12
Scope 15
Exercise 3: Patients’ Relative Ages 16
 Step-by-Step Method: Create Subquery 16
 Step-by-Step Method: Create Parent Query 16
Exercise 4: If Time Permits – Expand Your Encounters by Department and
Encounter Type Query 18
Common Table Expressions 19
Exercise 5: If Time Permits – Work with Common Table Expressions 20
8•2
Using Subqueries

Reviewing the Chapter 21

Review Questions 21
Review Key 22
Exercise Answers 23
Exercise 3: Patients’ Relative Ages 23
 Step-by-Step Method: Create Subquery 23
 Step-by-Step Method: Create Parent Query 23
Study Checklist 24

Clarity SQL Fundamentals Epic 2017 Training Companion

8•3
Using Subqueries

Using Subqueries
A SQL query acts on one or more tables and also returns results as a table.
Therefore, it is possible to create a query that is nested within another query. The
results of this ‘sub’ query comprise table within the outer ‘parent’ query. In this
chapter, you’ll explore the use of subqueries and how their placement determines
how they are able to be used within a SQL query.

By the End of This Lesson, You Will Be Able to…

 Recognize when to use a subquery in a SQL query

 Construct a subquery in a SQL query
 Identify correlated subqueries and describe their potential impact on query
performance
 Construct an in-line view in a SQL query
 Recognize when to use a common table expression
 Use the WITH clause in a SQL query

Clarity SQL Fundamentals Epic 2017 Training Companion

8•4
Using Subqueries

The Big Picture

Clarity SQL Fundamentals Epic 2017 Training Companion

8•5
Using Subqueries

Subqueries
A subquery is a query contained within another query. Subqueries can be used in
any of the following clauses:
 SELECT
 WHERE
 FROM
A subquery is always enclosed within parentheses, and it is usually executed prior
to the entire statement containing the subquery. Subqueries can be classified in
one of two ways.
Type Description
Correlated Refers to columns in the outer ‘parent’ query. Runs once
per line.
Uncorrelated Executed once. The same result is used inside the parent
query multiple times.

Since correlated subqueries are executed once for each row in the parent,
they have the potential to negatively impact performance.

Subquery in SELECT Clause

A subquery nested within the SELECT clause allows you to incorporate a piece of
information from a table not included in your outer ‘parent’ query. When used in
this manner, a subquery must return a single value.
You would like to list appointments, displaying the appointment contact
serial number, contact date, and the name of the patient for whom the
appointment was conducted.
One way to build the above query would be to include both the F_SCHED_APPT
table and the PATIENT table in your FROM clause, similar to this query:
SELECT
appt.PAT_ENC_CSN_ID
,appt.CONTACT_DATE
,pat.PAT_NAME
FROM
F_SCHED_APPT appt
INNER JOIN PATIENT pat
ON appt.PAT_ID = pat.PAT_ID

Clarity SQL Fundamentals Epic 2017 Training Companion

8•6
Using Subqueries

This would also give you the freedom to display any other columns from the
PATIENT table, such as the patient’s address or phone number. To do so, you
would include those columns in the SELECT clause.
But if you only need patient name from the PATIENT table, you can build this
directly within the SELECT list, without including PATIENT in the FROM clause.
A subquery in the SELECT clause has the following basic formula:
SELECT [column]
,(SELECT [data]
FROM [table 2]
WHERE [logic])
FROM [table 1]
If you were to apply this formula to your query, you could continue to display
appointment information and the patient’s name while only listing the
F_SCHED_APPT table in the FROM clause.
Let’s work through building this query.

 If you start with the appointment information only, you have:

SELECT appt.PAT_ENC_CSN_ID
,appt.CONTACT_DATE
FROM F_SCHED_APPT appt

 What you’d like is a third column added to your results, displaying the
patient’s name. Build that into your query, so it now looks like:
SELECT appt.PAT_ENC_CSN_ID
,appt.CONTACT_DATE
,(SELECT PAT_NAME
FROM PATIENT pat) 'Patient Name'
FROM F_SCHED_APPT appt

 Did the above query work? Why do you think this is?
____________________________________________________________

You likely received a ‘Subquery returned more than one value’ error message. To
return only one value, you need to limit what is being returned by the subquery.
Rather than listing every patient name that exists in the PATIENT table, you only
want the name of the patient for whom the appointment was conducted.

Clarity SQL Fundamentals Epic 2017 Training Companion

8•7
Using Subqueries

 You need to specify this in a WHERE statement, as shown below.

SELECT
appt.PAT_ENC_CSN_ID
,appt.CONTACT_DATE
,(
SELECT
pat.PAT_NAME
FROM
PATIENT pat
WHERE
appt.PAT_ID = pat.PAT_ID
) 'Patient Name'
FROM
F_SCHED_APPT appt

Exercise 1: Create a Subquery to Return Each Patient’s

Most Recent Appointment
You would like to create a query that displays the date of the most recent or future
appointment for a patient. That is, if the patient has any appointments scheduled
in the future, you would want to list the most future date; otherwise, you want to
list the most recent date on which the patient had an appointment. You’ll also
display the name of the patient, the patient’s birth date, and the patient’s MRN.
Create this query so that the only table in the FROM clause is PATIENT.
HINT: The date of the appointment is stored in CONTACT_DATE column of the
F_SCHED_APPT table. If you want the most recent or future appointment, which
function would you use on the CONTACT_DATE?

Exercise 2: If Time Permits – Office Visit Breakdown by

Department
You would like to create a query that displays the department in which office
visits have been scheduled.
Create a query that groups appointments by department ID and department name.
Then add in three columns with aggregate functions to calculate the following:
 Total office visits in this department
 Total office visits that have taken place in any department (overall total)
 Percent of office visits that have happened in this department
As an added challenge, sort your results to display the busiest departments first.
Your results may look similar to the following screenshot.

Clarity SQL Fundamentals Epic 2017 Training Companion

8•8
Using Subqueries

Correlated Subquery

A subquery is always enclosed within parentheses, and it is usually executed prior

to the entire statement containing the subquery. A correlated subquery refers to
columns existing in the outer ‘parent’ query. These correlated subqueries are
executed once for each row in the parent – as you might anticipate, they have the
potential to negatively impact performance.
You would like to find the most recent previous completed appointment
for each patient in the 12 months prior to their current appointment.

SELECT
appt.PAT_ENC_CSN_ID
,appt.PAT_ID
,appt.CONTACT_DATE
,(
SELECT
MAX(CONTACT_DATE)
FROM
F_SCHED_APPT prevappt
WHERE
prevappt.PAT_ID = appt.PAT_ID
AND prevappt.CONTACT_DATE >=
DATEADD(year, -1, appt.CONTACT_DATE)
AND prevappt.CONTACT_DATE < appt.CONTACT_DATE
AND prevappt.APPT_STATUS_C = 2
) most_recent_appt_date

FROM
F_SCHED_APPT appt

Clarity SQL Fundamentals Epic 2017 Training Companion

8•9
Using Subqueries

If you would like to create a similar query without using correlated

subqueries, you could use the query shown below:
SELECT
appt.PAT_ENC_CSN_ID
,appt.PAT_ID
,appt.CONTACT_DATE
,MAX(prevappt.CONTACT_DATE) most_recent_appt_date

FROM F_SCHED_APPT appt

LEFT OUTER JOIN F_SCHED_APPT prevappt
ON appt.PAT_ID = prevappt.PAT_ID
AND prevappt.APPT_STATUS_C = 2
AND prevappt.CONTACT_DATE >= DATEADD(YEAR, -1,
appt.CONTACT_DATE)
AND prevappt.CONTACT_DATE < appt.CONTACT_DATE

GROUP BY
appt.PAT_ENC_CSN_ID
,appt.PAT_ID
,appt.CONTACT_DATE

Subquery in WHERE Clause

A subquery nested within the WHERE clause allows you to create advanced
filtering on the data being returned by your query. A subquery in the WHERE
clause is commonly used to return the most recent contact for records.
Your organization has been having problems with extremely future-dated
appointments. You have been asked to write a query to return the
appointments scheduled in the most future year that exists in the
F_SCHED_APPT table. You will display each of those appointments’
contact serial number, contact date, and the ID of the patient for whom the
appointment is scheduled.
SELECT
appt.PAT_ENC_CSN_ID
,appt.CONTACT_DATE
,appt.PAT_ID

FROM
F_SCHED_APPT appt

WHERE
YEAR(appt.CONTACT_DATE) =
(
SELECT YEAR(MAX(last_appt.CONTACT_DATE))
FROM F_SCHED_APPT last_appt
)

Clarity SQL Fundamentals Epic 2017 Training Companion

8•10
Using Subqueries

Subquery in FROM Clause

You might have noticed that the previous two subquery locations tended to return
just one value, such as a sum, count, or maximum number. A subquery within the
FROM clause, however, will often be most useful when returning multiple rows
and columns of data.
This method of using a subquery in the FROM clause is often referred to as an
“Inline View.”
The result matrix of the subquery in the FROM clause is used in the query as
though it were another table.
In-Class Activity: Percentage of Encounters by Encounter
Type
In the following activity, you will create a query to meet the following need.
You have been asked to create a query that returns the breakdown of
encounter types by department in terms of percentages (for example, for a
given department, what percentage of its encounters are Office Visits
versus Telephone Calls). Encounter type is stored as the ENC_TYPE_C
column in the PAT_ENC table.
Let’s break this down into separate steps. There isn’t a column in Clarity that
stores this percentage, so you will need to calculate it within the query itself. To
calculate the percentage of encounters by type and department, you’ll need the
total count of encounters per department (this will become the denominator in
your calculation) and the number of appointments with each encounter type in
that department (this will become the numerator in your calculation). In short, you
will need to calculate:

 Count of all encounters per department

Let’s start with the denominator. To calculate the total number of encounters in
each department, the correct table to use would be PAT_ENC.
 Work with those sitting around you to create a query that would return count
of all encounters per department, by filling in the blanks in the query below.
SELECT DEPARTMENT_ID
,_____________ TotalEncs
FROM PAT_ENC
______________ DEPARTMENT_ID

Clarity SQL Fundamentals Epic 2017 Training Companion

8•11
Using Subqueries

 Build this query in SQL Management Studio.

 When you run your query, the first few rows of your result matrix should
display values similar to:

Total encounters per department. Note: the data in the training database may
have changed since the publication of this companion.

 Count of encounters of each type per department

Now focus on the numerator. To calculate the number of encounters of each type
in each department, again the correct table to use would be PAT_ENC.
 Work with those sitting around you to create a query that returns count of
encounters of each type per department by filling in the blanks in this query:
SELECT DEPARTMENT_ID
,_____________________ Type
,_____________________ NumEncsByType
FROM PAT_ENC
GROUP BY ____________________________________

 Build this query in SQL Server Management Studio.

 When you run your query, the first few rows of your result matrix should
display values similar to:

Clarity SQL Fundamentals Epic 2017 Training Companion

8•12
Using Subqueries

Number of encounters by department and encounter type. Note: the data in the
training database may have changed since the publication of this companion.

 Calculate percentage
The last step is to combine the above two calculations into a percentage. You will
need the TotalEncs column and NumEncsByType column to calculate your
percentage. In short, you will want to calculate:

To have both columns available in one query, you will need to include the two
result matrices in the FROM clause, which would allow you to use each as though
it were a Clarity table.
 Create a new query and copy-paste the two queries you created earlier into it.
Your query window will look similar to what is shown below:
--Query to calculate number of encounters by department
SELECT DEPARTMENT_ID
,COUNT(*) TotalEncs
FROM PAT_ENC
GROUP BY DEPARTMENT_ID

--Query to calculate number of encounters by department and

type
SELECT DEPARTMENT_ID
,ENC_TYPE_C Type
,COUNT(ENC_TYPE_C) NumEncsByType
FROM PAT_ENC
GROUP BY PAT_ENC.DEPARTMENT_ID,ENC_TYPE_C

Clarity SQL Fundamentals Epic 2017 Training Companion

8•13
Using Subqueries

 Run your query. You will have both result matrices returned separately. To
bring these together, you will need to include these two queries as subqueries
within an outer, parent query.
 Build a parent query around your two subqueries by adding SELECT * and a
FROM clause before your two subqueries. Enclose your two subqueries in
parentheses and give each an alias, similar to below.

SELECT *
FROM
(
--Query to calculate number of encounters by department
SELECT DEPARTMENT_ID
,COUNT(*) TotalEncs
FROM PAT_ENC
GROUP BY DEPARTMENT_ID
) NumEncsByDept

(
--Query to calculate number of encounters by department and
type
SELECT DEPARTMENT_ID
,ENC_TYPE_C Type
,COUNT(ENC_TYPE_C) NumEncsByType
FROM PAT_ENC
GROUP BY PAT_ENC.DEPARTMENT_ID,ENC_TYPE_C
) NumEncsByDeptType

 Attempt to run your query. You are presented with an error message. What is
missing from your query?
_____________________________________________________
 You need to join your two queries, as though they were tables themselves.
What column do both queries have in common that you could use in your join
expression?
_____________________________________________________
 Link your two subqueries together, using a LEFT OUTER JOIN and linking
on the column above. Now run your query.
Troubleshooting tip: you can highlight each subquery within your code
and execute that selection alone to verify that it returns what's desired.

Clarity SQL Fundamentals Epic 2017 Training Companion

8•14
Using Subqueries

 You are presented with all of the columns from both subqueries together in
one result matrix. To calculate the percentage, you will need to add the
calculation as a separate column. Remember, the percentage is calculated as:

 Add the columns in the screenshot below to your SELECT clause, then
ORDER BY the department ID. The first few rows of output from your query
should be similar to the result matrix below.

Final result of query displaying percentage of encounters of different types per

department. Note: data may have changed since this screenshot was taken.

Clarity SQL Fundamentals Epic 2017 Training Companion

8•15
Using Subqueries

Scope
A query can only access data from the table(s) in its FROM clause. A parent query,
then, can only access the columns in its subqueries’ tables if the subqueries list
those columns in their SELECT clauses. For example, what would happen if you
were to build the following query in SQL Management Studio?

SELECT PAT_NAME

FROM
(
SELECT PAT_ID
,BIRTH_DATE
FROM PATIENT
) patinfo

The above query will return an error. Although PAT_NAME is a column that
exists in the PATIENT table, your subquery is not passing that column to the
outer parent query via its SELECT clause. The parent query only has access to the
PAT_ID and BIRTH_DATE columns. If you did need the PAT_NAME, you
would need to include it in the subquery’s SELECT list.

SELECT PAT_NAME

FROM
(
SELECT PAT_ID
,BIRTH_DATE
,PAT_NAME
FROM PATIENT
) patinfo

Clarity SQL Fundamentals Epic 2017 Training Companion

8•16
Using Subqueries

Exercise 3: Patients’ Relative Ages

Create a query that determines, for each patient, whether they are younger or
older than the average age of patients in their city. Display each patient’s ID,
name, and city, and whether they are older or younger than their city’s average
age. Do not include cities with only one patient.
You may either attempt to create this query on your own, or you may choose to
follow the ‘step by step’ method below.

 Step-by-Step Method: Create Subquery

□ 1. You will begin by creating a subquery that returns cities where your
patients live and their ages. What table stores this information in Clarity?
____________________________________
□ 2. Create a query to display each patient’s city and age. Which function will
you need to use for age?
_____________________________________
□ 3. You want to create ‘buckets’ for each city – you’re going to need the
average age per city and the number of patients per city. What clause will
allow you to perform aggregate functions on patients from each city?
_____________________________________
□ 4. When you use this clause, you can select aggregate functions. Use the
average function, avg(), to find the average age for each city.
□ 5. Add a count of patients per city to your SELECT clause, and make sure
your columns are aliased. Your subquery will look similar to what is
shown below:
SELECT p.CITY
,avg(datediff(d,p.BIRTH_DATE,getdate()) "ageavg"
,count(*) "count"
FROM PATIENT p
GROUP BY p.CITY

 Step-by-Step Method: Create Parent Query

□ 6. Build a parent query around your subquery by adding SELECT * and a
FROM clause before your subquery. Enclose your subquery in parentheses
and give it an alias, similar to the following.

Clarity SQL Fundamentals Epic 2017 Training Companion

8•17
Using Subqueries

SELECT *
FROM
(
SELECT p.CITY
,avg(datediff(d,p.BIRTH_DATE,getdate())) "ageavg"
,count(*) "count"
FROM PATIENT p
GROUP BY p.CITY
) as agecity
□ 7. Run your query. You now have the aggregate data you need, but you
don’t have any patient-level information. You need to display each
patient’s name and ID. Which table stores that information?
______________________________________________
□ 8. When you have a subquery in the FROM clause, you are allowed to join it
to other tables. What column in your subquery is also in the above table?
______________________________________________
□ 9. Use this column to INNER JOIN to the table you identified. Your FROM
clause should look like this:
FROM
(
SELECT p.CITY
,avg(datediff(d,p.BIRTH_DATE,getdate())) "ageavg"
,count(*) "count"
FROM PATIENT p
GROUP BY p.CITY
) as agecity
INNER JOIN PATIENT pat
ON agecity.CITY = pat.CITY

□ 10. Add a WHERE clause to the end of your query so you don’t get cities with
only one patient. You can use the "count" column from your subquery.
□ 11. Now, you need to build your select statement. SELECT the patient ID, the
patient name, and the patient city.
□ 12. For the older/younger than average flag, you need a CASE statement.
You can copy this CASE statement as a new column into your query:
CASE
WHEN datediff(d,pat.BIRTH_DATE,getdate()) < agecity.ageavg
THEN 'Younger'
WHEN datediff(d,pat.BIRTH_DATE,getdate()) > agecity.ageavg
THEN 'Older'
ELSE 'AVERAGE'
END "Compared to average"

□ 13. Run your query.

Clarity SQL Fundamentals Epic 2017 Training Companion

8•18
Using Subqueries

Exercise 4: If Time Permits – Expand Your Encounters by

Department and Encounter Type Query
In this exercise, you would like to expand the query you created in the previous
in-class activity used to calculate the percentage of encounters by department and
encounter type. You would like to alter this query to…
 Display department name in addition to department ID
 Display encounter type names instead of category values
 Order results by department ID

Clarity SQL Fundamentals Epic 2017 Training Companion

8•19
Using Subqueries

Common Table Expressions

A common table expression, or WITH clause, allows you to execute one or more
subqueries and save the result set of each for later reference by your "main"
SELECT statement. Common table expressions can be used to make code neater,
since you can reference the common table expression name instead of including a
subquery within the main query itself. Common table expressions are known as
“subquery factoring” in Oracle.
Common table expressions may also improve report performance, as the queries
in the WITH clause are typically executed only once when the main query runs,
whereas an equivalent search using a subquery may result in multiple executions
of the subquery.
Each named query in the WITH clause is called a common table expression
(CTE). You can have multiple CTEs, and a given CTE can reference another
defined CTE or itself (also called a recursive CTE).
A query containing a common table expression has the following basic formula:
WITH [alias] AS
(SELECT [data]
FROM [table 2]
WHERE [logic])

SELECT [column]
,[data]
FROM [table 1]
JOIN [alias]
ON [table 1].[column]=[alias].[data]

During a previous activity, you created a query that returns the breakdown of
encounter types by department in terms of percentages (for example, for a
given department what percentage of its encounters are Office Visits versus
Telephone Calls). You created the query using two subqueries. You could
have created this query instead using two common table expressions, as
shown below, to achieve a simpler outer parent query:
WITH NumEncsByDept AS --CTE to calculate number of
encounters by department
(
SELECT DEPARTMENT_ID
,COUNT(*) TotalEncs
FROM PAT_ENC
GROUP BY DEPARTMENT_ID
)
,NumEncsByDeptType AS --CTE to calculate number of
encounters by department and type
(

Clarity SQL Fundamentals Epic 2017 Training Companion

8•20
Using Subqueries

SELECT DEPARTMENT_ID
,ENC_TYPE_C Type
,COUNT(ENC_TYPE_C) NumEncsByType
FROM PAT_ENC
GROUP BY PAT_ENC.DEPARTMENT_ID,ENC_TYPE_C
)

--Parent query
SELECT NumEncsByDept.DEPARTMENT_ID
,NumEncsByDeptType.Type
,NumEncsByDeptType.NumEncsByType
,NumEncsByDept.TotalEncs

,100*(NumEncsByDeptType.NumEncsByType)/(NumEncsByDept.TotalEn
cs) Percentage

FROM NumEncsByDept
LEFT OUTER JOIN NumEncsByDeptType
ON
NumEncsByDept.DEPARTMENT_ID=NumEncsByDeptType.DEPARTMENT_ID

If you have a query that is having performance issues, one thing to check
is whether the query includes advanced common table expressions. In the
case of complex CTEs, occasionally the Query Optimizer is unable to
handle them efficiently.

Exercise 5: If Time Permits – Work with Common Table

Expressions
In previous exercises within this chapter you created many queries that
incorporate subqueries. Choose any of the queries you have created previously
and try replacing the subqueries with common table expressions (CTEs).

Clarity SQL Fundamentals Epic 2017 Training Companion

8•21
Using Subqueries

Reviewing the Chapter

Review Questions

1. What clauses in SQL can contain a subquery?

2. If you have multiple subqueries in the FROM clause of your parent

query, what do you need to do to combine them?

3. True or False: A query can only contain one common table expression
(CTE).

Clarity SQL Fundamentals Epic 2017 Training Companion

8•22
Using Subqueries

Review Key
1. What clauses in SQL can contain a subquery?

SELECT, FROM, WHERE

2. If you have multiple subqueries in the FROM clause of your parent

query, what do you need to do to combine them?

You need to join them together, as you would tables in the FROM clause.

3. True or False: A query can only contain one common table expression
(CTE).

False. A query can contain any number of common table expressions

(CTEs).

Clarity SQL Fundamentals Epic 2017 Training Companion

8•23
Using Subqueries

Exercise Answers
See the Cogito’s Introduction to Epic’s Certification Environments appendix to
access the answer key files.

Exercise 3: Patients’ Relative Ages

 Step-by-Step Method: Create Subquery

□ 1. You will begin by creating a subquery that returns cities where your
patients live and their ages. What table stores this information in Clarity?
PATIENT
□ 2. Create a query to display each patient’s city and age. Which function will
you need to use for age?
DATEDIFF
□ 3. You want to create ‘buckets’ for each city – you’re going to need the
average age per city and the number of patients per city. What clause will
allow you to perform aggregate functions on patients from each city?
GROUP BY

 Step-by-Step Method: Create Parent Query

□ 7. Run your query. You now have the aggregate data you need, but you
don’t have any patient-level information. You need to display each
patient’s name and ID. Which table stores that information?
PATIENT
□ 8. When you have a subquery in the FROM clause, you are allowed to join
this to other tables. What column in your subquery is also in the above
table?
CITY

Clarity SQL Fundamentals Epic 2017 Training Companion

8•24
Using Subqueries

Study Checklist
 Make sure you can define the following key terms:
 Subquery
 Scope
 Common Table Expression
 Make sure you can perform the following tasks:
 Include a subquery in the SELECT, WHERE, and FROM clauses
 Include a common table expression in a SQL query
 Make sure you fully understand and can explain the following concepts:
 When to use a subquery
 The different effects of including a subquery in the SELECT, WHERE,
and FROM clauses

Clarity SQL Fundamentals Epic 2017 Training Companion

8•25
Using Subqueries

Clarity SQL Fundamentals Epic 2017 Training Companion

© 2017 Epic Systems Corporation. Confidential.
Appendix A
Capstone Exercise
Capstone Exercise 3
Reporting on Epic-Crystal Templates 4
A•2
Capstone Exercise

Clarity SQL Fundamentals Epic 2017 Training Companion

A•3
Capstone Exercise

Capstone Exercise
In this Capstone Exercise you will have a chance to apply your knowledge from
CLR110 Clarity Data Model Fundamentals, including topics such as Finding Data
in Chronicles and Integrating a Crystal Report in to Hyperspace, in order to write
a report about Epic-Crystal Templates in SQL.
On the following page, there is a report request from an end user. Since you
learned in CLR110 how to create an Epic-Crystal Template, you can follow the
proper workflows to find items to CTRL + Left Click or search for known values
in the Record Viewer. Use tools like the Clarity Compass to 1) map the INI and
Item Numbers you find to Tables and Columns in Clarity, and 2) figure out how
the tables you need are related to each other, in order to write a SQL query to
satisfy the report request.

Clarity SQL Fundamentals Epic 2017 Training Companion

A•4
Capstone Exercise

Reporting on Epic-Crystal Templates

Your organization would like to see what reports are Epic-Crystal integrated and
some details about those reports.
Use your knowledge of Epic-Crystal Integration from Clarity Data Model
Fundamentals to create a query that finds Epic-Crystal templates. Include the
name of the Epic-Crystal template, the input file, the data model, and the output
format. Filter your report to only include Epic-Crystal templates run on the
MONTHLY batch job.
If you have additional time, display the list of parameters and the default value(s)
for each of those parameters.

Clarity SQL Fundamentals Epic 2017 Training Companion

A•5
Capstone Exercise

Clarity SQL Fundamentals Epic 2017 Training Companion

© 2017 Epic Systems Corporation. Confidential.
Appendix B
Handouts
CLR130 Lessons 1-3 Review 3
CLR130 Lessons 1-3 Review Answers 5
B•2
Handouts

Clarity SQL Fundamentals Epic 2017 Training Companion

B•3
Handouts

CLR130 Lessons 1-3 Review

1. Did you sign in to class today? Use the Daily Sign In link on the Epic
training center.

2. You want to find patients in Madison without a current primary care

provider. You want to list their name, date of birth, sex, and the value
of CUR_PCP_PROV_ID (to verify they have no provider). However,
your query does not run, and even if it did it wouldn’t return the right
information. Find why the query doesn’t return what you need. There
will be six reasons for this.
3. If you have time, find potential style improvements. There are a few
ways this query could be better written.

Clarity SQL Fundamentals Epic 2017 Training Companion

B•4
Handouts

Across Down
1. Epic's umbrella term for 2. (False AND True) OR (False OR
reporting and analytics True)
5. SQL clause for filtering 3. CASE WHEN ____ ELSE END
7. Sorting column values to 4. This database object is a
speed up queries query that is stored in the
8. First clause processed database and does not actually
in a SQL query store results
9. Keyword that only 6. Not Unknown
returns unique column 10. SQL uses _____ Value Logic
values 11. The output of a query
14. Character that selects 12. "String" in a relational
all the columns in a table database
15. /* This */ 13. Keyword to limit our results
16. The first word in a to a number of rows or a
query percentage of total rows

Clarity SQL Fundamentals Epic 2017 Training Companion

B•5
Handouts

CLR130 Lessons 1-3 Review Answers

1. Did you sign in to class today? Use the Daily Sign In link on the Epic
training center.

2. You want to find patients in Madison without a current primary care

provider. You want to list their name, date of birth, sex, and the value
of CUR_PCP_PROV_ID (to verify they have no provider.) However,
your query does not run, and even if it did it wouldn’t return the right
information. Find why the query doesn’t return what you need. There
will be 6 reasons for this.
3. Style improvements: Consistency with column aliases (double-quotes
on all), consistency with table aliases (pat.BIRTH_DATE), others?

Clarity SQL Fundamentals Epic 2017 Training Companion

B•6
Handouts

Clarity SQL Fundamentals Epic 2017 Training Companion

B•7
Handouts

Clarity SQL Fundamentals Epic 2017 Training Companion

© 2017 Epic Systems Corporation. Confidential.