On Tue, Jul 29, 2008 at 13:28, Ted Roche <[EMAIL PROTECTED]> wrote:
> 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.
I always said I couldn't see what you could do in an EXISTS test that
you couldn't do with a JOIN. :-) Thanks, Ted: here's the query as I
finally ran it.
SELECT o1.pid ;
FROM allObs o1 ;
JOIN allObs o2 ON o1.pID = o2.pID ;
AND o1.obsDate > o2.obsDate ;
INTO CURSOR crsrWomenPap3Years ;
WHERE o1.sex = "F" ;
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)
_______________________________________________
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.
