Hi this is not quite what I was after. Basically I am after something like this:
Say here is my input data
Name Price Date/Time
ProductA 10.45 1/june/2010 01:00
ProductB 10.90 1/June/2010 01:00
ProductA 10.45 1/june/2010 02:00
ProductB 10.90 1/June/2010 02:00
ProductA 10.45 1/june/2010 03:00
ProductB 10.90 1/June/2010 03:00
ProductA 10.65 1/june/2010 04:00
ProductB 10.90 1/June/2010 04:00
Now Product B has not changed price at all, but product A changed price at 04:00
What I was wanting is to get the Name, Min Price and Min DateTime, NextPrice, Next Price DateTime
So the record i need returned is
ProductA,10.45,1/June/2010 01:00,10.65,1/june/2010 04:00
ProductA,10.65,1/june/2010 04:00,null,null
ProductB,10.90,1/june/2010 01:00,null,null
So logically it is return the product name and the start price, and the start time, and also the next price and the next price time. This is not the maximum price, or the maximum time, just the next price and time.
My table collects prices every 15 minutes, but the prices only change every few days (but can be every few hours)
So I am only after the actual prices and not the data in the middle where nothing changes.
I hope this makes sense.
cheers
Chris