Re: Trouble with max(date) query

webbcom_at_yahoo.com (melhayes) wrote:

>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

You code should only return records whose period_end_date is less then 8/1/2002 ( because the max date can be no later than 7/31/2002) so if it is do other than this, add the period_end_dt to your query and see if some bad data is involved. Received on Thu Sep 11 2003 - 11:17:56 CDT