finding the max of a subquery

[inlove22]

Here is my dilemma, I have a table (Stockdata) with stock data in the
following format.

Symbol - High - Low - Close - TradeDate

And have another table called ExpiryDates which is just one column of
dates i.e

OEXdate
2007-01-15
2006-12-15
2006-11-16
2006-10-17
---
---etc

So I have the following query which obtains the High prices from the
beginning of the Expirydate + the next 7 days.

SELECT tbl1.Symbol, tbl1.TradeDate, tbl1.High
FROM StockData as tbl1, ExpiryDate as tbl2
WHERE tbl1.TradeDate BETWEEN (tbl2.OEXdate+1) AND (tbl2.OEXdate+7);

This gets me the following data.

symbol tradedate high
AAPL 19/01/2007 89.65
AAPL 18/01/2007 92.11
AAPL 17/01/2007 97.6
AAPL 16/01/2007 97.25
.....
....
....
AMZN 19/01/2007 37.48
AMZN 18/01/2007 37.65
AMZN 17/01/2007 39
AMZN 16/01/2007 38.89
AMZN 15/12/2006 40.19
....
....
etc...

So what i would like now is the a query that will provide me a High
for that week for each Symbol?

Any ideas?
Thanks

Al

 

[Dale Fye]

Al,

Don't believe your results are indicative of the SQL string and the OEXDate
table you gave us, but will assume that all the extra lines for each of the
weeks are covered in the ...... portion of your output. Additionally, the
BETWEEN portion of your where clause implies that you are computing the high
for the 7 days following OEXDate, not "beginning on Expirydate + the next 7
days"

I think your query (I'll refer to it as query1) should read:
SELECT tbl1.Symbol, ExpiryDates.OEXdate, tbl1.TradeDate, tbl1.High
FROM StockData as tbl1, ExpiryDate as tbl2
WHERE tbl1.TradeDate BETWEEN (tbl2.OEXdate) AND (tbl2.OEXdate+6);

Once you have that, you can do:

SELECT qry1.Symbol, qry1.OEXDate, MAX(qry1.High) as WeeklyHigh
FROM query1
GROUP BY qry1.Symbol, qry1.OEXDate

HTH
Dale