Unanswered: need algorithm help for determining a range of values
suppose you have a large table with 2 columns
create table tick
ID bigint identity (1,1) primary key not null
, price money not null
and I want to know 3 things
Starting with ID = 1 through ID = (last)
give me the low and high price (that satisfies the below WHERE clause), and the last ID
WHERE high price - low price = 0.10
and the last ID (last) is the minimum ID to satisfy: high price - low price = 0.10
So the last ID will coincide with the record containing either the low or high price, the problem is you don't know which record in that range has the corresponding high/low price, it could be the first record or the 10,000th record.
I am thinking I need to create two summary tables, maybe calculate the min(ID) that goes down 0.01 then the min(ID) that goes down 0.02, etc...
Then calculate the min(ID) that goes up 0.01 then up 0.02, etc...
finally join against these two summary tables to figure out which combination of downSummary and upSummary have a difference of 0.10. Once that is determined, shift the starting range, rinse repeat, hmmm maybe I just figured it out.
Last edited by Gagnon; 03-29-12 at 14:49.
Reason: clarified high/low
* Each range bar must have a high/low range that equals the specified range.
* Each range bar must open outside the high/low range of the previous bar.
* Each range bar must close at either its high or its low.