Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

SQL to update obsvalue for EMR 9.5

General Discussion

Last Post by blackmanb 10 years ago

9 Posts

5 Users

0 Reactions

67 Views

RSS

blackmanb

(@blackmanb)

Posts: 268

Reputable Member

Topic starter

Hi,

We have been updating the SMOK STATUS obsterm with free text. What this means, is that there are values in SMOK STATUS that do not get picked up by the mu report for smoking, as it does not recognize many free-text entries. So, "Does not smoke" should have been "Never smoker", for example.

I would like help with an Update statement that would safely update "SMOK STATUS" to the appropriate values, just for this last quarter 01-OCT-14 thru 31-DEC-14.

I have 5 lists of the SMOK STATUS obsvalues that need updating, grouped by what they should be changed to.

I am not sure of the ORACLE syntax... and the below is no good, as it does not include the date range....

UPDATE MLO_.ml.obs
SET MLO_.ml.obsvalue =
CASE
WHEN (MLO_.ml.obsvalue in ('Never smoked','Does not smoke','Non Smoker','Patient is a non-smoker')) THEN 'Never smoker'
ELSE MLO_.ml.obsvalue
END

I will want to export the data into a Temp table, so SQL to do that would be welcome. Then I could "test" against the Temp table before making any real changes.

Suggestions ?

- Beverly

Posted : December 9, 2014 12:15 pm

acantu

(@acantu)

Posts: 722

Prominent Member

Email me at [email protected], i have the scripts you need. it doesn't let me post them on here...

**Now that this is fixed, here is the script**

This is to update your OBS Values:::

UPDATE OBS
SET OBSVALUE = 'Never smoker'
WHERE HDID IN (SELECT HDID FROM OBSHEAD WHERE NAME = 'SMOK STATUS') AND
OBSDATE BETWEEN TO_DATE('10/01/2014','MM/DD/YYYY') AND TO_DATE('12/31/2014','MM/DD/YYYY') AND
OBSVALUE IN ('Never smoked','Does not smoke','Non Smoker','Patient is a non-smoker')

This is to select all records, you can copy and paste them over into Excel if wanted:::

SELECT *
FROM OBS
WHERE HDID IN (SELECT HDID FROM OBSHEAD WHERE NAME = 'SMOK STATUS') AND
OBSDATE BETWEEN TO_DATE('10/01/2014','MM/DD/YYYY') AND TO_DATE('12/31/2014','MM/DD/YYYY') AND
OBSVALUE IN ('Never smoked','Does not smoke','Non Smoker','Patient is a non-smoker')

Posted : December 9, 2014 12:33 pm

jamesedward

(@jamesedward)

Posts: 257

Reputable Member

update ml.obs set obsvalue = 'xxxx' where hdid = 300015 and change = 2 and obsvalue like '%yyyyy%';

Back in 2011, instead of updating the table directly, I exported the data, fixed it and then re-imported it back into EMR as a Lab Result with a simple mail-merge. That takes much more time than the straight SQL update, but was more accurate clinically within the Chart documentation:

MSH|^~\&||MU-SMOKING|COR||201111040000||ORU|6080593|P|2.3

PID|1|510155|510155|270767|LASTNAME^FIRSTNAME^|||F|||||||||||

PV1|0001|O|^^^ENTERLOCATIONOFCARE

OBR||00002^001|1111111|Update^Update Smoking Values from EMR|||200912150000|||PSCB|||||^^^|ckhnmeaningfuluse^MEANINGFUL USE^CKHN EMR||CM|||||||F||1^^^^^R^^ROUTINE|||||

OBX|1|TX|SMOK STATUS||OBX-5||||||F|||200312230000

NTE|||

NTE|||This patient has been identified as part of the CKHN Meaningful Use

NTE|||update of patient Smoking Status Vocabulary.

NTE|||

NTE|||EMR Smoking Values Assigned to ONC Vocablulary

NTE|||Original Value Practice Name <ENTER-LOCNAME

NTE|||Original Value Doctor Name <ENTER-DOCTORNAME>

NTE|||Original Value OBSDATE 12/23/2003 <ENTER-ORIGINAL OBSDATE>

NTE|||

OBX|2|TX|SMOK STATUS BY||CKHN EMR||||||F|||200312230000 <ENTER-ORIGINAL OBSDATETIME AS MIDNIGHT>

NTE|||

NTE|||Only Patients with SMOK STATUS values within EMR, from 11/1/2009 onward

NTE|||have had the SMOK STATUS flowsheet term updated.

Posted : December 10, 2014 6:24 am

blackmanb

(@blackmanb)

Posts: 268

Reputable Member

Topic starter

James,

I get the concept - this would update the flowsheet values ? But I would not know how to generate a mail-merge HL7 file like shown. How would this work ? Export the records I want to modify to an excel spreadsheet and fix the data ? Then somehow use mailmerge to generate a huge HL7 update file to import via linklogic ? Would you mind e-mailing me directly to discuss ? I would very much like to know how to do this cleanly. - Beverly

Posted : December 10, 2014 6:38 am

JonesJD

(@jonesjd)

Posts: 48

Trusted Member

Acantu,

You should now be able to post SQL queries. Also, the current media uploader supports pre-formatted text which is great for code. Click the “paragraph” drop down, and choose "Pre" for preformatted. If you don’t see this dropdown, click the Toolbox icon.

Posted : December 22, 2014 7:05 am

jfitzmd

(@jfitzmd)

Posts: 419

Reputable Member

Strongly suggest that you not update the database directly, GE will not have any sympathy with misadventures.

The LinkLogic outlined above will save you from disasters.

The Company Lost Creek has an obs term editor that would solve your issue.

You can always update the obs term on an individual basis as an encounter is opened.

Posted : December 22, 2014 7:45 am

blackmanb

(@blackmanb)

Posts: 268

Reputable Member

Topic starter

<<You can always update the obs term on an individual basis as an encounter is opened.>>

This I have already done, by modifying the form they use to update the "SMOK STATUS" obsterm. It only uses radio buttons now for the "allowed" values, and I added code to check the obsprev and fit it to one of the acceptable values (where possible). Otherwise I display the previous value (if any) and they have to select one of the currently acceptable values. Moving forward, this should update the data to correct values.

I still have the problem with the quarterly report for reporting "smoking status". As a stop-gap, I modified the crystal report used for this, to expand what it accepts as valid values for SMOK STATUS. It now accepts a broad selection of the hand-entered data.

I was not comfortable with changing the obsterm data directly, as I did not know if that was a legitimate solution. I would prefer the linklogic solution, but still not sure how James did that.

- Beverly

Posted : December 22, 2014 8:27 am

acantu

(@acantu)

Posts: 722

Prominent Member

No problem Beverly, just responded to your request for an SQL script to make the changes. If you do not feel comfortable, that is not an issue. If you have any further questions, please let me know.

Posted : December 22, 2014 8:43 am

blackmanb

(@blackmanb)

Posts: 268

Reputable Member

Topic starter

Arnold, Not a problem and your scripts have already helped me in querying the database to see just what the data consists of, that needs to be changed. Very helpful and much appreciated ! 🙂 - Beverly

Posted : December 22, 2014 8:58 am

24 Forums
9,940 Topics
39.9 K Posts
0 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed