>I have the following tables
>
>claims.dbf
>claim_id I
>....
>
>
>claims2.dbf
>claim2_id I
>claims_ID I
>fromdate D
>status C(10)
>....
>
>
>for each claims.claim_id I need the max(claims2.fromdate) and the associated status for this date for a given data of ldDate
>
>meaning: what was the status of claims 123 ... 456 on 06/30/2003 for example
I think that might be something similar to this:
select claim2_id, max(fromdate) as LastDate;
from claims2;
group by claim2_id;
into cursor Temp;
nofilter
select Temp.*, Claims2.Status;
from Temp join Claims2;
on Temp.claim2_id = Claims2.claim2_id and Temp.LastDate = claims2.FromDate;
into cursor Temp
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)