Transcript Care Plan SQL April 25, 2015 Presented by: Greg Anderson CEO

2015 User Conference
Care Plan SQL
April 25, 2015
Presented by:
Greg Anderson
CEO
EHR Workshop 491
Agenda
▪ SQL database structure
(reference before workshop)
▪ Item Types and Items
▪ CQM Lists
▪ Trigger SQL in Items
▪ Putting it all together:
Appointment Reasons
▪ Homework: Appointment Types
▪ Q&A
2015 Office Practicum User Conference
SQL Database Structure
▪ In simplest terms, an SQL database contains
entities and relationships
▪ Entities model real world objects
▪
▪
▪
▪
Patient (REGISTER)
Encounter (ENC_NOTE, PHYSICAL)
Appointment (SCHEDULE)
Problem (CHARTNOTES)
▪Each entity is stored in one or more tables,
each of which has properties stored in fields
▪
▪
▪
Last name (LNAME)
Gender (SEX)
Birth date (BIRTHDAT)
2015 Office Practicum User Conference
SQL Database Tables and Fields
▪ Patient REGISTER (first 30 field definitions)
2015 Office Practicum User Conference
SQL Database Relationships
▪ Relationships define how the tables work
together to form an entire picture of the real
world problems modeled in the database
▪ Frequently referenced relationships are
defined in indexes to facilitate rapid access
▪ Well designed databases use the power of
relationships to minimize or eliminate
duplicate data and maximize reuse
2015 Office Practicum User Conference
SQL Entity/Relationship Diagram
▪ SCHEDULE with related entities
2015 Office Practicum User Conference
SQL Table Data
▪ Table contains multiple rows of data, one for
each instance of the entity type it contains
2015 Office Practicum User Conference
SQL SELECT Statements
▪ SELECT statements are sentences with a
specific vocabulary and grammar that define
the rules to fetch a subset of available data,
according to the following parameters:
▪ Which fields
▪
▪
▪
▪
Which tables
Which rows within tables
Which order
Which groups
SELECT
FROM, JOIN
WHERE
ORDER BY
GROUP BY
▪Complete SELECT syntax contains additional
parameters, but those listed above are the
most commonly used for care plans
2015 Office Practicum User Conference
SQL SELECT Statement Example
The following SQL SELECT statement fetches a
list of the last name, first name, sex, and birth
date for all active female patients who were born
since January 1, 2010:
SELECT LNAME, FNAME, SEX, BIRTHDAT
FROM REGISTER
WHERE (STATUS_PAT = ‘ACTIVE’)
AND (SEX = ‘F’)
AND (BIRTHDAT >= ‘01/01/2010’)
ORDER BY LNAME, FNAME
The results are sorted by last and first name
2015 Office Practicum User Conference
SQL SELECT Parameters
A parameter is a variable whose value is determined when
the SELECT statement is run. (Either the user can provide
the values, or the system can provide them
programmatically.) Parameters always begin with a “:”.
SELECT PATNO, INVOICENO, CPTCODE
FROM ARCHIVE_TRANSACTIONS
WHERE (DATE1 BETWEEN :SDATE AND :EDATE)
In this example, :SDATE and :EDATE represent start and
end dates for a range of transactions. By not hard-coding
the dates into the query, it can be reused for any desired
period of time.
2015 Office Practicum User Conference
Creating Action SQL in Item Types
▪ Item Types are the elemental atoms of data
mining - one type of thing found on one
section of a patient chart
▪ Very easy when the content of all records is
similar except for the patient they apply to
▪ Encourages reuse when only one property of
a record varies between use cases
▪
Diagnostic tests - only difference between HGB
and LEAD is the name or LOINC
▪
Medication - only difference between Adderall and
amoxicillin is the name or RxNorm or NDC
2015 Office Practicum User Conference
Standard Care Plan Item Types
Section
Chart
Elements
2015 Office Practicum User Conference
Action Item Data Mining Fields
▪ All data mining related to actions must return
a specific collection of fields - converts
heterogeneous data to a single standard
▪
Item_ID - the unique identifier for this record in the
table it comes from
▪
Item_Date - the effective date as of which this
record exists for the purpose of recall intervals
▪
Item_Value - freeform text in “Most Recent Info”
▪
Item_Flag - (optional) text indicating whether the
record is classified as normal or abnormal
▪
Item_Dx - (optional) text for records that have a
diagnosis element
2015 Office Practicum User Conference
Action Item Data Mining: Vaccines
▪ Let’s look at a simple example:
▪
How do you find the most recent administration of
a vaccine or antigen for a specific patient, to see if
they need or are due for another?
▪Step 1: Determine which primary table
contains the required data (VACCINE1)
▪Step 2: Decide if any additional tables need
to be joined for a complete dataset
▪Step 3: Identify which field(s) within the
table(s) are required to answer the question
2015 Office Practicum User Conference
Action Item Data Mining: Vaccines
▪
▪
▪
▪
▪
PATNO = patient ID
VACNAME = vaccine name
VACDATE = admin date
CVXCODE = CVX code
ID = unique ID
Question: which field is best
for identifying the vaccine
(VACNAME or CVXCODE)?
2015 Office Practicum User Conference
Action Item Data Mining: Vaccines
▪ VACNAME = “FLU-LAIV4 2-49yrs”,
“FLU-IIV4 3yrs+ pf”, “FLU-IIV4 6-35m pf”, etc.
▪ CVXCODE = “149”, “150”, “153”, etc.
▪ In both cases, there are lists of qualifying
items (even if just trying to find nasal flu)
▪
Almost all clinical data value sets are lists
▪
Subject to year-over-year variability
2015 Office Practicum User Conference
Creating a Vaccine Action Item Type
▪ General form for an action Item Type to find
the most recent administration of a specific
vaccine or antigen for a specific patient:
SELECT FIRST 1
ID as Item_ID,
VACDATE as Item_Date,
VACNAME as Item_Value
FROM VACCINE1
WHERE (PATNO = :patno)
AND (CVXCODE [???])
ORDER BY VACDATE DESC
2015 Office Practicum User Conference
only need 1 record
as creates an alias
reference date
“Most Recent Info”
primary table
PATNO required!
varying property
sort by most recent
Vaccine Action Item - Flu Criteria
▪ To find most recent flu immunization, what
goes here?
AND (CVXCODE [???])
Can’t use “=” because a list of CVX codes
maps to some formulation of flu vaccine
▪Might have considered a different rule:
AND (VACNAME LIKE ‘%FLU%’)
But what about IPV vs. OPV? Can’t say
AND (VACNAME LIKE ‘%PV%’)
(why not?)
2015 Office Practicum User Conference
Action Item Data Mining: Flu
▪ Best practice: IN list management operator
with the most unambiguous coded value
AND (CVXCODE IN ("15","16","88","111",
"125","126","127","128","135","140",
"141","144","149","150","153","155",
"158","161"))
(yes, the list really is that long as of 2015!)
▪ So now that we know the search condition,
where does it go?
2015 Office Practicum User Conference
Action Item Type Data Mining: Flu
▪ Option 1: Create an Item Type specifically for
flu vaccines
SELECT FIRST 1
ID as Item_ID,
VACDATE as Item_Date,
VACNAME as Item_Value
FROM VACCINE1
WHERE (PATNO = :patno)
AND (CVXCODE IN ("15","16","88","111",
"125","126","127","128","135","140",
"141","144","149","150","153","155",
"158","161"))
ORDER BY VACDATE DESC
2015 Office Practicum User Conference
Action Item Type Data Mining: Flu
▪ Option 1 advantages:
▪
Everything is in one place
▪
Has a single purpose, easily understood
▪ Option 1 disadvantages:
▪
Core logic must be replicated for each antigen,
even though method is always the same
▪
Difficult to find and maintain the items on the list
when they (inevitably) change
▪
Probably not the only time flu vaccines will be the
subject of data mining (CQMs, QI, etc.)
2015 Office Practicum User Conference
Action Item Type Data Mining: Flu
▪ Option 2: Create an Item Type specifically for
flu vaccines, but standardize and substitute
the coded list
SELECT FIRST 1
ID as Item_ID,
VACDATE as Item_Date,
VACNAME as Item_Value
FROM VACCINE1
WHERE (PATNO = :patno)
AND (CVXCODE IN (:CQM_LIST_CVX_FLU))
ORDER BY VACDATE DESC
2015 Office Practicum User Conference
CQM Lists
▪ CQM Lists benefit all data mining, both Care
Plans and QIC measures
2015 Office Practicum User Conference
Defining CQM Lists
▪ List Name is user-assigned, follows a simple
formula:
CODESET_PURPOSE
CODESET is CVX, LOINC, PROC, RXNORM,
etc. So CVX_FLU means a list of CVX codes
that define the value set for flu vaccines.
▪ List Contents is a comma-delimited list of
items in double-quotes:
"15","16","88","111","125","126","127",
"128","135","140","141","144","149",
"150","153","155","158","161"
2015 Office Practicum User Conference
Using CQM Lists: Flu
Think of a CQM List as a variable whose value is
substituted at runtime. You can put it anywhere
in your SQL as :CQM_LIST_ + the List Name
whose contents should be substituted:
AND (CVXCODE IN (:CQM_LIST_CVX_FLU))
Which at runtime will expand to:
AND (CVXCODE IN ("15","16","88","111",
"125","126","127","128","135","140",
"141","144","149","150","153","155",
"158","161"))
2015 Office Practicum User Conference
Benefits of CQM Lists
▪ Reusable across Items and Item Types
▪ Used by both care plans and CQMs - keeps
the rules consistent
▪ Maintainable as lists change - don’t need to
understand everything about how to build
care plans or CQMs in order to manage the
lists that drive them
▪ Best practice: unless data mining is driven by
a single value (and you know it always will
be), make a list
2015 Office Practicum User Conference
Action Item Data Mining: Flu
▪ Option 3: Create an Item Type that works for
all vaccines, then create specific Care Plan
Items for different antigens
SELECT FIRST 1
ID as Item_ID,
VACDATE as Item_Date,
VACNAME as Item_Value
FROM VACCINE1
WHERE (PATNO = :patno)
AND (CVXCODE :ITEM_SQL)
ORDER BY VACDATE DESC
2015 Office Practicum User Conference
Action SQL in Items
▪ Just as CQM Lists can be substituted at
runtime, so can the specific condition that
distinguishes one use of an Item Type
▪ :ITEM_SQL is a special parameter that tells
the care plan engine to find the answer in the
Item itself
2015 Office Practicum User Conference
Action SQL in Items
So …
AND (CVXCODE :ITEM_SQL)
in “Flu Vaccine” care plan Item
becomes
AND (CVXCODE IN (:CQM_LIST_CVX_FLU))
in “Vaccine” care plan Item Type,
which becomes
AND (CVXCODE IN ("15","16","88","111",
"125","126","127","128","135","140",
"141","144","149","150","153","155",
"158","161"))
when CQM List is expanded. Neat, eh?
2015 Office Practicum User Conference
Trigger SQL in Items
▪ Triggers are conditions that determine
whether data exists of a certain type
▪ All triggers take the form:
Item Name Condition Value
▪ Care plan engine builds a WHERE clause in
which Trigger SQL text replaces Item Name
▪ Helpful hint: All patient demographics are
always available by referencing r.field_name
2015 Office Practicum User Conference
Trigger SQL in Items
Bright Futures has this set of triggers:
which substitutes Patient Status:
and then Age in Years:
2015 Office Practicum User Conference
Trigger SQL in Items
Which turns into this SQL:
WHERE ((r.status_pat = ‘ACTIVE’)
AND ((current_timestamp-r.birthdat)/365.25 <= 21))
Remember, all triggers ultimately resolve to a
True or False response
2015 Office Practicum User Conference
Freestanding Trigger SQL in Items
Sometimes the Trigger SQL contains everything
needed to return a True/False response
In this case, simply leave the Condition and Value blank when you use the Item in a trigger.
2015 Office Practicum User Conference
Ready to Roll Your Own?
Let’s
Try It!
2015 Office Practicum User Conference
Hands-On: Appointment Reasons
▪ Let’s build the data mining for this problem:
▪
How do you find the most recent appointment for
a specific patient related to asthma care?
▪Step 1: Determine which table contains the
required data (SCHEDULE)
▪Step 2: Decide if any additional tables need
to be joined for a complete dataset (no, but
need list of qualifying template names)
▪Step 3: Identify which field(s) within the
table(s) are required to answer the question
2015 Office Practicum User Conference
Action Item Data Mining: Appt Reasons
▪ PATNO = patient ID
▪ APPT_DATE = visit date
▪ ENC_TEMPLATE = reason
▪ CODE1 = appt type
▪ VISIT_STATUS = status
▪ UNIQKEY = unique ID
2015 Office Practicum User Conference
Creating an Appt Reason Item Type
▪ General form for an action item type to find the
most recent checked-in visit from a list of
specific templates for a specific patient:
SELECT FIRST 1
only need 1 record
UNIQKEY as Item_ID,
as creates an alias
APPT_DATE as Item_Date,
reference date
ENC_TEMPLATE as Item_Value
“Most Recent Info”
FROM SCHEDULE
primary table
WHERE (PATNO = :patno)
PATNO required!
AND (VISIT_STATUS IN
ignore cancel/
(‘Complete’, ‘Superbill Exist’))
no show
AND (ENC_TEMPLATE :ITEM_SQL) be flexible!
ORDER BY APPT_DATE DESC
sort by most recent
2015 Office Practicum User Conference
Creating an Appt Reason Item Type
▪ Plug this into the Care Plan Item Types grid:
2015 Office Practicum User Conference
Creating an Appt Reason CQM List
Next figure out which template names satisfy the
requirement for asthma care or follow up:
2015 Office Practicum User Conference
Creating an Appt Reason Item List
Then carefully transcribe the names to a new
CQM List:
Since there is no specific codeset, just use an
obvious prefix and name. You may make
numerous template name sets for different
conditions, so be consistent.
2015 Office Practicum User Conference
Creating a Care Plan Item for Asthma
Go to Care Plan Items tab, define a new item:
▪ Item Name can be anything but should be
very obvious, because it appears everywhere
▪ Note that Action SQL takes advantage of
maximum reusability
2015 Office Practicum User Conference
Adding a Care Plan Item to a Care Plan
Finally, go to the Care Plans/Registries tab and
add new Item to the Asthma - Moderate plan:
We’re done! The most recent asthma-related
appointment for all patients enrolled in the
Asthma - Moderate plan will now appear in their
personal care plan.
2015 Office Practicum User Conference
Homework: Appointment Types
▪ Your homework assignment (if you choose to
accept it :) is to create a similar collection of
Item Type, CQM List, and Item for the
Appointment Type field on the schedule
(remember, it is illogically named CODE1)
▪ Possible uses:
▪
Most recent scheduled well visit (based on your
list of well visit appointment types)
▪
Group or prenatal counseling
2015 Office Practicum User Conference
Questions
?
2015 Office Practicum User Conference
We want your feedback!
2015 Office Practicum User Conference