Hi Lam,
Try and see whether following SQL work for you:
create temporary table tmp as
select product_id, min(abs(to_days('2004-6-30') - to_days(snapshot_date))
+ if(snapshot_date > '2004-6-30',0,0.1)) as date_diff
from product_tbl group by product_id;
select product_tbl.*
from product_tbl, tmp
where product_tbl.project_id = tmp.project_id and
abs(to_days('2004-6-30') - to_days(snapshot_date)) + if(snapshot_date >
'2004-6-30',0,0.1) = tmp.date_diff;
I have tested the about SQL on 4.0.20 and it is okay. If you are using
Version 4.1, you may try to use subquery.
Regards,
Michael
-----------------------------------------------------------------------------------------------------------
KMB E-mail Disclaimer
This e-mail may contain confidential, proprietary or legally privileged
information and is intended for the attention and use of the
addressee(s) only. If you are not the intended recipient of this
message, you must not copy, use or disclose any part of its
contents. Please notify the sender immediately and delete this
message from your system.
The KMB Group and each of its affiliates and the sender of this
message shall not be responsible or liable for any errors or omissions
in the contents of this message as secure or error free e-mail
transmission cannot be guaranteed. Information sent via e-mail
could arrive late or contain viruses or be intercepted, corrupted,
lost, destroyed, or incomplete. Unless otherwise stated, any
information given in this message is indicative only and is subject to
our formal written confirmation.
