Challenging SQL Query Problem! 1

[mtsugawa]

Hi All:

I've got an interesting SQL challenge for anyone that's interested:

I have the following two tables:

SECURITIES PRICE_HISTORY
========== ==============
SYMBOL SYMBOL DATE PRICE
------ ------ --------- -----
AAPL AAPL MONDAY 25.21
MSFT AAPL TUESDAY 25.45
ORCL MSFT MONDAY 62.14
MSFT THURSDAY 61.05
MSFT FRIDAY 64.07
ORCL MONDAY 16.45
ORCL TUESDAY 15.85
ORCL WEDNESDAY 16.05

I want to calculate my portfolio value for each day that all my securities traded. For example, since there is no price for AAPL on WEDNESDAY, I don't want to calculate the portfolio value for this date. However, since there are prices for all three securities on MONDAY, I do want to calculate the portfolio value.

The following query gives me the portfolio value for all the dates (MONDAY - FRIDAY). However, only the value for MONDAY is accurate since this is the only date that all three securities in my portfolio traded.

SELECT ph.symbol,
SUM( ph.price )
INTO v_daily_portfolio_value
FROM price_history ph,
security s
WHERE s.symbol = ph.symbol
GROUP BY ph.symbol
ORDER BY ph.symbol;

How can I exclude the rows for dates when at least one of the securities did not trade? Another way of phrasing this is: I only want to calculate the date's portfolio value if all of my securities (AAPL, MSFT and ORCL) have a price in the price_history table on the given date.

Thanks for any help you can offer.

Mark

 

[Dagon]

SELECT S.symbol,
SUM( ph.price ) price
FROM price_history ph,
security s
WHERE s.symbol = ph.symbol
and the_date = '&Date'
AND NOT EXISTS
(SELECT 'X' FROM security S2, PRICE_HISTORY PH2
where s2.symbol = ph2.symbol (+)
and ph2.the_date (+) = '&Date'
and ph2.symbol is null)
GROUP BY S.symbol
ORDER BY S.symbol