I just wrote a query against our EMR database to give me the first and
last of a series of observations. I came up with the following, which
appears to work, but I was wondering if anyone had any thoughts on how
to do it more efficiently. I don't like those correlated subqueries
just to get a max and a min, but I don't know that there's a better
way.
Thanks!
SELECT person.lastname, person.firstname, obshead.name, obs.obsdate,
obs.obsvalue, obs2.obsdate, obs2.obsvalue
FROM ml.Person Person
JOIN ml.rptObs Obs ON Person.pID = Obs.pID
JOIN ml.ObsHead ObsHead ON Obs.hdID = ObsHead.hdID
JOIN ml.rptObs Obs2 ON Obs.pID = Obs2.pID
AND Obs.hdID = Obs2.hdID
AND Obs.obsDate < obs2.obsDate
WHERE ObsHead.name LIKE 'SF-12%'
AND Obs.obsDate = (SELECT MIN(obsDate)
FROM ml.RptObs Obs3
WHERE Obs3.pID = obs.pID
AND Obs3.hdID = obs.hdID)
AND Obs2.obsDate = (SELECT MAX(obsDate)
FROM ml.RptObs Obs4
WHERE Obs4.pID = obs2.pID
AND Obs4.hdID = obs2.hdID)
ORDER BY Person.lastname, Person.firstname, Obs.obsdate, obshead.name
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
