Thanks David,
1. Since Guest is the PK for Guests.dbf, I assume I don't need "x1.emp" in x2?

2. In the 3rd SQL, is there a reason you did "x1.guest = x2.guest and x1.delta = x2.delta" instead of "str(x1.guest) + str(x1.delta) = str(x2.guest) + str(x2.delta)"?
IOW is "Condition1 AND Condition2" faster than "field1 + field2 = field1 + field2" in the Join?

3. Do you think it's worth exploring whether it's worth while to index x1 and x2 so that Rushmore might speed up the 3rd SQL?
X1 will probably average around 20,000 records each time.
X2 and finalanswer will probably average around 3,000 records.


>Bill,
>
>Here's one way to do the whole thing with 3 SQLs, no scan loop necessary
>
>

* find all valid commissions per guest
>
>select guests.*, commissions.effdate, commissions.commission, ( guests.date - commissions.effdate ) as delta ;
>   from commissions ;
>   inner join guests ;
>      on commissions.emp = guests.emp ;
>   into cursor x1 ;
>   having delta > 0
>
>* find the earliest valid
>
>select x1.guest, x1.emp, min(delta) as delta ;
>   from x1 ;
>   into cursor x2 ;
>   group by guest, emp
>
>* pull the earliest rows out of x1 to get the final result
>
>select x1.guest, x1.date, x1.emp, x1.effdate, x1.commission ;
>   from x1 ;
>   inner join x2 ;
>      on x1.guest = x2.guest and x1.emp = x2.emp and x1.delta = x2.delta ;
>   into cursor finalanswer ;
>   order by 1,2
>

>
>>I want to match each guest with the Commission due the Emp for that date.
>>

>>desired cursor:
>>Guest    Date    Emp    Effective    Commission
>>-----------------------------------------------
>>1        4/4/2   1      1/1/1        100
>>2        4/4/2   2      1/1/1        100
>>3        5/4/2   1      5/1/2        125
>>4        5/4/2   2      1/1/1        100
>>5        6/4/2   1      6/1/2        150
>>6        6/4/2   2      6/1/2        110
>>