Time Aggregate

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

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
 
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
 
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
 
Thanks for that - worked a treat!
Les


John Spencer (MVP) said:
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 said:
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
Click to expand...
Click to expand...
 
Thanks for that - worked a treat!
Les


Dale Fye said:
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

Leslie Isaacs said:
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
Click to expand...
Click to expand...
 
Back
Top