Is it ok for your?
SELECT EmpID, EffectiveDate as ed,value FROM t1 ;
WHERE id+fecha in (select id+MAX(EffectiveDate) from t1 WHERE fecha <=?dParam GROUP BY id)
I hope this Help.
>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?
>Bill Morris
>Morco - Because You Deserve More
>
bill69@earthlink.net