average between datetime field

[le1234]

currently trying to extract the time difference bewteen two date time fields and then work out the average on the results. I can pull out the hours and mins between the two datetime fields using convert however if I try and run an average I get an error as its not a number value field. can anyone advise with the next step I am missing? thanks

query
SELECT TableA.[Reference],
TableA.[Open Date & Time],
TableA.[Close Date & Time],
CAST(FLOOR(DATEDIFF(mi,[Open Date & Time],[Close Date & Time]) / 60) AS varchar(2)) + ':' + CAST(FLOOR((DATEDIFF(mi,[Open Date & Time],[Close Date & Time]) / 60 - FLOOR(DATEDIFF(mi,[Open Date & Time],[Close Date & Time]) / 60)) * 60) AS varchar(2)) AS castHourMin,
CONVERT(varchar(10),DATEDIFF(mi,[Open Date & Time],[Close Date & Time])/60) + ':' + RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,[Open Date & Time],[Close Date & Time])%60),2) as convertOpenHoursMins,
CAST(FLOOR(DATEDIFF(mi,[Open Date & Time],[Close Date & Time]) / 60) AS varchar(2)) as casthour,
CAST(FLOOR((DATEDIFF(mi,[Open Date & Time],[Close Date & Time]) / 60 - FLOOR(DATEDIFF(mi,[Open Date & Time],[Close Date & Time]) / 60)) * 60) AS varchar(2)) AS castMin,
CONVERT(varchar(10),DATEDIFF(mi,[Open Date & Time],[Close Date & Time])/60) as converthour,
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,[Open Date & Time],[Close Date & Time])%60),2) as convertMins
FROM TableA
WHERE (([Open Date & Time] > '08/01/2008') AND ([Open Date & Time] - 1 < '08/31/2008')) AND TableA.[Status ID:] = 'CLOSED'
ORDER BY TableA.[Reference]desc

results set
Ref Open Date & Time Close Date & Time castHourMin convertOpenHoursMins casthour castmin converthour convermins
101 09/08/2011 16:51 10/08/2011 15:35 22:00 22:44 22 0 22 44
102 09/08/2011 16:39 09/08/2011 16:58 00:00 00:19 0 0 0 19
103 09/08/2011 16:37 10/08/2011 11:50 19:00 19:13 19 0 19 13
104 09/08/2011 15:37 11/08/2011 15:40 48:00:00 48:03:00 48 0 48 3
105 09/08/2011 15:13 09/08/2011 15:40 00:00 00:27 0 0 0 27
106 09/08/2011 15:11 10/08/2011 14:35 23:00 23:24 23 0 23 24
107 09/08/2011 14:36 09/08/2011 15:11 00:00 00:35 0 0 0 35
108 09/08/2011 13:55 11/08/2011 17:22 51:00:00 51:27:00 51 0 51 27
109 09/08/2011 12:05 09/08/2011 12:50 00:00 00:45 0 0 0 45
110 09/08/2011 11:58 12/08/2011 08:58 69:00:00 69:00:00 69 0 69 0
111 09/08/2011 11:27 09/08/2011 11:29 00:00 00:02 0 0 0 2
112 09/08/2011 10:50 09/08/2011 14:45 03:00 03:55 3 0 3 55
113 09/08/2011 10:43 09/08/2011 10:45 00:00 00:02 0 0 0 2
114 09/08/2011 10:43 09/08/2011 16:06 05:00 05:23 5 0 5 23
115 09/08/2011 10:38 09/08/2011 10:38 00:00 00:00 0 0 0 0
116 09/08/2011 10:33 09/08/2011 11:36 01:00 01:03 1 0 1 3

 

[SQLScholar]

What you need to do is find the total time in minutes first.

Then average it, then do whatever your doing with it to make it look nice (more like a time!). I dont know if it helps but this is how i make seconds look nice.

CASE WHEN LEN(RTRIM(CONVERT(CHAR(10), SUM(DurationSeconds) / ( 60
* 60 )))) = 1 THEN '0'
ELSE ''
END + RTRIM(CONVERT(CHAR(10), SUM(DurationSeconds) / ( 60 * 60 )))
+ ':' + RIGHT('0' + RTRIM(CONVERT(CHAR(10), ( SUM(DurationSeconds)
/ 60 ) % 60)), 2) + ':' + RIGHT('0'
+ RTRIM(CONVERT(CHAR(10), SUM(DurationSeconds)
% 60)), 2) AS DurationTime

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[simian336]

What are you trying to take the average of?

Simi

 

[gmmastros]

Averaging DateTime values can be a little weird. To accomplish this, I would suggest that you calculate the difference in your datetime values to the minute, then perform your average, and finally convert it back to date time.

Ex:

Declare @Temp Table(Ref Int, OpenDateTime DateTime, CloseDateTime DateTime)
Set DateFormat DMY

Insert Into @Temp Values(101,'09/08/2011 16:51','10/08/2011 15:35')
Insert Into @Temp Values(102,'09/08/2011 16:39','09/08/2011 16:58')
Insert Into @Temp Values(103,'09/08/2011 16:37','10/08/2011 11:50')
Insert Into @Temp Values(104,'09/08/2011 15:37','11/08/2011 15:40')
Insert Into @Temp Values(105,'09/08/2011 15:13','09/08/2011 15:40')
Insert Into @Temp Values(106,'09/08/2011 15:11','10/08/2011 14:35')
Insert Into @Temp Values(107,'09/08/2011 14:36','09/08/2011 15:11')
Insert Into @Temp Values(108,'09/08/2011 13:55','11/08/2011 17:22')
Insert Into @Temp Values(109,'09/08/2011 12:05','09/08/2011 12:50')
Insert Into @Temp Values(110,'09/08/2011 11:58','12/08/2011 08:58')
Insert Into @Temp Values(111,'09/08/2011 11:27','09/08/2011 11:29')
Insert Into @Temp Values(112,'09/08/2011 10:50','09/08/2011 14:45')
Insert Into @Temp Values(113,'09/08/2011 10:43','09/08/2011 10:45')
Insert Into @Temp Values(114,'09/08/2011 10:43','09/08/2011 16:06')
Insert Into @Temp Values(115,'09/08/2011 10:38','09/08/2011 10:38')
Insert Into @Temp Values(116,'09/08/2011 10:33','09/08/2011 11:36')

-- Query to get difference in minutes.
Select DateDiff(Minute, OpenDateTime, CloseDateTime)
From   @Temp

-- Query to get average minute difference.
Select Avg(DateDiff(Minute, OpenDateTime, CloseDateTime))
From   @Temp

-- Query to convert this back to DateTime
Select DateAdd(Minute, Avg(DateDiff(Minute, OpenDateTime, CloseDateTime)), 0)
From   @Temp

Note that the last query returns: 1900-01-01 15:23:00.000 Notice the Date part of the response. Jan 1, 1900 doesn't really have a meaning in this context, but rest assured that the time part is correct. If the average difference in time had been more than 24 hours, the date part would have changed. For example, if the average difference had been 1 day and 3 hours, the output would have been '1900-01-02 03:00:00.000'.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Wow. I'm really slow today!

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[le1234]

thanks for the responses!
SQL Scholar - I like the breakdown to seconds; this looks much better.
simian336 - I want to be able to work out the Open Hours and mins between the 2 datetime fields for each record, which I can get but then work out the average open time for the whole dataset returned.
gmmastros - i will give this method a try....