Re: Trouble with max(date) query

I think this should do it, but don't bet any $ on it till you try it...

and staffno not in
(
select staffno from tblPay where pay_date >= to_date('01-AUG-2002','DD-MON-YYYY')
)
;

"melhayes" <webbcom_at_yahoo.com> wrote in message news:16a64606.0309110751.10cad7e1_at_posting.google.com...
> 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 - 12:17:40 CDT