SQL for Multiple Parameters

[EoinC]

Hi - thanks in advance for help.

I have this query that will calcuate my positions on any day that I
enter. My positions is equal to the sum of my Shares on previous days.

SELECT StockTransactions.DealCode, StockTransactions.Stock,
Sum(StockTransactions.Shares) AS SumOfShares
FROM StockTransactions
WHERE StockTransactions.TradeDate<[As Of]
GROUP BY StockTransactions.Stock
HAVING Sum(StockTransactions.Shares)>0;

This is fine however, I'd like to have the query run for every day
between two
dates that I enter (for every day - not just trade dates).

So, if I input start date as Jan 1 and end date as Jan 15 it will run
the above query repeatdly using Jan 1 as [As Of], Jan 2 as [As Of],
Jan 3 as [As Of],.....Jan 15 as [As Of].

I'm only using one table, however I'm wondering if I should have
another table with every calendar day in it and bring it into the
query.

STOCK SHARES PRICE TRADEDATE SETTDATE
BBDb.TO 200 20 01-Jan-07 04-Jan-07
ANP.TO 100 10 01-Jan-07 04-Jan-07
BCB.TO 300 30 01-Jan-07 04-Jan-07
ANP.TO 100 10.1 10-Jan-07 13-Jan-07
BCB.TO 300 30.1 10-Jan-07 13-Jan-07
BBDb.TO 200 20.1 10-Jan-07 13-Jan-07
BCB.TO -150 30 15-Jan-07 18-Jan-07
BBDb.TO -100 20 15-Jan-07 18-Jan-07
ANP.TO -50 10 15-Jan-07 18-Jan-07
ANP.TO 100 10.2 01-Feb-07 04-Feb-07
BCB.TO 300 30.2 01-Feb-07 04-Feb-07
BBDb.TO 200 20.2 01-Feb-07 04-Feb-07

THANKS - I posted this a few days ago and received some help however I
haven't been able to crack it.

 

[KARL DEWEY]

Maybe this is what you are looking for. Build a table named CountNumber,
field called CountNUM, and filled with numbers from 0 (zero) to your maximum
spread.
EoinC_1 ---
SELECT DateAdd("d",[CountNUM],[Enter start]) AS Dates
FROM StockTransactions, CountNumber
WHERE (((DateAdd("d",[CountNUM],CVDate([Enter start])))<=CVDate([Enter
end])) AND ((StockTransactions.TradeDate)>=CVDate([Enter start])))
GROUP BY DateAdd("d",[CountNUM],[Enter start])
ORDER BY DateAdd("d",[CountNUM],[Enter start]);

SELECT StockTransactions.DealCode, StockTransactions.Stock,
StockTransactions.Shares, StockTransactions.PRICE, EoinC_1.Dates AS
Trade_Dates, StockTransactions.SETTDATE
FROM EoinC_1 LEFT JOIN StockTransactions ON EoinC_1.Dates =
StockTransactions.TradeDate;

 

[steve]

If you change your where criteria to something like:

WHERE StockTransactions.TradeDate Between [BeginningDate] and [EndingDate]

it should do what you want.

Steve