Avg, Max, Min, Deviation for timedate

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

SQL Server 2008 Forums

Transact-SQL (2008)

Avg, Max, Min, Deviation for timedate

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-24 : 00:33:18

How to calculate Avg, Min, Max, Standard Deviation for each person with multiple timedate values in a row. Only interested in 00:06:31 format as the result

Some intervals maybe NULL.

Select Name, [Interval 1], [Interval 2], [Interval 3], [Interval 4], [Interval 5], [Interval 5]
From Interval

Name Iterval 1 Iterval 2 Iterval 3 Iterval 4 Iterval 5 Iterval 5
Kim Mottrom 2012-06-24 00:06:39.000 2012-06-24 00:06:34.000 2012-06-24 00:06:33.000 2012-06-24 00:06:31.000 2012-06-24 00:06:28.000 2012-06-24 00:06:28.000
Justin Hill 2012-06-24 00:07:07.000 2012-06-24 00:07:12.000 2012-06-24 00:07:19.000 2012-06-24 00:07:19.000 2012-06-24 00:07:13.000 2012-06-24 00:07:13.000
Megan Szirom NULL NULL NULL NULL NULL NULL
Peter Crump 2012-06-24 00:07:41.000 2012-06-24 00:07:49.000 2012-06-24 00:07:48.000 2012-06-24 00:07:51.000 2012-06-24 00:07:44.000 2012-06-24 00:07:44.000
Bill Starr 2012-06-24 00:10:08.000 2012-06-24 00:10:11.000 2012-06-24 00:10:04.000 2012-06-24 00:10:15.000 2012-06-24 00:10:16.000 2012-06-24 00:10:16.000
Jim Hoare 2012-06-24 00:10:48.000 2012-06-24 00:10:58.000 2012-06-24 00:10:07.000 2012-06-24 00:10:07.000 2012-06-24 00:10:11.000 2012-06-24 00:10:11.000
Rhiannon Lovegrove 2012-06-24 00:08:18.000 NULL 2012-06-24 00:08:28.000 NULL NULL NULL
Amber Tuschorski 2012-06-24 00:08:21.000 2012-06-24 00:08:21.000 2012-06-24 00:08:26.000 2012-06-24 00:08:28.000 2012-06-24 00:08:43.000 2012-06-24 00:08:43.000
Alix Harlington 2012-06-24 00:07:26.000 NULL 2012-06-24 00:07:38.000 NULL 2012-06-24 00:07:32.000 2012-06-24 00:07:32.000
Corey Harlington 2012-06-24 00:12:19.000 NULL 2012-06-24 00:11:41.000 NULL NULL NULL
Danielle Walsh 2012-06-24 00:08:09.000 NULL 2012-06-24 00:08:22.000 NULL NULL NULL
Rhiana Hooker 2012-06-24 00:08:38.000 NULL 2012-06-24 00:09:31.000 NULL 2012-06-24 00:09:20.000 2012-06-24 00:09:20.000
Ayeisha Wallace 2012-06-24 00:09:57.000 NULL 2012-06-24 00:09:59.000 NULL NULL NULL
Troy Hooker 2012-06-24 00:11:05.000 2012-06-24 00:11:30.000 2012-06-24 00:11:29.000 2012-06-24 00:11:37.000 2012-06-24 00:11:39.000 2012-06-24 00:11:39.000
Anna Cross 2012-06-24 00:07:26.000 2012-06-24 00:07:44.000 NULL 2012-06-24 00:07:59.000 2012-06-24 00:07:54.000 2012-06-24 00:07:54.000
Beth Cross 2012-06-24 00:08:55.000 NULL 2012-06-24 00:08:40.000 2012-06-24 00:09:35.000 NULL NULL
Summer Pelenstov 2012-06-24 00:12:12.000 NULL 2012-06-24 00:11:41.000 NULL NULL NULL
Gloria Holliday 2012-06-24 00:08:05.000 2012-06-24 00:08:06.000 2012-06-24 00:08:08.000 2012-06-24 00:08:03.000 2012-06-24 00:08:22.000 2012-06-24 00:08:22.000
Lachlan Tyler-Dowd 2012-06-24 00:08:09.000 2012-06-24 00:09:13.000 NULL 2012-06-24 00:08:03.000 2012-06-24 00:08:13.000 2012-06-24 00:08:13.000
Michael Nicolaides 2012-06-24 00:09:41.000 2012-06-24 00:10:56.000 2012-06-24 00:11:32.000 NULL NULL NULL
Mark Hooker 2012-06-24 00:12:51.000 NULL 2012-06-24 00:13:16.000 NULL 2012-06-24 00:11:52.000 2012-06-24 00:11:52.000
Ryan Hooker 2012-06-24 00:12:45.000 NULL 2012-06-24 00:12:24.000 NULL 2012-06-24 00:11:43.000 2012-06-24 00:11:43.000
Riley Szirom 2012-06-24 00:12:45.000 NULL 2012-06-24 00:13:16.000 NULL NULL NULL
Chris Nicolaides 2012-06-24 00:10:43.000 2012-06-24 00:12:08.000 NULL NULL NULL NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-24 : 00:38:10

