Max of two queries

[DianaW]

Dear All,

I am not the SQL expert and I would be very happy if someone could help me with the following question:

The first query gives me the closing price of the last trading day (here just 11/28/08):

SELECT Ticker, Close
FROM Data
WHERE dDate=#11/28/2008#
GROUP BY Ticker, dDate, Close

The second query tells me, what the highest closing price in the last 20 trading days (excluding the last one, 11/28/08) was:

SELECT Ticker, MAX(Close)
FROM
    (SELECT TOP 20 Ticker, Close
     FROM Data
     WHERE dDate<#11/28/2008#
     GROUP BY Ticker, dDate, Close
     ORDER BY dDate DESC)
GROUP BY Ticker, dDate

Both of them work so far. Now I have two questions:
1) I want to combine them into one query which gives me the maximum of both results for each Ticker and the corresponding Date. I guess I have to work with Joins but honestly I have no idea...
2) I want that the end result shows only those, where the price of the last trading day (i.e, here 11/28/08) is greater than the highest of the last 20 trading days. I guess the result of question 1 then needs some "Having" clause?

I would be very happy if somebody could give me some hint here...

Thank you very much.

Diana

(Data has 6 columns: Ticker, dDate, Open, High, Low, Close and contains price data for stocks)

 

[Brianwarnock]

I don't see why the 1st of your two new queries cannot be just the second of your original modified for 21 days and <= to the last trading date.

A new query is required for the 2nd question ie join queries 1 and 2 on ticker and select from query1 where close > Maxofclose from query2. You use a Where clause as this is not an aggregate query.


Brian

 

[DianaW]

Brian,

thanks for your help. Re the first comment - you are right. It's actually easier to just do it for the Top21 values.

Re the second comment - I have tried to put together the following request:

SELECT Ticker, dDate
FROM (
   SELECT Ticker, MAX(Close) AS MaxOfClose
   FROM 
      (SELECT TOP 21Ticker, Close
      FROM  Data
      WHERE dDate<=#11/28/2008#
      GROUP BY Ticker, dDate, Close
      ORDER BY dDate DESC)
      GROUP BY Ticker) As Tab1
   INNER JOIN 
      (SELECT Ticker, Close
      FROM Data
      WHERE dDate=#11/28/2008#
      AND MaxOfClose>Close
      GROUP BY Ticker, Close) AS Tab2
   ON Tab1.Ticker AND Tab2.Ticker
WHERE dDate=#11/28/08#
GROUP BY Ticker

However, Access just tells me that it does not support the JOIN expression. Where is my mistake?

Diana

 

[Brianwarnock]

I'm a simple guy and usually go for the simple approach, I would have had a 3rd query like

Select ticker, dDate, Close
From Query1 InnerJoin query2 on query1.ticker = query2.ticker
Where query1.close>query2.MaxofClose

note don't trust that syntax, it is Sturday

This is your original 2nd query not your new one as the maxofclose there could be on the last trading day, perhaps >= might resolve that.

Brian

 

[Brianwarnock]

PS to answer your question having now looked at your code there should be an = not an and
Tab1.Ticker = Tab2.Ticker

Brian

PS I'm not sure about other aspects of your code, but as i said I'm a simple guy.

 

[DianaW]

Thanks Brian... I think I am close to the solution now... Splitting it up is a good idea. In VBA it now looks like that:

Dim query1 As String
query1 = "SELECT Ticker, Close FROM 5401.txt WHERE dDate = #11/28/2008# GROUP BY Ticker, dDate, Close"
 
Dim query2 As String
query2 = "SELECT TOP 20 Ticker, Close" & _
                " FROM 5401.txt" & _
                " WHERE dDate < #11/28/2008#" & _
                " GROUP BY Ticker, dDate, Close" & _
                " ORDER BY dDate DESC"
 
Dim query3 As String
query3 = "SELECT Ticker, MAX(Close) AS MaxOfClose" & _
            " FROM (" & query2 & ")" & _
            " GROUP BY Ticker"
    
sSQL = "SELECT Ticker, dDate, Close" & _
            " FROM (" & query1 & ") AS Q1 INNER JOIN (" & query3 & ") AS Q2" & _
            " ON Q1.Ticker = Q2.Ticker" & _
            " WHERE Q1.Close > Q2.MaxOfClose"

Query1, Query2 and Query3 work separately. However, there must still be some minor problem in the final sSQL. The error message says that the field "Ticker" can refer to more than one table, which are mentioned in the FROM clause. ...what's the mistake here, can you help???

Diana

 

[jal]

I haven't read all this so I am a little lost but probably this line:

sSQL = "SELECT Ticker, dDate, Close"

should be changed to something like this:

sSQL = "SELECT Q1.Ticker, Q1.dDate, Q1.Close"

or maybe this:

sSQL = "SELECT Q2.Ticker, Q2.dDate, Q2.Close"

Or maybe some combination of Q1 or Q2 (I am just guessing wildly here)


sSQL = "SELECT Q1.Ticker, Q2.dDate, Q2.Close"

Anyway the root cause of this is that a join is a physical join (the left row gets attached to the right row resulting in a long row). So if you are joining a table to itself, each column name will appear twice in the result, so you have to tell the engine whether you want the right version or the left version (the Q1 version or the Q2 version).

 

[Brianwarnock]

Hi Diane,
Jal is correct that when 2 data sources have the same named fields then you must qualify the fields, yeah! yeah! I know I didn't in my sample SQL in post 4 but I did say don't trust the syntax
Sorry about that but air code is prone to the odd error and is best treated as a pointer to what to do rather than the definitive answer.

Brian

 

[DianaW]

Thank you.

Jal, Brian,

thank you very much for your help. The code now works.

Best regards
Diana