# Thread: need algorithm help for determining a range of values

1. Registered User
Join Date
Jun 2005
Posts
319

## 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 15:49. Reason: clarified high/low

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
SELECT MIN(Price) AS low_price, MAX(price) AS high_price
FROM yourTable

The last part about high-low = .10 and you wan the ID makes no sense

3. Registered User
Join Date
Jun 2005
Posts
319
Originally Posted by Brett Kaiser
SELECT MIN(Price) AS low_price, MAX(price) AS high_price
FROM yourTable

The last part about high-low = .10 and you wan the ID makes no sense
This should explain it better to you:
Range Bar Charts: A Different View Of The Markets

Three rules of range bars:

* 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•