Hi Sergey,
This is still 2 steps, just combined onto 1 line of code.
I was really hoping for something that would only "use t1 shared" once during the process.
This is probably the best that can be done, but I'm still hoping.


>This query should work

SELECT * FROM t1 ;
>	WHERE STR(EmpId) + Dtos(EffectiveDate) IN ( ;
>		Select Str(EmpID) + Dtos(max(EffectiveDate)) ;
>  from t1 ;
>  where EffectiveDate <= ?dParam ;
>  group by EmpID )
>

>
>>I have this table (T1.dbf)
>>

>>EmpID     EffectiveDate      Value
>>1         {5/6/2}            17
>>1         {3/4/2}            18
>>1         {1/1/1}             5
>>2         {4/5/2}            18
>>2         {1/1/1}             6
>>3         {4/12/2}           16
>>3         {1/1/1}             7
>>

>>and want to return this for a parameter of {4/8/2}
>>

>>EmpID     EffectiveDate      Value
>>1         {3/4/2}            18
>>2         {4/5/2}            18
>>3         {1/1/1}             7
>>

>>Currently I have to do it in 2 steps.
>>

>>1 Select EmpID, max(EffectiveDate) as ED
>>  from t1 ;
>>  where EffectiveDate <= ?dParam ;
>>  group by EmpID ;
>>  into cursor Temp
>>2 Select Temp.*, T1.Value ;
>>  from Temp join T1 on Temp.EmpId + Temp.ED = T1.EmpID + T1.EffectiveDate
>>

>>Can anyone get it down to 1 step?