Professional Web Applications Themes

Query, Join on nearest - Microsoft SQL / MS SQL Server

Hello, I am in a position where i need to create a query where i must join two tables on the nearest value. Imagine i have 2 tables, tbl_Trade and tbl_Time. For each Time in tbl_Time, I need to locate the row in tbl_Trade where tbl_Time.LastUpdate is closest to, but less than the value of tbl_Time.myTime. I have been using the followng query Select Avg(s.Price) AS Price , t.myTime From tbl_Trade s Inner Join tbl_Times t On (t.myDate + '2003-07-08') > s.LastUpdate AND (t.myDate + '2003-07-08' - '1:00 AM') < s.LastUpdate Inner Join tbl_StockList l On l.ID = s.StockID Where ...

  1. #1

    Default Query, Join on nearest

    Hello, I am in a position where i need to create a query where i must join
    two tables on the nearest value.

    Imagine i have 2 tables, tbl_Trade and tbl_Time. For each Time in tbl_Time,
    I need to locate the row in tbl_Trade where tbl_Time.LastUpdate is closest
    to, but less than the value of tbl_Time.myTime.

    I have been using the followng query

    Select Avg(s.Price) AS Price , t.myTime
    From tbl_Trade s
    Inner Join tbl_Times t
    On (t.myDate + '2003-07-08') > s.LastUpdate
    AND (t.myDate + '2003-07-08' - '1:00 AM') < s.LastUpdate
    Inner Join tbl_StockList l
    On l.ID = s.StockID
    Where l.Code = 'ORI'
    Group By t.myTime

    But this relies on using an average of all trades in the time period (which
    in this case is 1 hour).

    Essentially the problem is that each trade can occur at any time, so there
    is not necessarily a trade that occurs in that period, nor is an average of
    the value of the average of the prices necessarily indicative of the value
    we wish to retrieve.

    So basically what i want to be able to do is to generate a recordset of the
    value of the price of the last trade at each hour O, O is specified in a
    table. Is such a join possible? Or is there a work around aside from using
    triggers to populate a table of prices for each hour for each commodity?

    Cheers


    Tommy Guest

  2. #2

    Default Re: Query, Join on nearest

    Please post DDL (CREATE TABLE statements) and sample data (INSERT
    statements).

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  3. #3

    Default Query, Join on nearest

    I would do it using a full outer join and then specify
    the "closeness" in the where statement using datediff.

    example where datediff(hh,datecolumn1,datecolumn2) <= 1

    HTH

    Ray Higdon MCSE, MCDBA, CCNA

    >-----Original Message-----
    >Hello, I am in a position where i need to create a query
    where i must join
    >two tables on the nearest value.
    >
    >Imagine i have 2 tables, tbl_Trade and tbl_Time. For
    each Time in tbl_Time,
    >I need to locate the row in tbl_Trade where
    tbl_Time.LastUpdate is closest
    >to, but less than the value of tbl_Time.myTime.
    >
    >I have been using the followng query
    >
    >Select Avg(s.Price) AS Price , t.myTime
    > From tbl_Trade s
    > Inner Join tbl_Times t
    > On (t.myDate + '2003-07-08') > s.LastUpdate
    > AND (t.myDate + '2003-07-08' - '1:00 AM') <
    s.LastUpdate
    > Inner Join tbl_StockList l
    > On l.ID = s.StockID
    > Where l.Code = 'ORI'
    > Group By t.myTime
    >
    >But this relies on using an average of all trades in the
    time period (which
    >in this case is 1 hour).
    >
    >Essentially the problem is that each trade can occur at
    any time, so there
    >is not necessarily a trade that occurs in that period,
    nor is an average of
    >the value of the average of the prices necessarily
    indicative of the value
    >we wish to retrieve.
    >
    >So basically what i want to be able to do is to generate
    a recordset of the
    >value of the price of the last trade at each hour O, O is
    specified in a
    >table. Is such a join possible? Or is there a work
    around aside from using
    >triggers to populate a table of prices for each hour for
    each commodity?
    >
    >Cheers
    >
    >
    >.
    >
    Ray Higdon Guest

  4. #4

    Default Re: Query, Join on nearest

    "Tommy" <blahblah.com> wrote in message
    news:3f0c03a9$0$59955$c30e37c6lon-reader.news.telstra.net...
    > Hello, I am in a position where i need to create a query where i must join
    > two tables on the nearest value.
    >
    > Imagine i have 2 tables, tbl_Trade and tbl_Time. For each Time in tbl_Time,
    > I need to locate the row in tbl_Trade where tbl_Time.LastUpdate is closest
    > to, but less than the value of tbl_Time.myTime.
    >
    > I have been using the followng query
    >
    > Select Avg(s.Price) AS Price , t.myTime
    > From tbl_Trade s
    > Inner Join tbl_Times t
    > On (t.myDate + '2003-07-08') > s.LastUpdate
    > AND (t.myDate + '2003-07-08' - '1:00 AM') < s.LastUpdate
    > Inner Join tbl_StockList l
    > On l.ID = s.StockID
    > Where l.Code = 'ORI'
    > Group By t.myTime
    >
    > But this relies on using an average of all trades in the time period (which
    > in this case is 1 hour).
    >
    > Essentially the problem is that each trade can occur at any time, so there
    > is not necessarily a trade that occurs in that period, nor is an average of
    > the value of the average of the prices necessarily indicative of the value
    > we wish to retrieve.
    >
    > So basically what i want to be able to do is to generate a recordset of the
    > value of the price of the last trade at each hour O, O is specified in a
    > table. Is such a join possible? Or is there a work around aside from using
    > triggers to populate a table of prices for each hour for each commodity?
    >
    > Cheers
    Here's a stab at what I believe you're asking, however, without DDL,
    sample data, and an example of what the query should return, this is
    just a guess.

    -- For each stock, give the most recent trade before a given
    -- reference time
    CREATE VIEW MostRecentTradeAtTime (stock_id, ref_time, actual_time)
    AS
    SELECT ST.StockID, T.myTime, ST.LastUpdate
    FROM tbl_Time AS T
    INNER JOIN
    tbl_Trade AS ST
    ON ST.LastUpdate < T.myTime AND
    NOT EXISTS (SELECT *
    FROM tbl_Trade
    WHERE StockID = ST.StockID AND
    LastUpdate > ST.LastUpdate AND
    LastUpdate < T.myTime)

    Regards,
    jag


    John Gilson Guest

  5. #5

    Default Re: Query, Join on nearest - My Results

    I have spent the better part of the afternoon ing around with this
    problem and have come up with 2 different solutions. One uses cursors and
    is incredibly fast, the other doesnt and is very slow.

    I created them as stored procedures so that i could stress test each more
    easily.

    The first solution, and nicest looking one is....
    ---------------------------------------------------------------------------
    Create procedure Tester2 (Stockid as int)
    As
    Select tbl_Stocks.Price, tbl_Stocks.LastUpdate
    From tbl_Stocks
    Inner Join (
    Select Max(s.ID) AS Closest --, l.Code, s.LastUpdate
    From tbl_Stocks s
    Inner Join tbl_Times t
    On s.LastUpdate < (t.myDate + '2003-07-08')
    Where s.StockID=StockID
    Group By t.myDate
    ) dt_Updates
    On dt_Updates.Closest = tbl_Stocks.ID

    The second, uglier one is...
    ---------------------------------------------------------------------------
    Create procedure Tester1 (Stockid as int)
    As

    declare myTime as smalldatetime
    declare Result Table ( Price int, LastUpdate smalldatetime )


    Declare Time_Cursor cursor
    For
    Select myDate + '2003-07-08' From tbl_Times

    Open Time_Cursor

    Fetch Next From Time_Cursor Into myTime
    While Fetch_Status = 0
    Begin
    Insert Into Result
    Select Top 1 Price, LastUpdate
    From tbl_Stocks
    Where StockID=StockID
    And LastUpdate < myTime
    Order By LastUpdate Desc

    Fetch Next From Time_Cursor Into myTime
    End
    Close Time_Cursor
    Deallocate Time_Cursor
    Select * From Result
    ---------------------------------------------------------------------------

    And the results?

    I used a silly little bit of SQL to run each 100 times.
    Tester1 1 Second
    Tester2 148 Seconds

    Running Tester1 1000 Times however took 14 seconds, however i think most of
    this was spent in network congestion as the CPU's on the server didnt go
    over 30%. Tester2 however was using over 80%. So it seems that Tester1
    around 100 times faster (which is a pretty nice speed increase really!)...
    Also of note, my earlier incarnations of Tester2 (ie the single query
    approach) were taking over 20 seconds per SINGLE query.

    In this test case, tbl_Stocks has over 2 million records and tbl_Times has
    9. The server is a Dual P4 2.4 Xeon, 1GB Ram. The data created from the
    queries is going to form the basis for charts.

    I hope this is of interest to some of you.

    Cheers!


    "Tommy" <blahblah.com> wrote in message
    news:3f0c03a9$0$59955$c30e37c6lon-reader.news.telstra.net...
    > Hello, I am in a position where i need to create a query where i must
    join
    > two tables on the nearest value.
    >
    > Imagine i have 2 tables, tbl_Trade and tbl_Time. For each Time in
    tbl_Time,
    > I need to locate the row in tbl_Trade where tbl_Time.LastUpdate is closest
    > to, but less than the value of tbl_Time.myTime.
    >
    > I have been using the followng query
    >
    > Select Avg(s.Price) AS Price , t.myTime
    > From tbl_Trade s
    > Inner Join tbl_Times t
    > On (t.myDate + '2003-07-08') > s.LastUpdate
    > AND (t.myDate + '2003-07-08' - '1:00 AM') < s.LastUpdate
    > Inner Join tbl_StockList l
    > On l.ID = s.StockID
    > Where l.Code = 'ORI'
    > Group By t.myTime
    >
    > But this relies on using an average of all trades in the time period
    (which
    > in this case is 1 hour).
    >
    > Essentially the problem is that each trade can occur at any time, so there
    > is not necessarily a trade that occurs in that period, nor is an average
    of
    > the value of the average of the prices necessarily indicative of the value
    > we wish to retrieve.
    >
    > So basically what i want to be able to do is to generate a recordset of
    the
    > value of the price of the last trade at each hour O, O is specified in a
    > table. Is such a join possible? Or is there a work around aside from
    using
    > triggers to populate a table of prices for each hour for each commodity?
    >
    > Cheers
    >
    >

    Tommy Guest

  6. #6

    Default Re: Query, Join on nearest - My Results

    Tommy,

    I assume that table tblStocks has a clustered unique index on (StockID,
    LastUpdate). If not, then that could help. I also assume that LastUpdate
    is of datatype datetime (not smalldatetime). Make sure that StockID has
    the same datatype as column StockID.

    How does the query below perform?

    Declare d datetime
    Set d='2003-07-08'

    SELECT DateAdd(day,myDate,d) AS ProbeDate,(
    SELECT S2.Price
    FROM tblStocks S2
    WHERE S2.StockID = StockID
    AND S2.LastUpdate = (
    SELECT MAX(S1.LastUpdate)
    FROM tblStocks S1
    WHERE S1.StockID = StockID
    AND S1.LastUpdate < DateAdd(day,myDate,d)
    )
    ) AS Price,(
    SELECT MAX(LastUpdate)
    FROM tblStocks S3
    WHERE S3.StockID = StockID
    AND S3.LastUpdate < DateAdd(day,myDate,d)
    ) AS LastUpdate
    FROM tbl_Times

    Gert-Jan


    Tommy wrote:
    >
    > I have spent the better part of the afternoon ing around with this
    > problem and have come up with 2 different solutions. One uses cursors and
    > is incredibly fast, the other doesnt and is very slow.
    >
    > I created them as stored procedures so that i could stress test each more
    > easily.
    >
    > The first solution, and nicest looking one is....
    > ---------------------------------------------------------------------------
    > Create procedure Tester2 (Stockid as int)
    > As
    > Select tbl_Stocks.Price, tbl_Stocks.LastUpdate
    > From tbl_Stocks
    > Inner Join (
    > Select Max(s.ID) AS Closest --, l.Code, s.LastUpdate
    > From tbl_Stocks s
    > Inner Join tbl_Times t
    > On s.LastUpdate < (t.myDate + '2003-07-08')
    > Where s.StockID=StockID
    > Group By t.myDate
    > ) dt_Updates
    > On dt_Updates.Closest = tbl_Stocks.ID
    >
    > The second, uglier one is...
    > ---------------------------------------------------------------------------
    > Create procedure Tester1 (Stockid as int)
    > As
    >
    > declare myTime as smalldatetime
    > declare Result Table ( Price int, LastUpdate smalldatetime )
    >
    > Declare Time_Cursor cursor
    > For
    > Select myDate + '2003-07-08' From tbl_Times
    >
    > Open Time_Cursor
    >
    > Fetch Next From Time_Cursor Into myTime
    > While Fetch_Status = 0
    > Begin
    > Insert Into Result
    > Select Top 1 Price, LastUpdate
    > From tbl_Stocks
    > Where StockID=StockID
    > And LastUpdate < myTime
    > Order By LastUpdate Desc
    >
    > Fetch Next From Time_Cursor Into myTime
    > End
    > Close Time_Cursor
    > Deallocate Time_Cursor
    > Select * From Result
    > ---------------------------------------------------------------------------
    >
    > And the results?
    >
    > I used a silly little bit of SQL to run each 100 times.
    > Tester1 1 Second
    > Tester2 148 Seconds
    >
    > Running Tester1 1000 Times however took 14 seconds, however i think most of
    > this was spent in network congestion as the CPU's on the server didnt go
    > over 30%. Tester2 however was using over 80%. So it seems that Tester1
    > around 100 times faster (which is a pretty nice speed increase really!)...
    > Also of note, my earlier incarnations of Tester2 (ie the single query
    > approach) were taking over 20 seconds per SINGLE query.
    >
    > In this test case, tbl_Stocks has over 2 million records and tbl_Times has
    > 9. The server is a Dual P4 2.4 Xeon, 1GB Ram. The data created from the
    > queries is going to form the basis for charts.
    >
    > I hope this is of interest to some of you.
    >
    > Cheers!
    >
    > "Tommy" <blahblah.com> wrote in message
    > news:3f0c03a9$0$59955$c30e37c6lon-reader.news.telstra.net...
    > > Hello, I am in a position where i need to create a query where i must
    > join
    > > two tables on the nearest value.
    > >
    > > Imagine i have 2 tables, tbl_Trade and tbl_Time. For each Time in
    > tbl_Time,
    > > I need to locate the row in tbl_Trade where tbl_Time.LastUpdate is closest
    > > to, but less than the value of tbl_Time.myTime.
    > >
    > > I have been using the followng query
    > >
    > > Select Avg(s.Price) AS Price , t.myTime
    > > From tbl_Trade s
    > > Inner Join tbl_Times t
    > > On (t.myDate + '2003-07-08') > s.LastUpdate
    > > AND (t.myDate + '2003-07-08' - '1:00 AM') < s.LastUpdate
    > > Inner Join tbl_StockList l
    > > On l.ID = s.StockID
    > > Where l.Code = 'ORI'
    > > Group By t.myTime
    > >
    > > But this relies on using an average of all trades in the time period
    > (which
    > > in this case is 1 hour).
    > >
    > > Essentially the problem is that each trade can occur at any time, so there
    > > is not necessarily a trade that occurs in that period, nor is an average
    > of
    > > the value of the average of the prices necessarily indicative of the value
    > > we wish to retrieve.
    > >
    > > So basically what i want to be able to do is to generate a recordset of
    > the
    > > value of the price of the last trade at each hour O, O is specified in a
    > > table. Is such a join possible? Or is there a work around aside from
    > using
    > > triggers to populate a table of prices for each hour for each commodity?
    > >
    > > Cheers
    > >
    > >
    Gert-Jan Strik Guest

Similar Threads

  1. Question Database join query
    By iconic in forum Brainstorming Area
    Replies: 0
    Last Post: September 10th, 09:42 AM
  2. sql join query
    By dylan in forum MySQL
    Replies: 8
    Last Post: August 24th, 12:07 PM
  3. Inner Join Query
    By KimMazz in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 13th, 05:19 PM
  4. Add another join to a query
    By Conti in forum Coldfusion Database Access
    Replies: 6
    Last Post: March 20th, 06:23 PM
  5. SQL join query help
    By poff in forum PHP Development
    Replies: 2
    Last Post: July 13th, 02:36 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139