[code]
; with cte as
(
Select Name, Interval = datediff(second, 0, [Interval 1]) From Interval union all
Select Name, Interval = datediff(second, 0, [Interval 2]) From Interval union all
Select Name, Interval = datediff(second, 0, [Interval 3]) From Interval union all
Select Name, Interval = datediff(second, 0, [Interval 4]) From Interval union all
Select Name, Interval = datediff(second, 0, [Interval 5])From Interval
)
select Name,
min_time = convert(varchar(10), dateadd(second, min(Interval), 0), 108),
max_time = convert(varchar(10), dateadd(second, max(Interval), 0), 108),
stddev_time = convert(varchar(10), dateadd(second, stddev(Interval), 0), 108)
from cte
group by Name
[/code]

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-24 : 00:57:36

I get this error;

Msg 535, Level 16, State 0, Line 8
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-25 : 02:05:31

try datediff(minute, ...)

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-25 : 03:22:26

How would one add average time?

avg_time = convert(varchar(10), dateadd(second, avg(Interval), 0), 108)

Msg 8115, Level 16, State 2, Line 11
Arithmetic overflow error converting expression to data type int.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-25 : 03:26:23

are you working on the time only or do you need to take the date into consideration ?

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-25 : 03:29:49

time only thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-25 : 03:53:32

[code]
;with
cte as
(
Select [Name], Interval = [Interval 1] From Interval union all
Select [Name], Interval = [Interval 2] From Interval union all
Select [Name], Interval = [Interval 3] From Interval union all
Select [Name], Interval = [Interval 4] From Interval union all
Select [Name], Interval = [Interval 5] From Interval
),
cte2 as
(
select [Name], Interval, min_time = min(Interval) over (partition by [Name])
from cte
)
select [Name],
min_time = min(Interval),
max_time = max(Interval),
avg_time = dateadd(minute, avg(datediff(minute, min_time, Interval)), min_time),
stddev_time = dateadd(minute, stdev(datediff(minute, min_time, Interval)), min_time)
from cte2
group by [Name], min_time
[/code]

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-25 : 21:45:16

There seems to be an error for avg_time. You can see the time for Steve is 00:07:00.000 and it shoudl be 00:07:30.000

Name Interval 1 Interval 2 Interval 3 Interval 4
Steve 00:10:00.000 00:05:00.000 00:10:00.000 00:05:00.000
Alix 00:07:26.000 00:07:38.000 NULL 00:07:59.000
Heather NULL 00:08:00.000 NULL NULL
Corey NULL 00:20:00.000 00:10:00.000 NULL

Name min_time max_time avg_time stddev_time
Steve 00:05:00.000 00:10:00.000 00:07:00.000 00:07:00.000
Alix 00:07:26.000 00:07:59.000 00:07:26.000 00:07:26.000
Heather 00:08:00.000 00:08:00.000 00:08:00.000 NULL
Corey 00:10:00.000 00:20:00.000 00:15:00.000 00:17:00.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-25 : 21:56:43

maybe this ?


avg_time	= dateadd(minute, avg(datediff(minute, min_time, Interval)), dateadd(day, datediff(day, 0, min_time), 0))

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-25 : 22:14:04

That gives 1900-01-01 00:02:00.000.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-25 : 22:43:20

Does your data contains date and do you need to consider date at all ?

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-25 : 22:57:09

The input data is as follows; I could work with or without date but since these are race times date is not needed.

Name Interval 1 Interval 2 Interval 3 Interval 4
Steve 00:10:00.000 00:05:00.000 00:10:00.000 00:05:00.000
Alix 00:07:26.000 00:07:38.000 NULL 00:07:59.000
Heather NULL 00:08:00.000 NULL NULL
Corey NULL 00:20:00.000 00:10:00.000 NULL
Justin 06:31:00.100 06:31:00.000 08:10:06.000 10:11:04.500

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-25 : 23:25:16

try

