SQL averages - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL averages

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2008-06-18 : 05:41:11

Is there anyway to find an average date value in SQL? it may sound like a strange question. Im on the bench as to whether it is possible

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 05:42:26

Use AVG() function.

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-06-18 : 05:45:53

its saying this...

Operand data type datetime is invalid for avg operator.

hence why i didnt think it could be done

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 05:55:30

And you deleted your two other responses...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 05:56:24

How well do you want to caclulate the average date? By the hour? By the second? By the day?

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-06-18 : 05:57:26

by the day is good enough for what im going to use it for. thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 05:58:41

And how do you want to round off the average day?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 06:00:10

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-06-18 : 06:00:30

round it off to the exact average day. if it can be done.

how else would you round off a date?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 06:02:01

To 12:00 AM or to 12:00 PM.

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-06-18 : 06:02:45

PM's good

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 06:09:37

[code]DECLARE @Sample TABLE (dt DATETIME)

INSERT @Sample
SELECT ABS(CHECKSUM(NEWID())) / 13327.2343249
FROM master..spt_values
WHERE Type = 'p'

SELECT DATEADD(MINUTE, AVG(1.0 * DATEDIFF(MINUTE, '19000101', dt)), '19000101')
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 06:15:55

[code]DECLARE @Sample TABLE (dt DATETIME)

INSERT @Sample
SELECT ABS(CHECKSUM(NEWID())) / 13327.2343249
FROM master..spt_values
WHERE Type = 'p'

SELECT DATEADD(DAY, AVG(1.0 * DATEDIFF(DAY, '19000101', dt)), '19000101')
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-06-18 : 06:18:32

hmm not sure what that is doing. looks complex

it would be nice if i could find the average of mail1date where mail2date is blank and mail3date is blank etc etc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 06:20:05

Add a WHERE filter maybe?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 06:22:25

[code]-- Prepare sample data because I don't have access to your environment
DECLARE @Sample TABLE (mail1date DATETIME, mail2date DATETIME, mail3date DATETIME)

INSERT @Sample
(
mail1date
)
SELECT ABS(CHECKSUM(NEWID())) / 13327.2343249
FROM master..spt_values
WHERE Type = 'p'

-- This is the final query that produces the average date
SELECT DATEADD(DAY, AVG(1.0 * DATEDIFF(DAY, '19000101', mail1date)), '19000101')
FROM @Sample
WHERE mail1date IS NOT NULL
mail2date IS NULL
mail3date IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources