Re: [MEDITECH-L] ** A bit off topic ** SQL code to split employee

[TSL...@cho.org]

Ganesh:

I think my very first impression of Meditech was one of genuine horror
when I found out how they store their patient name data. You are
completely correct. It is a mess. Aside from missing prefix salutations
and suffix titles, you can never extract a middle name or initial with
any confidence that the data is correct. I have done some studies
on our data and found that about 15% of the time our staff has put
junk data where one would expect the middle name. For example:

SMITH,JOHN AKA "JACK"

A middle name field would solve a lot of problems.

That said, string extraction in SQL is no more cumbersome than
Magic. Here is some code for first and last name extraction
from Meditech data:

LastName = LEFT(BarVisits.Name, CHARINDEX(',',BarVisits.Name)-1),
FirstMidName = RIGHT(BarVisits.Name, (LEN(BarVisits.Name) - CHARINDEX(',',BarVisits.Name))),

It's not pretty, but Magic isn't any better. In fact, Magic doesn't have a
simple SUBSTRING function like SQL and most other languages have.

The code above was written by a co-worker of mine. I have cut and pasted it to
new routines about a thousand times. Of course, one could turn it into a user defined
function pretty easily instead.

Thomas Stephen Laxar
Coordinator, Hospital Information Systems
Children's Hospital & Research Center Oakland
510-428-3833
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).

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Tue Dec 29, 2009 12:19 pm From: Ganesh Seshadri <gse

Subject: Re: [MEDITECH-L] ** A bit off topic ** SQL code to split employee
To: "Purvins, James A." <Pur...@inhs.org>,
"Laura Wilson" <lwi...@tanner.org>, <medit...@mtusers.com>,
<CSLis...@SISUnet.org>
From: Ganesh Seshadri <gses...@indianarmc.org>
Date: Tue, 29 Dec 2009 15:14:09 -0500

I share the same worries with James and feel this pain every day when I
try to get data off DR. I generally end up exporting to excel or access
and using the built-in functions there. To get a bit more off-topic,
regarding this issue, I still feel the fundamental problem is the way
the name/suffix/title data is stored in meditech. Hopefully their
future versions will have cleaner ways in which parts of the name and
appendages are stored.
Regards, ganesh seshadri
Indiana Regional

-----Original Message-----
From: meditech-...@mtusers.com
[mailto:meditech-...@mtusers.com] On Behalf Of Purvins, James A.
Sent: Tuesday, December 29, 2009 2:43 PM
To: Laura Wilson; medit...@mtusers.com; CSLis...@SISUnet.org
Subject: [MEDITECH-L] ** A bit off topic ** SQL code to split employee
namein Data Repository

This is the kind of stuff that I worry we NPR programmers will no longer
be able to do with ease...when it comes to Meditech's next generation.

String manipulation in Magic is so much easier and less verbose than
those SQL statements.

Is Meditech really going to do away with all that programming
functionality that Magic offers and move towards a Wizards only
programming environment?

Not that I am totally enamored writing Magic code, but building a SQL
function to do string extraction????

That's kind of like writing a Compiler using a patch panel.....its
downright going back to the Stone Age !!

FWIW and My 2 cents,

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 Laura Wilson
Sent: Tuesday, December 29, 2009 10:41 AM
To: medit...@mtusers.com; CSLis...@SISUnet.org
Subject: [MEDITECH-L] SQL code to split employee name in Data Repository

Has anyone written SQL code to split the employee name into separate
fields (last name, first name, middle name)? I am not a SQL guru by any
means and am struggling to handle all the various ways the name can be
entered.

Ex.
Jones,Tom
Jones,Tom A.
Jones,Tom Alvin
Jones III,Tom
Jones III,Tom A.
Jones Jr.,Tom Alvin
And so on....

