Garrett Fitzgerald wrote:
> How would you format that query? I'm always interested in making my
> code more readable, since it might be me that has to read it later.
Here's how *I* would do it:
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
I would also explicitly name the field aliases.
Paul
_______________________________________________
Post Messages to: [email protected]
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.
