On Tue, Jul 29, 2008 at 1:02 PM, Garrett Fitzgerald
<[EMAIL PROTECTED]> wrote:
> I'm trying to write a query here that asks what women between 18 and
> 64 seen in a date range have had a pap smear within 3 years before
> their visit. I tried the following, but Fox told me to go fornicate
> myself. Anybody have any thoughts on the way it should actually look?
> Thanks.
How about:
SELECT pid, MAX(obsDate) ;
FROM allObs o1 allObs o2 ;
WHERE o1.sex = "F" ;
AND o1.pid = o2.pid and o1.obsDate > o2.obsDate
AND o1.dateOfBirt BETWEEN GOMONTH(o1.obsDate, -65 * 12) + 1 ;
AND GOMONTH(o1.obsDate, -18 * 12) ;
AND o1.obsDate BETWEEN ldStart AND ldEnd
AND o2.obsName = "PAP SMEAR"
AND o2.obsDate < GOMONTH(o1.obsDate, -3 * 12)
This gets you a cartesian set of allObs, self-joined on the pid, and
filtered so the newer of the two dates matches is in o1 (if there were
more than one visit between ldStart and ldEnd, you'll get multiple
records). The older record was a pap. The age test is only applied to
that later visit.
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com
_______________________________________________
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.