Was just hoping that someone had already done this and would be willing
to share the code. (Or maybe it is stored separately already and I
haven't found it!)

Thanks in advance!

Laura Wilson Asst Dir, I.S.
[cid:image0...@01CA888C.9894AD80]
INFORMATION SYSTEMS
770.836.9791 | www.tanner.org<http://www.tanner.org/>

------------------------------------------------------------------------
--------------------------------------------
This e-mail and any files or attachments transmitted with it contain
information that is confidential and privileged. This information is
intended only for the use of the individual(s) and entit(ies) to whom it
is addressed. If you are not the intended recipient, any disclosure,
copying, printing, or use of this information is strictly prohibited and
possibly a violation of federal or state law and regulations.
If you have received this information in error, please contact the
sender immediately and destroy all copies of this communication.




====================================

Please do NOT send messages that ask "Please post to the list" These
are useless messages that just waste the email server's resources.
Instead, email the original requester and ask that they send you or post
the results of their question.

To UNSUBSCRIBE, go to http://MTUSers.net
for information.

The meditech-l archives (and other tips) can be found at
http://mtusers.net/zarchives

Job opportunities in the Meditech community can be found at
http://mtusers.net/zjobs




====================================

Please do NOT send messages that ask "Please post to the list" These are useless messages that just waste the email server's resources. Instead, em
ail the original requester and ask that they send you or post the results of their question.

To UNSUBSCRIBE, go to http://MTUSers.net
for information.

The meditech-l archives (and other tips) can be found at
http://mtusers.net/zarchives

Job opportunities in the Meditech community can be found at
http://mtusers.net/zjobs


--------------------------------------------------------------------------------
Return-Path: <Ganesh Seshadri <gses...@indianarmc.org>>
Received: from smtp2.mail.cho.org by cho.org; Tue, 29 Dec 2009 12:19:57 0000
Received: from smtp2.mail.cho.org ([192.168.1.39])
by smtp2a.mail.cho.org with ESMTP; Tue, 29 Dec 2009 12:19:44 -0800
X-ASG-Debug-ID: 1262117980-075d3bd40001-tQnRVc
Received: from host3.emwd.com (host3.emwd.com [72.52.162.75]) by smtp2.mail.cho.org with ESMTP id UwEnQMEh5qlE8hV4 for <tsl...@cho.org>; Tue, 29 Dec
2009 12:19:40 -0800 (PST)
X-Barracuda-Envelope-From: meditech-...@mtusers.com
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 1NPiU4-00051w-G4; Tue, 29 Dec 2009 15:16:00 -0500
Received: from tx2ehsobe005.messaging.microsoft.com ([65.55.88.15]
helo=TX2EHSOBE010.bigfish.com)
by host3.emwd.com with esmtps (TLSv1:RC4-MD5:128) (Exim 4.69)
(envelope-from <gses...@indianarmc.org>) id 1NPiU0-00051I-4o
for medit...@mtusers.com; Tue, 29 Dec 2009 15:15:58 -0500
Received: from mail150-tx2-R.bigfish.com (10.9.14.253) by
TX2EHSOBE010.bigfish.com (10.9.40.30) with Microsoft SMTP Server id
8.1.240.5; Tue, 29 Dec 2009 20:15:57 +0000
Received: from mail150-tx2 (localhost.localdomain [127.0.0.1]) by
mail150-tx2-R.bigfish.com (Postfix) with ESMTP id 67569F10357;
Tue, 29 Dec 2009 20:15:56 +0000 (UTC)
X-SpamScore: -37
X-BigFish: VPS-37(zzfcbW542Na0dJ1443N9371P1047izz1202hzzz2fh362I6bh61h)
X-Spam-TCS-SCL: 0:0
Received: from mail150-tx2 (localhost.localdomain [127.0.0.1]) by mail150-tx2
(MessageSwitch) id 1262117654442080_29440;
Tue, 29 Dec 2009 20:14:14 +0000 (UTC)
Received: from TX2EHSMHS035.bigfish.com (unknown [10.9.14.235]) by
mail150-tx2.bigfish.com (Postfix) with ESMTP id 6CAAC8480A8;
Tue, 29 Dec 2009 20:14:13 +0000 (UTC)
Received: from exchange-fe.irmc.local (68.234.38.5) by
TX2EHSMHS035.bigfish.com (10.9.99.135) with Microsoft SMTP Server (TLS)
id 14.0.482.32; Tue, 29 Dec 2009 20:14:11 +0000
Received: from exchange-vs.irmc.local ([10.1.0.151]) by exchange-fe.irmc.local
with Microsoft SMTPSVC(6.0.3790.3959);
Tue, 29 Dec 2009 15:14:10 -0500
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-Class: urn:content-classes:message
MIME-Version: 1.0
Date: Tue, 29 Dec 2009 15:14:09 -0500
Message-ID: <0EB7D2D8E49D0A4785E4...@EXCHANGE-VS.IRMC.LOCAL>
In-Reply-To: <84D8A8FE478E4543BC22...@IRMEXCH01.irm.inhs.org>
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
Thread-Topic: [MEDITECH-L] ** A bit off topic ** SQL code to split employee
name in Data Repository
Thread-Index: AcqItoFvBxKeCTUuQc+ljdCjQNZw4AAB6kmgAAEZNMA=
References: <A8F640CD2D60FB49922C1...@EX2007MAIL.ths.local>
<84D8A8FE478E4543BC22...@IRMEXCH01.irm.inhs.org>
From: Ganesh Seshadri <gses...@indianarmc.org>
To: "Purvins, James A." <Pur...@inhs.org>,
"Laura Wilson" <lwi...@tanner.org>, <medit...@mtusers.com>,
<CSLis...@SISUnet.org>
X-OriginalArrivalTime: 29 Dec 2009 20:14:10.0461 (UTC)
FILETIME=[7B72ECD0:01CA88C3]
X-Reverse-DNS: 5-38-234-68.static.stingcomm.net
X-Spam-Status: No, score=-3.5
X-Spam-Score: -34
X-Spam-Bar: ---
X-Spam-Flag: NO
X-ASG-Orig-Subj: Re: [MEDITECH-L] ** A bit off topic ** SQL code to split employee
name in Data Repository
Subject: Re: [MEDITECH-L] ** A bit off topic ** SQL code to split employee
name in Data Repository
X-BeenThere: medit...@mtusers.com
X-Mailman-Version: 2.1.12.cp3
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: 1262117980
X-Barracuda-URL: http://192.168.1.39:8000/cgi-mod/mark.cgi
X-Virus-Scanned: by bsmtpd at mail.cho.org

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

====================================

Please do NOT send messages that ask "Please post to the list" These are useless messages that just waste the email server's resources. Instead, email the original requester and ask that they send you or post the results of their question.

To UNSUBSCRIBE, go to http://MTUSers.net
for information.

The meditech-l archives (and other tips) can be found at
http://mtusers.net/zarchives

Job opportunities in the Meditech community can be found at
http://mtusers.net/zjobs