Notifications
Clear all
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
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
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|||
NTE|||This patient has been identified as part of the CKHN Meaningful Use
NTE|||update of patient Smoking Status Vocabulary.
NTE|||
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|||
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
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
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
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
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
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
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
