Notifications
Clear all
Trading Post
4
Posts
3
Users
0
Reactions
101
Views
Topic starter
I am looking for a report that will list all of our non blank obs terms. Anyone have such a thing that they are willing to share?
thanks
Posted : May 2, 2012 2:48 am
Here's one I posted to the old Trading Post that not only lists the obs terms, but counts them as well. Takes a heck of a long time to run, though…
WITH obsCounts AS
(SELECT hdid,
COUNT(DISTINCT pid) AS pidCount
FROM ml.obs
WHERE change IN (1, 2)
GROUP BY hdid
)
SELECT oh.name,
oh.description,
hg.groups,
nvl(oc.pidCount, 0) as obsCount
FROM ml.obshead oh
JOIN
(SELECT groupid,
sys_connect_by_path( groupname, ':' ) groups
FROM ml.hiergrps
START WITH parentid = 0
CONNECT BY prior groupid = parentid
) hg
ON oh.groupid = hg.groupid
LEFT JOIN obsCounts oc
ON oh.hdid = oc.hdid
Posted : May 9, 2012 2:26 am
Topic starter
Thanks so much I will pose it as an option.
Posted : May 18, 2012 2:21 am
Here's a SQL/Oracle query that will give you a list of obs terms that have been used in your system if that is what you are looking for:
select distinct ll.hdid, ob.name, ob.description, ob.unit from ml.lastlabs ll, ml.obshead ob where ll.hdid = ob.hdid
Returns:
53 BP DIASTOLIC blood pressure, diastolic - 8462-4 mm Hg 116 ABO BLD GRP ABO blood group NULL 2075 TD #1LOT dT (Diphtheria and Tetanus) immunization for children #1 lot number NULL 7198 BIRTH PLAN birth plan NULL 2056 MMR #1LOT MMR (measles, mumps, rubella) virus immunization #1 lot number NULL 2504 LEAD SCREENI lead screening NULL
etc....
Posted : May 18, 2012 5:01 am
