Notifications
Clear all
Topic starter
I have to create forms for clients that sometimes require my using "odd" OBSTERMS (rather than having custom OBSTERMS created). I can find the list of OBSTERMS easily enough, but can someone tell me how to identify which ones may have already been used (for ANY reason) and which ones have no values (meaning they haven't been used for something else, whether their intended purpose or otherwise)?
Posted : August 22, 2018 11:02 am
I have a Crystal Report listing all obs terms used and their frequencies. Contact me directly if you would like a copy.
Posted : August 22, 2018 2:48 pm
Chris,
the easiest way to get at this is to run a query against your database. You could certainly create a custom report once you are comfortable with the logic. I often use Microsoft Access to connect to our SQL database and run "read only" queries for this purpose. Below is a screenshot of the logic that will give you the data you are after and an example of the output. This query will identify all the OBS terms that are used in your database and the total number of times each term has been used. If you want to sort this in different ways (e.g. most frequently used OBS term to least frequent), I would recommend exporting the data to Excel or having MS Access create a table from the resultant data and then running queries against that local table. You'll find this query runs pretty fast; about 10 seconds in our environment, and we have a lot of data.
Cheers,
Greg
Here is the actual SQL statement:
SELECT HIERGRPS.GROUPNAME, OBSHEAD.NAME, OBSHEAD.DESCRIPTION, Count(OBS.OBSVALUE) AS CountOfOBSVALUE
FROM (HIERGRPS INNER JOIN OBSHEAD ON HIERGRPS.GROUPID = OBSHEAD.GROUPID) INNER JOIN OBS ON OBSHEAD.HDID = OBS.HDID
GROUP BY HIERGRPS.GROUPNAME, OBSHEAD.NAME, OBSHEAD.DESCRIPTION
ORDER BY HIERGRPS.GROUPNAME, OBSHEAD.NAME
Posted : August 23, 2018 12:22 am
Topic starter
Thank you both for such quick responses. I'm not great at SQL so this probably isn't the best way to go about this, but after changing underscores to periods in virtualHITmans script and running that, I obtained a list of OBS Terms used (and how many times they were used). I copied all of that (with Headers) into Excel, colored the results and set a filter. VirtualHITmans script did not show any NULL values though so I then ran SELECT * from OBSHEAD and copied only the name and description fields into the same spreadsheet (with no color) and sorted by Name. That allowed me to see which OBS Terms were not used (those with no count or color - excluding of course those that had the same name/description as appeared in virtualHITmans script).
Hope that all makes sense to those who come after looking for an answer. BTW, I haven't yet received the Crystal Report from jfitzmd so I can't say if that would've met my needs in a friendlier manner.
Posted : August 23, 2018 3:31 am
Yeah. For what it's worth, you did the right modifications to your SQL script. MS Access uses "dbo_" as a prefix to identify database tables so you need to remove that prefix if you are setting up a query in SQL studio or a similar tool. Didn't realize you could just change the "_" to a "." to get the same thing, so thanks for that. I'll correct the SQL script in my previous post in case someone else can benefit from this post.
For future reference, here is the SQL script to identify all of the OBS terms in your database that have not ever been used.
SELECT OBSHEAD.NAME, OBSHEAD.DESCRIPTION, OBS.OBSVALUE
FROM OBSHEAD LEFT JOIN OBS ON OBSHEAD.HDID = OBS.HDID
WHERE (((OBS.OBSVALUE) Is Null))
ORDER BY OBSHEAD.NAME;
Posted : August 23, 2018 4:10 am
Topic starter
Awesome! Thank you again.
Posted : August 23, 2018 4:53 am
Topic starter
Thank you!
Posted : August 23, 2018 4:54 am
This may not be what you are after - but I usually do a chart report from Centricity and select the obs term I am hoping is not in use and then run a query looking for patients where date of last obs term is not blank. I work with a programmer from another practice who does it the same way (we independently ended up using the same system).
Posted : September 6, 2018 5:15 am
