Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: Help with table JOIN

    I am looking for a 'vanilla' ANSI way of doing the following join. I would like to avoid any vendor specific syntax.

    I have a table of securities descriptive data (STOCKS) and a table of daily stock prices (PRICES). I want to create a query (view) that will join the STOCKS table with the PRICES table such that the resulting view will contain a single row for each STOCKS row with columns for Current.Date, Current.Price, Prior.Date, Prior.Price where ‘Current’ is defined as the single row from PRICES with a date that is <= one defined date and ‘Prior’ is defined as the single row from PRICES with a date that is <= a second defined date.

    STOCKS(Symbol, Name)
    PRICES(Symbol, Date, Price)

    STOCKS(‘MSFT’, ‘Microsoft Corporation’)
    PRICES(‘MSFT’, ‘2009-03-31’, 18.375),
    (‘MSFT’, ‘2009-02-27’, 16.150),
    (‘MSFT’, ‘2008-12-31’, 19.44))

    for example:
    select …where CurrDate <= 2009-03-31 and PriorDate <= 2009-02-28… would return:
    (‘MSFT’, 2009-03-31, 18.375, 2009-02-27, 16.150)

    (note: 2009-02-28 was a Saturday. The <= allows the prior valid price to be returned)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    everything made sense up until this part --
    Quote Originally Posted by JerryEF
    ... where ‘Current’ is defined as the single row from PRICES with a date that is <= one defined date and ‘Prior’ is defined as the single row from PRICES with a date that is <= a second defined date.
    totally confused by that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Posts
    3

    Clarification

    Sorry 'bout that.

    I will be providing 2 dates that will represent 'CurrentDate' and 'PriorDate': For example: CurrentDate might be Today and PriorDate might be last month end, or last year end or yesterday.... The resulting row will contain the prices for each of the two dates AND the actual dates associated with those two prices. The example in the original post shows that for a 'requested' date of 2009-02-28 (month end on a Saturday) we will get the preceding market day or 2009-02-27.

    I hope this clarifies.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT s.Symbol
         , s.Name
         , p1.Date  AS prior_date
         , p1.Price AS prior_price
         , p2.Date  AS curr_date
         , p2.Price AS curr_price
      FROM stocks AS s
    INNER
      JOIN ( SELECT Symbol
                  , MAX(Date) AS latest_date
               FROM prices
              WHERE Date <= $priordate ) AS d1
        ON d1.Symbol = s.Symbol
    INNER
      JOIN prices AS p1
        ON p1.Symbol = s.Symbol
       AND p1.Date = d1.latest_date         
    INNER
      JOIN ( SELECT Symbol
                  , MAX(Date) AS latest_date
               FROM prices
              WHERE Date <= $currentdate ) AS d2
        ON d2.Symbol = s.Symbol
    INNER
      JOIN prices AS p2
        ON p2.Symbol = s.Symbol
       AND p2.Date = d2.latest_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    Thank you...

Posting Permissions

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