Another one to try

WITH CTE1 AS (

SELECT Name ,Price ,Date,

ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DATE) AS rn1,

ROW_NUMBER() OVER(PARTITION BY Name,Price ORDER BY DATE) AS rn2

FROM #Temp),

CTE2 AS (

SELECT Name,Price AS [Min Price],MIN(Date) AS [Min DateTime],MAX(rn1) AS maxRN

FROM CTE1

GROUP BY Name,Price,rn2-rn1)

SELECT a.Name,a.[Min Price],a.[Min DateTime],

b.Price AS [NextPrice],

b.Date AS [Next Price DateTime]

FROM CTE2 a

LEFT OUTER JOIN CTE1 b ON b.Name=a.Name AND b.rn1=a.maxRN+1 AND b.Date>a.[Min DateTime]

ORDER BY a.Name,a.[Min DateTime];

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537