;with
cte as
(
	Select [Name], Interval = dateadd(day, datediff(day, 0, [Interval 1]), 0) From Interval union all
	Select [Name], Interval = dateadd(day, datediff(day, 0, [Interval 2]), 0) From Interval union all
	Select [Name], Interval = dateadd(day, datediff(day, 0, [Interval 3]), 0) From Interval union all
	Select [Name], Interval = dateadd(day, datediff(day, 0, [Interval 4]), 0) From Interval union all
	Select [Name], Interval = dateadd(day, datediff(day, 0, [Interval 5]), 0) From Interval
),
,
cte2 as
(
	select	[Name], Interval, min_time = min(Interval) over (partition by [Name])
	from	cte
)
select	[Name], 
	min_time	= min(Interval),
	max_time 	= max(Interval),
	avg_time	= dateadd(minute, avg(datediff(minute, 0, Interval)), 0, 0)),
	stdev_time	= dateadd(minute, stdev(datediff(minute, min_time, Interval)), min_time)
from	cte2
group by [Name], min_time

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-26 : 00:29:07

Script above gives;

Msg 174, Level 15, State 1, Line 64
The dateadd function requires 3 argument(s).

If I comment out the avg_time and, stdev_time I get;

Name min_time max_time
Alix 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
Corey 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
Heather 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
Justin 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
Steve 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-26 : 04:10:41

sorry, extra ")" there


avg_time	= dateadd(minute, avg(datediff(minute, 0, Interval)), 0, 0),

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-26 : 19:36:27

select [Name],
min_time = min(Interval),
max_time = max(Interval),
avg_time = dateadd(minute, avg(datediff(minute, 0, Interval)), 0, 0)
--stdev_time = dateadd(minute, stdev(datediff(minute, min_time, Interval)), min_time)
from cte2

Msg 174, Level 15, State 1, Line 64
The dateadd function requires 3 argument(s).

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-26 : 19:39:37

select [Name],
min_time = min(Interval),
max_time = max(Interval),
avg_time = dateadd(minute, avg(datediff(minute, 0, Interval)), 0)
--stdev_time = dateadd(minute, stdev(datediff(minute, min_time, Interval)), min_time)
from cte2

Input Data;

Name Interval 1 Interval 2 Interval 3 Interval 4
Steve 00:10:00.000 00:05:00.000 00:10:00.000 00:05:00.000
Alix 00:07:26.000 00:07:38.000 NULL 00:07:59.000
Heather NULL 00:08:00.000 NULL NULL
Corey NULL 00:20:00.000 00:10:00.000 NULL
Justin 06:31:00.100 06:31:00.000 08:10:06.000 10:11:04.500

Result;

Name min_time max_time avg_time
Alix 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
Corey 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
Heather 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
Justin 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
Steve 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-06-27 : 19:22:35

Any other ideas, I still don't have a working solution for this problem?

Interval datatype = time(3)
Input Data;

Name Interval 1 Interval 2 Interval 3 Interval 4
Steve 00:10:00.000 00:05:00.000 00:10:00.000 00:05:00.000
Alix 00:07:26.000 00:07:38.000 NULL 00:07:59.000
Heather NULL 00:08:00.000 NULL NULL
Corey NULL 00:20:00.000 00:10:00.000 NULL
Justin 06:31:00.100 06:31:00.000 08:10:06.000 10:11:04.500

For each person I need min time, max time, average time and standard deviation time. Note some races may have NULL values as the athlete did not compete in this race. Also for the same person they may do races with the same time.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-07-03 : 21:31:53

Any other ideas, I still don't have a working solution for this problem?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-03 : 23:46:48

Technically you're storing a duration (race time), not an actual time value, so neither datetime nor time is really optimal. Leaving it as is for now, the following should work:

;WITH CTE AS(SELECT Name,DATEDIFF(s,0,CAST(TIME AS TIME)) Duration FROM
(SELECT * FROM INTERVAL) a
UNPIVOT(TIME FOR INTERVAL IN(Interval1,Interval2,Interval3,Interval4,Interval5,Interval6)) B)
SELECT Name, AVG(Duration) AvgDuration, MAX(Duration) MaxDuration, MIN(Duration) MinDuration,
STDEV(Duration) StdDevDuration 
FROM CTE GROUP BY Name

I converted everything to time data type and then took the DATEDIFF in seconds, this avoids overflow and allows AVG and STDEV to be calculated. You can optionally do a DATEADD(second,#,0) to convert them back to datetime values but it may not make much sense (STDEV especially).

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources