Modifying a Query To Reflect Business days

[carl]

I am using this:

SELECT Trades.Month, Trades.FirmId, Sum(Trades.TradeVolume)/20 AS
AverageTradeVolume
FROM Trades
GROUP BY Trades.Month, Trades.FirmId;

The "/20" is an attempt to get the average daily volume (20 business days
per month).

However, if I am half way through the month, the statistic get skewed.

Is there a way to get the average daily volume that is pased on the actual
number of business days that have elapsed ?

Thank you in advance.

 

[KARL DEWEY]

I do not know how accurate you want to be but try this ---
SELECT Trades.Month, Trades.FirmId, Sum(Trades.TradeVolume)/Day(Date())/7*5
AS AverageTradeVolume
FROM Trades
GROUP BY Trades.Month, Trades.FirmId;

This takes the day of the month, divides by 7 (days in a week) time 5 (work
days in a week) for your divisor.

 

[Ron2006]

I do not know how accurate you want to be but try this ---
SELECT Trades.Month, Trades.FirmId, Sum(Trades.TradeVolume)/Day(Date())/7*5
AS AverageTradeVolume
FROM Trades
GROUP BY Trades.Month, Trades.FirmId;

This takes the day of the month, divides by 7 (days in a week) time 5 (work
days in a week) for your divisor.
--
KARL DEWEY
Build a little - Test a little










- Show quoted text -

If you are not worried about holidays then you can use.....


WorkDays=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",
[StartDate],[EndDate
],7)+DateDiff("ww",[StartDate],[EndDate],1))

watch word wrap

first datediff is how many days,
second is number of Saturdays
Third is number of Sundays.