>>But I haven't of JOIN. I think that I can do something like:
>>INNER JOIN thetable b ON b.date = (select min(date) ... where c.date > a.date )
>>
>>Can this be good?
>
>this might be you're only option but I think you want a MAX() and not MIN(). You want the maximum date that is less than the date in the outer query.

Related to this, I have the query that runs correctly and return the good results under SQL 2000. The query takes 3 seconds to complete.

This is my query:

SELECT HP1.BarraID, TT.Ticker, TT.Name AS Compagnie, 
HP1.Date AS 'Dernière date', HP1.Price AS 'Dernier prix', 
HP2.Date AS 'Date courante', HP2.Price AS 'Prix courant', 
((HP2.Price / HP1.Price) - 1) *100 AS Variation 

FROM HistoPriceCNE3 AS HP1 

INNER JOIN HistoPriceCNE3 AS HP2 
ON HP1.BarraID = HP2.BarraID 
AND HP2.Date = (     SELECT MIN(Date)     FROM HistoPriceCNE3 AS HP3     Where HP3.BarraID = HP1.BarraID     AND HP3.Date > HP1.Date ) 
AND ABS((HP2.Price / HP1.Price) - 1) > .5 

INNER JOIN Titre AS TT 
ON HP1.BarraID = TT.BarraID 
AND TT.Modele = 'CNE3' 
AND TT.DateLastUpdate = (     SELECT MAX(DateLastUpdate)     FROM Titre AS AA     Where AA.Modele = 'CNE3'     AND AA.BarraID = HP1.BarraID) 

WHERE HP1.Date >= CONVERT(DATETIME,'2001/07/02',101) 
AND HP2.ForceVariation IS NULL 

ORDER BY HP1.BarraID, HP1.Date

If I run the same query on a SQL 7 server, it takes 200 seconds to return the same result!!!

I found that if I comment the line AND ABS((HP2.Price / HP1.Price) - 1) > .5 , the query takes 13 seconds.

Any ideas on how to run faster on SQL 7?