Help with table JOIN

JerryEF · #1 (**permalink**) 04-25-09, 07:49

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)

r937 · #2 (**permalink**) 04-25-09, 09:52

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

JerryEF · #3 (**permalink**) 04-25-09, 11:13

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.

r937 · #4 (**permalink**) 04-25-09, 11:26

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

JerryEF · #5 (**permalink**) 04-25-09, 12:59

Thank you...

Internet Services

Web Development

Digital Marketing

Consumer Tech