Time Aggregate

[Leslie Isaacs]

Hello All
I have a query based on a table which includes the following (hopefully
self-expalnatory) fields:

[client]
[period]
[taskstart]
[taskend]

Each [client] appears many times, once for each [period].
[taskstart] and [taskend] are time fields.

I need to calculate the total, and average, time spent ( = the gap between
[taskstart] and [taskend] ) for each [client]. I have tried using the
datediff function, which works OK in giving me all the individual 'gaps',
but I cannot then change to a Totals query - I get the error message about
trying to use a domain aggregate function ... .

I would be very gretafuol for any help.

Many thanks
Leslie Isaacs

 

[Dale Fye]

Leslie,

Try something like the following. I'm assuming that the taskstart and
taskend are date/time fields and that you want to measure the number of
minutes between taskstart and taskend.

SELECT T.Client
, Sum(T.Duration) as TotalMinutes
, AVG(T.Duration) as AvgMinutes
FROM (SELECT Client
, DateDiff("n", [TaskStart], [TaskEnd]) as
Duration
FROM yourTable
WHERE [Period] BETWEEN X AND Y) as T
GROUP BY T.Client

**Note that I used and 'n', not an 'm' in the datediff function. You will
need to substitute the appropriate values in for the variables:
yourTable
X
Y

HTH
Dale

 

[John Spencer (MVP)]

Don't know how fine you need your Total (Seconds, Minutes, Hours) and averages,
but you need to do something like the SQL below to get a number of seconds ("s")
or minutes ("n"). Once you have that, you can manipulate the results with some
math functions to get hours or minutes if needed.

SELECT Client,
Sum(DateDiff("s",TaskStart,TaskEnd)) as TotalSecs,
Avg(DateDiff("s",TaskStart,TaskEnd)) as AvgSecs
FROM TheTable
GROUP BY Client

 

[Leslie Isaacs]

Thanks for that - worked a treat!
Les

Don't know how fine you need your Total (Seconds, Minutes, Hours) and averages,
but you need to do something like the SQL below to get a number of seconds ("s")
or minutes ("n"). Once you have that, you can manipulate the results with some
math functions to get hours or minutes if needed.

SELECT Client,
Sum(DateDiff("s",TaskStart,TaskEnd)) as TotalSecs,
Avg(DateDiff("s",TaskStart,TaskEnd)) as AvgSecs
FROM TheTable
GROUP BY Client

Hello All
I have a query based on a table which includes the following (hopefully
self-expalnatory) fields:

[client]
[period]
[taskstart]
[taskend]

Each [client] appears many times, once for each [period].
[taskstart] and [taskend] are time fields.

I need to calculate the total, and average, time spent ( = the gap between
[taskstart] and [taskend] ) for each [client]. I have tried using the
datediff function, which works OK in giving me all the individual 'gaps',
but I cannot then change to a Totals query - I get the error message about
trying to use a domain aggregate function ... .

I would be very gretafuol for any help.

Many thanks
Leslie Isaacs

 

[Leslie Isaacs]

Thanks for that - worked a treat!
Les

Leslie,

Try something like the following. I'm assuming that the taskstart and
taskend are date/time fields and that you want to measure the number of
minutes between taskstart and taskend.

SELECT T.Client
, Sum(T.Duration) as TotalMinutes
, AVG(T.Duration) as AvgMinutes
FROM (SELECT Client
, DateDiff("n", [TaskStart], [TaskEnd]) as
Duration
FROM yourTable
WHERE [Period] BETWEEN X AND Y) as T
GROUP BY T.Client

**Note that I used and 'n', not an 'm' in the datediff function. You will
need to substitute the appropriate values in for the variables:
yourTable
X
Y

HTH
Dale

Hello All
I have a query based on a table which includes the following (hopefully
self-expalnatory) fields:

[client]
[period]
[taskstart]
[taskend]

Each [client] appears many times, once for each [period].
[taskstart] and [taskend] are time fields.

I need to calculate the total, and average, time spent ( = the gap between
[taskstart] and [taskend] ) for each [client]. I have tried using the
datediff function, which works OK in giving me all the individual 'gaps',
but I cannot then change to a Totals query - I get the error message about
trying to use a domain aggregate function ... .

I would be very gretafuol for any help.

Many thanks
Leslie Isaacs