Best way of getting Avg Date?

Question

Best way of getting Avg Date?

bp

SSCarpal Tunnel

Points: 4656
More actions
August 28, 2003 at 12:24 am

#81674

What is the fastest and most reliable way of figuring out the average date? Convert to number, average it and back?
Is it:
DECLARE @tt TABLE (THE_DATE SMALLDATETIME)
INSERT INTO @tt VALUES('1/1/2003');
INSERT INTO @tt VALUES('1/2/2003');
INSERT INTO @tt VALUES('1/3/2003');
INSERT INTO @tt VALUES('1/4/2003');
INSERT INTO @tt VALUES('1/5/2003');
SELECT CAST(AVG(CAST(THE_DATE AS INT)) AS SMALLDATETIME) FROM @tt;
Thanks in advance,
Billy
Edited by - bp on 08/28/2003 12:41:22 AM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Billy,

quote:
What is the fastest and most reliable way of figuring out the average date? Convert to number, average it and back?
Is it:
DECLARE @tt TABLE (THE_DATE SMALLDATETIME)
INSERT INTO @tt VALUES('1/1/2003');
INSERT INTO @tt VALUES('1/2/2003');
INSERT INTO @tt VALUES('1/3/2003');
INSERT INTO @tt VALUES('1/4/2003');
INSERT INTO @tt VALUES('1/5/2003');
SELECT CAST(AVG(CAST(THE_DATE AS INT)) AS SMALLDATETIME) FROM @tt;

what do you mean by 'average date'?

The modus, aritmitetical mean, geometrical mean ????

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

bp SSCarpal Tunnel Points: 4656 More actions · Answer 2

hmmm.. good question.

The type which the AVG() aggregate function used to generate summary values in query resultset.

I tried SELECT AVG(THE_DATE) FROM @tt but it returns the following error message:

Server: Msg 409, Level 16, State 2, Line 13

The average aggregate operation cannot take a smalldatetime data type as an argument.

bp SSCarpal Tunnel Points: 4656 More actions · Answer 3

I can also think of:

SET NOCOUNT ON

DECLARE @tt TABLE (THE_DATE SMALLDATETIME)

DECLARE @current_date SMALLDATETIME

SET @current_date = CONVERT(SMALLDATETIME, CONVERT(VARCHAR(10), GETDATE(), 101), 101)

INSERT INTO @tt VALUES('1/1/2003');

INSERT INTO @tt VALUES('1/2/2003');

INSERT INTO @tt VALUES('1/3/2003');

INSERT INTO @tt VALUES('1/4/2003');

INSERT INTO @tt VALUES('1/5/2003');

SELECT DATEADD(D,-AVG(DATEDIFF(D, THE_DATE, @current_date)),@current_date) FROM @tt;

... but not sure if it is any better...

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

quote:
The type which the AVG() aggregate function used to generate summary values in query resultset.

AVG uses the simple arithmetical mean.

That is the SUM of all values (ex NULL) divided by the number of all values (ex NULL)

So in your the 'average integer' behind the dates.

I was just asking, in which context you want to compute an average date?

Should be a funny conclusion to say something like:

My family is born on average on 07/22/1985

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 5

hi,

we had a similar problem, and I used a base date to work out the average :-

DECLARE @tt TABLE (THE_DATE SMALLDATETIME)

INSERT INTO @tt VALUES('1/1/2003');

INSERT INTO @tt VALUES('1/2/2003');

INSERT INTO @tt VALUES('1/3/2003');

INSERT INTO @tt VALUES('1/4/2003');

INSERT INTO @tt VALUES('1/5/2003');

select dateadd(day, avg(datediff(day, "Jan 1 1900" , THE_DATE)),

"Jan 1 1900")

from @tt

this works by working out the average number of days from "Jan 1 1900" and then turning the result back into a date.

HTH

Paul