# Average time?

N

#### Noozer

I have a table:

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

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

....I need to find the average times for:
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?

N

#### 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!

J

#### 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:

FROM MyTable
WHERE Action1 IS NOT NULL;

John W. Vinson[MVP]

N

#### Noozer

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