All,
This should get us started on visit types. This syntax is in Netezza, and I
merely pulled a date range. Also if you're querying from Clarity, remove the
database and "STG_" prefix. The two categories of the value "-- ??" means the
patient could potentially be deceased. More likely than not, your organization
will have some custom category value for these purposes, and might not even use
the standard release range of category values.
So not perfect, but it's a start.
SELECT DISTINCT ENC.PAT_ENC_CSN_ID, ENC.*
FROM
QA_ETL..STG_PAT_ENC ENC
JOIN QA_ETL..STG_ZC_APPT_STATUS APPT ON ENC.APPT_STATUS_C = APPT.APPT_STATUS_C
JOIN QA_ETL..STG_ZC_DISP_ENC_TYPE ENCTYPE ON ENC.ENC_TYPE_C =
ENCTYPE.DISP_ENC_TYPE_C
WHERE
ENC.CONTACT_DATE >= to_date('20130101', 'yyyymmdd')
AND ENC.CONTACT_DATE < to_date('20140101', 'yyyymmdd')
AND APPT.NAME = 'Completed' -- not using category value since some
organizations duplicate their appointment status categories for various reasons
-- commenting out any 'STANDARD' release range values that we are not
confident the provider had patient contact (patient interaction via electronic,
phone or in person)
-- registration does not count since the provider does not interact
with the patient at this time
-- any custom category values that are merely a specific type of one of
the standard category values, or is brand new, will need to be decided on
AND ENCTYPE.DISP_ENC_TYPE_C IN (
-- EPT-30 value EPT-30 name
--1, --1-Registration
-- no provider
at this time
--2, --2-Walk-In
-- this changes
to some other visit type
3, --3-Hospital Encounter
--5, --5-Canceled
--6, --6-Unmerge
--7, --7-Contact Moved
--10, --10-EMPTY
11, --11-Research Encounter
-- patient is
asked research related questions during visit
--20, --20-Treatment Plan
-- human contact
initiated on related visit
--30, --30-Update
31, --31-PCP/Clinic Change
--40, --40-Wait List
-- put on wait
list for appointment at certain date/time
45, --45-Clerical Orders
-- follows
patient contact
48, --48-Mother Baby Link
49, --49-Lactation
Encounter
--50, --50-Appointment
-- ?? 51, --51-Surgery
52, --52-Anesthesia
53, --53-Anesthesia Event
--60, --60-Health Maintenance
Letter -- can't guarantee patient read
medical letter
61, --61-Patient Email
62, --62-E-Visit
63, --63-Mobile Order Only
64, --64-Questionnaire
Series Submission
69, --69-Patient Outreach
-- patient was
called to meet quality measures
70, --70-Telephone
71, --71-Nurse Triage
72, --72-E-Consult
76, --76-Telemedicine
--80, --80-External Contact
-- can't be confident
this was with a patient
81, --81-Ophth Exam
85, --85-Hospice Admission
90, --90-Home Health
Admission
91, --91-Home Care Visit
92, --92-Home Care Update
95, --95-Patient Web Update
96, --96-Community Orders
--97, --97-Committee Review
-- ?? 98, --98-Post Mortem
Documentation -- only include if post
mortem visits (ie: organ donation surgery, etc..) is included
99, --99-Billing Encounter
-- billing
purposes
101, --101-Office Visit
-- ?? 102, --102-Consent Form
-- this might occur before or
after patient is deceased
104, --104-External Hospital
Admission
--105, --105-Letter (Out)
-- used in Letter
Manager functionality
106, --106-Hospital
107, --107-Refill
108, --108-Immunization
--109, --109-History
110, --110-Referral
-- patient
interaction should occur before referral
111, --111-Orders Only
-- patient interaction
should occur before order
--112, --112-Rx Refill Authorize
113, --113-Meds Only (Web)
-- MyChart Med Order
--114, --114-Meds Void (Web)
--115, --115-Resolute Professional
Billing Hospital Prof Fee -- billing purposes
116, --116-Episode Changes
-- patient interaction should occur before this
--117, --117-Ancillary Orders
118, --118-Pharmacy Visit
119 --119-BPA
-- Hyperspace dialog requiring
provider to ask patient about something
--150 --150-Abstract
)
AND VISIT_PROV_ID IS NOT NULL
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Wanta Keith M
Sent: Tuesday, October 28, 2014 5:38 PM
To: 'Dan Connolly'; [email protected]; Mish Thomas;
[email protected]
Cc: Tamara McMahon; Theresa Shireman; Jianghua He
Subject: RE: [gpc-informatics] #178: determine whether last.visit is the last
fulfilled visit or the last scheduled visit
Note, I'm only considering the standard release range category values for
EPT-30 in this message.
Does last visit need to have some human contact (either physical or via phone
or electronic communication)?
Since visits in i2b2 are generally captured with a provider (unless you're
doing genetics sequencing or something), you normally need to have the actual
provider. So I think a completed appointment would only be considered with a
provider, correct? Since on a canceled, no show, or other that's not
completed, would not have a confirmed provider.
There are exceptions to this rule. A nurse can be considered a patient's
provider for a visit. In that case, we would have no provider, so those visits
would likely seem as important as those without a provider.
Visit types such as 20-Treatment Plan or Appointment can turn into other visit
types. Since we're dealing with NAACR data, a treatment plan could turn into
several other visits for chemo treatment (for each visit) for example. But
they do not confirm you have an actual visit where the patient was seen.
116-Episode changes might mean there will be future visits, but doesn't
necessarily mean we have a visit. For example, we might have a pregnancy, and
everything will be billed for that one episode at the end of it. That first
encounter might not mean we had actual human contact with the patient, but
maybe we did.
Are we considering auxiliary (needed for medical staff to access EHR without
patient) or ancillary system visit types (needed for interfaces/Bridges to move
data into Epic)? We don't really have patient/human contact, but there might
have been from the interfaced medical software.
I'd personally choose to throw away the following since we're dealing with
things outside patient care, or they are just based on Epic Hyperspace
workflows, or because they involve potential future visits (e.g. Wait List) or
other unrelated patient activity.
5-Canceled
6-Unmerge
7-Contact Moved
10-EMPTY
30-Update
97-Committee Review
98-Post Mortem Documentation
99-Billing Encounter
109-History
110-Referral
112-Rx Refill Authorize
115-Resolute Professional Billing Hospital Prof Fee
Then we have the gray area visit types of whether there was human contact or
not. This would require more discussion. For example, 60 is the precursor for
something scheduled for the future, but we don't know if there was human
contact. And Abstract is very abstract!
30-Update
60-Health Maintenance Letter
64-Questionnaire Series Submission
71-Nurse Triage
102-Consent Form
105-Letter (Out)
150-Abstract
Then we should consider if the patient is deceased or not. That can change
whether or not we have a last visit. But a consent form (visit type 102) from
a family member for example for organ donations would technically be considered
a last visit in some doctor's books, even if the patient is deceased.
I'd like to build a query, but I'd like to weed out what is needed by PCORI
first.
-----Original Message-----
From: Dan Connolly [mailto:[email protected]]
Sent: Tuesday, October 28, 2014 5:13 PM
To: Wanta Keith M; [email protected]; Mish Thomas;
[email protected]
Cc: Tamara McMahon; Theresa Shireman; Jianghua He
Subject: RE: [gpc-informatics] #178: determine whether last.visit is the last
fulfilled visit or the last scheduled visit
Several dozen questions... for example?
i.e. please let's try to have the bulk of the technical discussion in email.
Please do throw a query together, even if only to point out why a simple one
isn't adequate.
--
Dan
________________________________________
From: Wanta Keith M [[email protected]]
Sent: Tuesday, October 28, 2014 4:12 PM
To: [email protected]; Mish Thomas; [email protected]; Dan
Connolly
Cc: Tamara McMahon; Theresa Shireman; Jianghua He
Subject: RE: [gpc-informatics] #178: determine whether last.visit is the
last fulfilled visit or the last scheduled visit
All,
This afternoon I got up to speed on visit types. I have several dozen
questions that may be relevant for this discussion. I could probably spend
some time throwing a query together, but I'd prefer to ask the questions before
helping with a query since it would get complicated quickly. This topic might
drain our time during the next call, so it might be wise to have a side
conversation before then.
During today's GPC DEV call, I briefly spoke about release ranges. This is
just Epic's term for setting aside id ranges for records and items in
Chronicles (Epic's DBMS). We won't care about standard released records since
analytical data won't be in these masterfiles which records are set aside.
They are typically used for standard workflows or Model System data (which is
not relevant to us). We would however be interested in item category values
that have a release range. One example of this would be item EPT-30. This is
the encounter type item, which gets extracted to PAT_ENC.ENC_TYPE_C column for
patient encounters. Epic releases category values 1 through 119, and 150
inclusive. Anything outside that release range, our GPC would not need any
mapped concepts for since those visit types are not standard within our GPC
Epic sites. Then we would need to find the common visit types of Cerner and
Cattails (spelling?), and only include the shared visit types for all three
EMRs.
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of GPC Informatics
Sent: Monday, October 27, 2014 10:39 AM
To: Mish Thomas; [email protected]; [email protected]
Cc: [email protected]; [email protected]; [email protected];
[email protected]
Subject: Re: [gpc-informatics] #178: determine whether last.visit is the last
fulfilled visit or the last scheduled visit
#178: determine whether last.visit is the last fulfilled visit or the last
scheduled visit
-----------------------+----------------------------
Reporter: mish | Owner: huhickman
Type: problem | Status: assigned
Priority: major | Milestone: data-quality2
Component: data-stds | Resolution:
Keywords: | Blocked By:
Blocking: |
-----------------------+----------------------------
Comment (by dconnolly):
Tom, you mentioned starting separate calls for quality issues. Do you want to
do that right away, or should I continue to track this on the regular Tuesday
calls, for now?
Hubert, please give us an update on your progress.
--
Ticket URL:
<http://informatics.gpcnetwork.org/trac/Project/ticket/178#comment:3>
gpc-informatics <http://informatics.gpcnetwork.org/>
Greater Plains Network - Informatics
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
