>>Hi All:
>>
>>I'm having trouble getting the desired results for an SQL statement.
>>
>>I have 2 tables. FEEDINV has a bunch of records of invoices, each with a delivery date. For each record in FEEDINV, I want a matching record in RATPRIC, where the date in RATPRIC is equal to or less than, BUT I only want the CLOSEST record in RATPRIC AND only if is within 7 days. (Kind of like SET NEAR ON, SEEK). ALSO, I only want a match if FEEDINV.fi_cpprim = RATPRIC.rp_cpprim.
>
>Yossi - take a look at the code I wrote for you for the INGRPT. It does the same sort of thing you're looking for.
>
>Tamar

Tamar:

thanks! I'm almost there EXCEPT that when the RATPRIC date is more than 7 days less than the FEEDINV date, I want to get NULLs in the RATPRIC fields. The attached code excludes them altogether.

SET DELETED on

SELECT fi_invnum, fi_flcode, fi_cpprim, fi_weight, fi_dlvdat, rp_rtprim, MAX(rp_date), rp_cpprim ;
FROM feedinv ;
left OUTER JOIN ratpric ;
ON fi_rtprim = rp_rtprim ;
AND fi_cpprim = rp_cpprim ;
AND fi_dlvdat > rp_date ;
WHERE fi_flcode = 'BAA12062' ;
AND fi_dlvdat - rp_date < 7 ;
GROUP BY fi_invnum, fi_flcode, fi_cpprim, fi_weight, fi_dlvdat, rp_rtprim, rp_cpprim