[MEDITECH-L] DR SQL Query Examples
Ganesh Seshadri
we're one of the 99% NPR, 1% DR hospitals when it comes to reporting.
being a sql wannabe, I was fortunate to get some SQL examples specific
to Meditech from Thomas Laxar that others too may be able to modify and
utilize at their sites. I have attached the two procedures he sent me
since both are reasonably small but instructive when it comes to doing
things like getting at CPT codes tied to charges in BAR - not an
uncommon request, but not exactly a piece of cake for me with SQL. I
hope that the listserv doesn't strip these attachments off.
so here goes, and thanks Tom for enlightening us.
regards, ganesh seshadri, indiana regional
-----Original Message-----
From: Thomas Stephen Laxar [mailto:tsl...@mail.cho.org]
Sent: Tuesday, October 14, 2008 12:13 PM
To: tsl...@mail.cho.org; Ganesh Seshadri
Subject: RE: SQL Query Examples
Ganesh:
One more thing. If you are able to send attachments to Meditech-L, it
is okay to post my samples. Others might be interested.
My Meditech-L account is MOX and we have attachment options turned off.
Thomas Stephen Laxar
Coordinator, Hospital Information Systems Children's Hospital Oakland
tsl...@mail.cho.org
phone: 510-428-3833
The email message (and any attachments) is for the sole use of the
intended
recipient(s) and may contain confidential information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply email and
destroy all copies of the original message (and any attachments).
-----Original Message-----
From: Thomas Stephen Laxar [mailto:tsl...@mail.cho.org]
Sent: Monday, October 13, 2008 10:10 AM
To: 'Ganesh Seshadri'
Subject: RE: SQL Query Examples
Ganesh:
I haven't figured out how to handle that problem either. Your idea of
unions sounds pretty good.
One strategy I have used is to start in AbstractData. All accounts have
an AdmitDateTime value in that table. The only downside is if you need
to pick up accounts in B/AR that have been cancelled. They won't exist
in ABS, but they stay in B/AR, especially if there are charges posted to
them.
I haven't been able go to MUSE in some time. We have a $0.00 education
and travel budget. I did go to the Regional meeting in San Jose
Regional a few years ago because it was close enough to commute. I was
going to pay my own fees as well, but a budget exception happened to
take care of that.
So an educational session sounds fun, but maybe not feasible in these
difficult financial times. It doesn't help that our case load is 67%
Medi-Cal (our version of Medicaid). That could put our operations
seriously at risk as the economy contracts.
As for paradigm shifts, I thought that SQL was going to be the be all
and end all in 1988. I began to study it then. Next thing I knew, I
was programming in Magic and didn't see any SQL until maybe 1998 if that
is when DR came to be. We were the second DR hospital. So it turned
out to be a lucky, if belated, guess at what would come to pass.
Thomas Stephen Laxar
Coordinator, Hospital Information Systems Children's Hospital Oakland
tsl...@mail.cho.org
phone: 510-428-3833
The email message (and any attachments) is for the sole use of the
intended
recipient(s) and may contain confidential information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply email and
destroy all copies of the original message (and any attachments).
-----Original Message-----
From: Ganesh Seshadri [mailto:gses...@indianarmc.org]
Sent: Monday, October 13, 2008 6:07 AM
To: tsl...@mail.cho.org
Subject: RE: SQL Query Examples
thanks a lot; it's logic like this that I typically need and have never
taken the time to properly learn.
Same goes for basic things like the equivalent of going through BAR.PAT
bar.acct using the adm.ser or dis.ser index - in NPR you can do it with
your eyes closed. in DR, since there isn't a field for bar.adm.ser or
bar.dis.ser that can be indexed (at least I don't know how to), what
I've done is indexed specific individual fields such as Admit.date,
Discharge.date & Service.date, select on them and then try to union the
results. Again, I don't have the faintest idea if this is a reasonable
way or if there are far more straightforward approaches.
If you haven't already, you ought to do a session at MUSE specific to
SQL as it applies to the DR. There'll be dinosaurs like me who'll
benefit greatly - perhaps those of us who thought that relational DB
will never make it because its performance in 16KB/32KB systems was too
slow to be useful in real life didn't take note when the paradigm
shifted.
As always, thanks for your help. regards, ganesh
-----Original Message-----
From: Thomas Stephen Laxar [mailto:tsl...@mail.cho.org]
Sent: Friday, October 10, 2008 5:34 PM
To: Ganesh Seshadri
Subject: RE: SQL Query Examples
Ganesh:
The tricky part here is B/AR uses different dates for inpatients and
outpatients. I would avoid that issue by using the charge service date.
The altcode really does relate to the charge, not the visit.
So, you could try something like this:
Cpt4 = ISNULL((
SELECT AltCode.Code
FROM LiveDb.dbo.DBarProcAltCodeEffectDates AltCode
WHERE Chgs.SourceID = AltCode.SourceID
AND Chgs.TransactionProcedureID =
AltCode.ProcedureID
AND AltCode.TypeID = 'CPT-4'
AND AltCode.EffectiveDateTime = (
SELECT MAX(AltCode.EffectiveDateTime)
FROM LiveDb.dbo.DBarProcAltCodeEffectDates
AltCode
WHERE Chgs.SourceID = AltCode.SourceID
AND Chgs.TransactionProcedureID =
AltCode.ProcedureID
AND AltCode.TypeID = 'CPT-4'
AND AltCode.EffectiveDateTime <=
Chgs.ServiceDateTime
)
The additional code is the last line:
AND AltCode.EffectiveDateTime <= Chgs.ServiceDateTime
Sorry about the funny indents. Email isn't the greatest for formatting
code.
Thomas Stephen Laxar
Coordinator, Hospital Information Systems Children's Hospital Oakland
tsl...@mail.cho.org
phone: 510-428-3833
The email message (and any attachments) is for the sole use of the
intended
recipient(s) and may contain confidential information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply email and
destroy all copies of the original message (and any attachments).
-----Original Message-----
From: Ganesh Seshadri [mailto:gses...@indianarmc.org]
Sent: Friday, October 10, 2008 1:19 PM
To: tsl...@mail.cho.org
Subject: RE: SQL Query Examples
Tom, hate to bug you, but just one question - if you can think of it
without wasting any of your time - instead of "SELECT
MAX(AltCode.EffectiveDateTime)" that you have listed in one of your
procedures, what would you put if you needed the most recent date not
later than service/discharge date of the account?
Again, thanks a million. regards, ganesh -----Original Message-----
From: Thomas Stephen Laxar [mailto:tsl...@mail.cho.org]
Sent: Friday, October 10, 2008 3:05 PM
To: Ganesh Seshadri
Subject: SQL Query Examples
Ganesh:
My Meditech-L account is for MOX and I can't attach files. So I am
sending you a couple of examples rather than posting them. They were
done years apart and show a transformation in my approach to coding SQL
queries.
The demographic example uses lots of JOINS. I have found that JOINS are
very time and resource intensive. So I currently will use many
subselects in the SELECT part of the query instead. This means a lot
more coding, but the performance is way better.
By the way, the WHERE statement in the example is a dummy. The real
WHERE statement references a table our own database of accounts that
have already been sent as a way of filtering out duplicates. You
wouldn't be able to run that version. This one should run as it is more
generic.
The transaction file is for a completely different profee vendor. It
references a group response that has a list of GL departments that are
using that vendor. It does list a CPT code and takes effective date
into consideration.
Let me know if you have any questions about these examples.
Thomas Stephen Laxar
Coordinator, Hospital Information Systems Children's Hospital Oakland
tsl...@mail.cho.org
phone: 510-428-3833
The email message (and any attachments) is for the sole use of the
intended
recipient(s) and may contain confidential information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply email and
destroy all copies of the original message (and any attachments).
Joan O'Neil
If so, and you're going to Eastern Regional MUSE, I'd like to meet with
you there. Joan
Ganesh:
tsl...@mail.cho.org
phone: 510-428-3833
Ganesh:
tsl...@mail.cho.org
phone: 510-428-3833
Ganesh:
AND AltCode.EffectiveDateTime <= Chgs.ServiceDateTime
tsl...@mail.cho.org
phone: 510-428-3833
Ganesh:
tsl...@mail.cho.org
phone: 510-428-3833
CONFIDENTIALITY NOTICE
Information contained in this transmission, together with any other documents or attachments, is privileged and confidential, and is intended only for the use of the individual or entity to which it is addressed. This transmission may contain information or materials protected by the applicable laws of the State of Connecticut, and /or protected health information as defined by the Health Insurance Portability and Accountability Act of 1996 (HIPAA). This information is intended exclusively for the use of the individual or entity named as addressee(s). The authorized recipient of this information is STRICTLY PROHIBITED from disclosing this information after its stated need has been fulfilled. Misuse or distribution of the information contained in this transmission is punishable by civil and/or criminal penalties under state or federal law.
If you are not the intended recipient, you are hereby notified that any disclosure, dissemination, saving, printing, copying, or action taken in reliance on the contents of these documents of this message, or any attachment, is strictly prohibited. Please notify the original sender (only) immediately by telephone or by reply e-mail and delete this message, along with any attachments from your computer immediately.
====================================
Don't forget to visit the meditech-l web site at MTUsers.net. Check out the vendors who have donated to the meditech-l. Make sure to support them for supporting you.
If you appreciate the benefits of the meditech-l, be sure to make a donation to help keep it going. The form is at MTUsers.net. Skip that next latte, fast food burger, or milk shake and donate $7, $5, or even $3 to the meditech-l today.
The meditech-l archives (and other tips) can be found at
http://mtusers.net/zarchives
Job opportunites in the Meditech community can be found at
http://mtusers.net/zjobs
Purvins, James A.
Some of that SQL source code looks pretty ugly.
Uglier than NPR in my humble opinion.
I will stick with NPR until it dies...
James Purvins
Inland Northwest Health Services
(509) 232-8384
This e-mail and any attachments are confidential and may also be
privileged. If you are not the named recipient, or have otherwise
received this communication in error, please delete it from your inbox,
notify the sender immediately by replying to the message, and do not
disclose its contents to any other person, use the information for any
purpose or store or copy the information in any medium. Thank you for
your cooperation
-----Original Message-----
From: meditech-...@mtusers.com
[mailto:meditech-...@mtusers.com] On Behalf Of Ganesh Seshadri
Sent: Tuesday, October 14, 2008 10:31 AM
To: medit...@MTUsers.com
Subject: [MEDITECH-L] DR SQL Query Examples
Ganesh:
tsl...@mail.cho.org
phone: 510-428-3833
Ganesh:
tsl...@mail.cho.org
phone: 510-428-3833
Ganesh:
AND AltCode.EffectiveDateTime <= Chgs.ServiceDateTime
tsl...@mail.cho.org
phone: 510-428-3833
Ganesh:
tsl...@mail.cho.org
phone: 510-428-3833
Joyner, Jim
Your ABS DPM (or any large dpm for that matter)
suddenly becomes many many many compliated table joins. If you are using
a visual SQL tool like
SQL Server, Access, etc. the windows/programming area becomes a jumbled
mess of tables/joins that become really hard to manage.
I think the best overall use of the DR is to use along with the
Medisolve solutions(or other similar tools). They're pretty much taken
the guess work out of writing the SQL for you.. Making it much more user
friendly.
Jim Joyner - Quality Svcs.
St. Patrick Hospital
406-327-1918
Ganesh Seshadri
that put this sql-skillset topic in context. Couple of observations for
James - 1) if you think some of the sql code looks pretty ugly, you
should try to navigate through the logic of our med recon form - a true
rube goldberg
2) the half-full cup observation is that ugly is good if you're adept
with the ugly
3) and finally on your last thought, there's a reasonable chance that
NPR is more resilient than DSR I'll get the goodbye prompt before it
does.
-----Original Message-----
From: Purvins, James A. [mailto:Pur...@inhs.org]
Sent: Wednesday, October 15, 2008 11:43 AM
To: Ganesh Seshadri; medit...@MTUsers.com
TSL...@cho.org
Jim:
I totally agree with you about using visual tools for writing SQL.
The screens do become a big mess very quickly.
There is an easy solution for that: Never use them.
Not only that, they only do JOINS which are problematical for a couple reasons:
1. JOINS are poor choice from a performance point of view. You
need to use some discretion when using this syntax. If you
need a lot of fields a table, by all means use a JOIN. If you
only need a couple, use a sub select. Sub selects are much
faster than a JOIN when the table is indexed appropriately.
2. With JOINS you run the risk of getting far more rows than you
intended. Due to the creative data entry that goes on in any
organization, you can get nearly duplicate rows where you would
absolutely expect a unique one-to-one relationship.
By in large, depending on visual tools generally constrains the range
of solutions available to you. No matter what tool you use, being able
to do some straight up coding is a necessity. That's why we code
all our SQL in the Query Analyzer editor.
The NPR report writer for example, was a crippled visual tool until
Meditech added macros. Fortunately for us, Joel Berman showed us how
to invoke a $T program from an xx. variable long before macros existed.
It was the only thing that made NPR a livable platform.
As SQL being an ugly language, well that's a pretty amusing judgment
when NPR is the standard of beauty. Beauty is completely subjective
of course. If you think NPR is more beautiful than SQL, then for you
it is.
In the last 27 years I have programmed professionally in many languages
including:
dBase (all dialects)
BASIC
Pascal
Lattice C
Visual C++
PostScript
8086 Assembler
Visual Basic
Visual Basic .NET (yes, a very different language from Visual Basic)
JavaScript
Cold Fusion
Transact SQL
NPR / $T
and endless little macro languages like Brief Macro Language and Alph RPL.
If I were to hold a beauty contest, NPR would easily hold down last place.
I can't even imagine a more awkward syntax.
Maybe 8086 Assembler could compete, but I definitely like 8086 Assembler
better. Maybe that's because I like the romance of talking to the bare
metal of the chips.
The bottom line is that they are all just tools. They all work well when
used correctly. It is best not to fall in love with your screw driver and
think it is the quintessential tool. You might be tempted to use it a chisel
with disastrous results.
Anyway, thanks a bunch. I've really enjoyed all your comments.
Thomas Stephen Laxar
Coordinator, Hospital Information Systems
Children's Hospital Oakland
tsl...@cho.org
The email message (and any attachments) is for the sole use of the intended
recipient(s) and may contain confidential information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not the
intended recipient, please contact the sender by reply email and destroy all
copies of the original message (and any attachments).
Wed Oct 15, 2008 9:49 am From: "Joyner, Jim" <jjoyn
Subject: Re: [MEDITECH-L] Kinda Off Topic : DR SQL Query Examples
To: "Purvins, James A." <Pur...@inhs.org>,
"Ganesh Seshadri" <gses...@indianarmc.org>, <medit...@MTUsers.com>
From: "Joyner, Jim" <jjo...@saintpatrick.org>
Date: Wed, 15 Oct 2008 10:48:03 -0600
Honestly.. The DR and SQL gets very complicated..
Your ABS DPM (or any large dpm for that matter)
suddenly becomes many many many compliated table joins. If you are using
a visual SQL tool like
SQL Server, Access, etc. the windows/programming area becomes a jumbled
mess of tables/joins that become really hard to manage.
I think the best overall use of the DR is to use along with the
Medisolve solutions(or other similar tools). They're pretty much taken
the guess work out of writing the SQL for you.. Making it much more user
friendly.
Jim Joyner - Quality Svcs.
St. Patrick Hospital
406-327-1918
Return-Path: <"Joyner, Jim" <jjo...@saintpatrick.org>>
Received: from smtp2.mail.cho.org by cho.org; Wed, 15 Oct 2008 09:49:23 0000
Received: from smtp2.mail.cho.org ([192.168.1.39])
by smtp2a.mail.cho.org with SMTP; Wed, 15 Oct 2008 09:48:57 -0700
X-ASG-Debug-ID: 1224089335-3c7b00340000-tQnRVc
X-Barracuda-URL: http://192.168.1.39:8000/cgi-bin/mark.cgi
Received: from host3.emwd.com (localhost [127.0.0.1])
by smtp2.mail.cho.org (Spam Firewall) with ESMTP id 69AE19E464C
for <tsl...@cho.org>; Wed, 15 Oct 2008 09:48:55 -0700 (PDT)
Received: from host3.emwd.com (host3.emwd.com [72.52.162.75]) by smtp2.mail.cho.org with ESMTP id on9rrIDeJhC0mqBW for <tsl...@cho.org>; Wed, 15 Oct
2008 09:48:55 -0700 (PDT)
X-ASG-Whitelist: Sender
Received: from localhost ([127.0.0.1] helo=host3.emwd.com)
by host3.emwd.com with esmtp (Exim 4.69)
(envelope-from <meditech-...@mtusers.com>)
id 1Kq9YA-00064C-Go; Wed, 15 Oct 2008 12:48:42 -0400
Received: from phsor-nat2.providence.org ([170.220.2.14])
by host3.emwd.com with esmtp (Exim 4.69)
(envelope-from <jjo...@saintpatrick.org>) id 1Kq9Xu-00061v-UY
for medit...@MTUsers.com; Wed, 15 Oct 2008 12:48:39 -0400
Received: from mail pickup service by phsor-nat2.providence.org with Microsoft
SMTPSVC; Wed, 15 Oct 2008 09:48:26 -0700
X-KryptiqSpooler: Handled
Received: from WNP0620K.or.providence.org ([170.220.65.132]) by
phsor-nat2.providence.org with Microsoft SMTPSVC(6.0.3790.1830);
Wed, 15 Oct 2008 09:48:19 -0700
Received: from spt-mail02.wa.providence.org ([170.173.40.40]) by
WNP0620K.or.providence.org with Microsoft
SMTPSVC(6.0.3790.1830); Wed, 15 Oct 2008 09:48:17 -0700
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3168
Importance: normal
Priority: normal
Content-Class: urn:content-classes:message
MIME-Version: 1.0
Date: Wed, 15 Oct 2008 10:48:03 -0600
Message-ID: <886507A3AFB7D54D87FD...@spt-mail02.wa.providence.org>
In-Reply-To: <84D8A8FE478E4543BC22...@IRMEXCH01.irm.inhs.org>
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
Thread-Topic: [MEDITECH-L] Kinda Off Topic : DR SQL Query Examples
thread-index: AckrCwjsqSy334JZSUODCnlTlhsHnAACb4JgAAKAHCAAhFEdYAAJUkXQADCKaqAAAgkRoAAvNPxQAAI1g5A=
From: "Joyner, Jim" <jjo...@saintpatrick.org>
To: "Purvins, James A." <Pur...@inhs.org>,
"Ganesh Seshadri" <gses...@indianarmc.org>, <medit...@MTUsers.com>
X-OriginalArrivalTime: 15 Oct 2008 16:48:17.0335 (UTC)
FILETIME=[D2A77C70:01C92EE5]
X-Spam-Status: No, score=-3.4
X-Spam-Score: -33
X-Spam-Bar: ---
X-Spam-Flag: NO
X-ASG-Orig-Subj: Re: [MEDITECH-L] Kinda Off Topic : DR SQL Query Examples
Subject: Re: [MEDITECH-L] Kinda Off Topic : DR SQL Query Examples
X-BeenThere: medit...@mtusers.com
X-Mailman-Version: 2.1.11.cp2
Precedence: list
List-Id: Email list for Meditech users <meditech-l_mtusers.com.MTUsers.com>
List-Unsubscribe: <http://mtusers.com/mailman/options/meditech-l_mtusers.com>,
<mailto:meditech-...@mtusers.com?subject=unsubscribe>
List-Post: <mailto:medit...@mtusers.com>
List-Help: <mailto:meditech-...@mtusers.com?subject=help>
List-Subscribe: <http://mtusers.com/mailman/listinfo/meditech-l_mtusers.com>,
<mailto:meditech-...@mtusers.com?subject=subscribe>
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Sender: meditech-...@mtusers.com
Errors-To: meditech-...@mtusers.com
X-AntiAbuse: This header was added to track abuse, please include it with any abuse report
X-AntiAbuse: Primary Hostname - host3.emwd.com
X-AntiAbuse: Original Domain - cho.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - mtusers.com
X-Barracuda-Connect: host3.emwd.com[72.52.162.75]
X-Barracuda-Start-Time: 1224089336
X-Barracuda-Virus-Scanned: by Barracuda Spam Firewall at mail.cho.org
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Purvins, James A.
programmer. It can be real ugly or it can be semi-readable.
I once saw a $T macro written in COBOL...kinda scarey actually...
So what $T lacks in readability is made up for the fact that its pretty
easy to write self documenting code in Magic .... if the programmer is
up to it that is...