Average time?

[Noozer]

I have a table:

TicketNo: Number
Added: Date/Time
Action1: Date/Time
Action2: Date/Time

Action1 is null until the user does an update, which is when the date is
added.
Action2 is null until the user does a second update, which is when the date
is added.

....I need to find the average times for:
1) Added to Action1
2) Added to Action2
3) Action1 to Action2

Obviously, for #1 I have to ignore rows where Action1 is null. Same for #2;
ignore any row where Action2 is null. And again #3 should ignore rows where
Action1 is null.

How do I calculate averages on date/time columns? I assume I'll be using
DateDiff... something like would compute the average minutes:

SELECT AVG(Minutes) FROM (SELECT DateDiff("n",Date1,Date2) AS Minutes FROM
(SELECT Added As Date1, Action1 as Date2 FROM MyTable WHERE NOT
ISNULL(Action1)));

....am I close?

 

[Noozer]

How do I calculate averages on date/time columns? I assume I'll be using
DateDiff... something like would compute the average minutes:

SELECT AVG(Minutes) FROM (SELECT DateDiff("n",Date1,Date2) AS Minutes FROM
(SELECT Added As Date1, Action1 as Date2 FROM MyTable WHERE NOT
ISNULL(Action1)));

Hmm... some of this is sticking. I got it RIGHT the first time! DOH!

 

[John Vinson]

Hmm... some of this is sticking. I got it RIGHT the first time! DOH!

<g> Ain't that such fun!

Just one suggestion: the IsNull() function works but it's less
efficient than using the SQL syntax:

SELECT AVG(Minutes) FROM (SELECT DateDiff("n",Date1,Date2) AS Minutes
FROM
(SELECT Added As Date1, Action1 as Date2 FROM MyTable WHERE Action1 IS
NOT NULL));

or, lose the subquery:

SELECT Avg(DateDiff("n",Added,Action1)) AS AvgMinutes
FROM MyTable
WHERE Action1 IS NOT NULL;

John W. Vinson[MVP]

 

[Noozer]

Just one suggestion: the IsNull() function works but it's less
efficient than using the SQL syntax:

or, lose the subquery:

SELECT Avg(DateDiff("n",Added,Action1)) AS AvgMinutes
FROM MyTable
WHERE Action1 IS NOT NULL;

Thanks muchly! It's always appreciated!