Results 1 to 3 of 3
  1. #1
    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. #2
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by Brett Kaiser View Post
    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
  •