Trouble with max(date) query

Hello,

I have a payroll table in Oracle 817 and I've having problems with what looks to me should be a simple query.

The table has payroll records from the present back to 1999 with let's say 2 fields, staffno varchar2(7) and pay_date date. I'd like a list of all staff who's most recent pay date is less than 01-AUG-2002. This looks dead easy but I can't get it to work...

this gives me the proper max pay_date

select max(pay_date)

     from tblPay
     where pay_date <  to_date('01-AUG-2002','DD-MON-YYYY');

21-JUL-2002 When I try to wrap it...

select a.staffno, a.pay_date from tblPay a where a.period_end_date <= (select max(b.pay_date) from   tblPay b where b.pay_date < to_date('01-AUG-2002','DD-MON-YYYY')) order by a.staffno;

This gives me only pre-August timecards, but many of the results also have pay_dates after Aug 1.

What am I missing?

Thanks for any input.

Dan Webb
dew_at_post.queensu.ca Received on Thu Sep 11 2003 - 10:51:35 CDT