Breakout Query for prices

[TylerTand]

I am trying to write a breakout formula to identify if today's close is greater than the maximum close of the last 20 days. This is what I have so far:

SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose
FROM WZ07
WHERE (((DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))) Between Date()-28 And Date()-1) AND ((WZ07.DClose)=(select max([WZ07].DClose) from [WZ07])));


If I replace the = symbol with a > symbol near the end will it compare today's close to the max of the date range selected? I thought I had this figured out but apparently not.

I can do a query that will give me the last 20 days using the "Between date()-28 and date()-1 criteria. I can do a separate query for the maximum close using select max([WZ07].DClose) from [WZ07] but when I try to put them together it screws up. I need to have it first do the last 20 days query and then take the maximum close from it, and then compare to see if today's close is greater than that number. Can this all be put together? Thanks for your help.

 

[EMP]

I need to have it first do the last 20 days query and then take the maximum close from it,

The following may be what you are after.

SELECT WZ07.Symbol, WZ07.AlphaDeliveryMonth, DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, WZ07.DClose
FROM WZ07
WHERE DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) Between Date()-28 And Date()-1
AND WZ07.DClose=(Select max([WZ07].DClose) from [WZ07] where DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) Between Date()-28 And Date()-1);

^

 

[TylerTand]

I copied the formula and it gives me a data mismatch error message. I think because you have two criteria that have to be reached. It doesn't seem to like having criteria for the date (to get the range of last 20 days) and the max of the close. Any other ideas how to write this?

 

[EMP]

I just repeated the Where clause in your subquery. So if the Where clause worked in your original query, it should also work in the subquery.

Anyhow I've no problem running the statement. It returned the following results in the attached database.

Symbol	AlphaDeliveryMonth	transdate	DClose
S003	ccc			8/28/2007	80

You can see if my table structure is different from yours.

^

 

[TylerTand]

O.K. I figured it out. I was missing one part to make it look at today's date and then see if the close was greater than the range of the last 20 days. Thanks for all your help. But of course we are not done. Is there a way to apply this to multiple tables? Since I have 200 that I would like to run this against I could use some help organizing it. I was thinking something like this:

SELECT *.Symbol, *.AlphaDeliveryMonth,DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) AS transdate, *.DClose
FROM *
WHERE (((DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))))=Date()) AND ((*.DClose)>(Select max([*].DClose) from [*] where DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2))) Between Date()-28 And Date()-1)));

I know this doesn't work but my point is, how can this query be applied to multiple tables and return the breakouts for all tables(if they exist) in one query?