Avg and Max between dates in HH:MM

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

Avg and Max between dates in HH:MM

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-18 : 07:53:33

I am still struggling with calculating the time between dates. For the last week I keep returning to my problem as I was hoping to solve without help from the forums but I am to the point of giving up.

My problem is I need to read a table that contains a StartDate and an EndDate. All I am trying to do is read the table and calculate 3 items. A count, which I know but I also need to calculate the maximum time of the dates read in HH:MM as well as the average time between start and end in HH:MM.

My sample data is:

Table - SvcReq
StartDate, EndDate
9/2/09 12:42, 9/2/09 17:00
9/2/09 14:57, 9/2/09 15:00
9/2/09 15:02, 9/2/09 16:05
9/2/09 16:18, 9/3/09 18:28

I manually calculated what the results should be.
Max time in HH:MM = 26:10
Avg time in HH:MM = 07:54 (If I calculated correctly...)

My assumption is the same technique can be used for both Max and Average but a solution eludes me. All help is greatly appreciated.

John

"The smoke monster is just the Others doing barbecue"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-18 : 08:33:36

DECLARE @Table TABLE (Start datetime, Stop datetime)

INSERT INTO @Table
SELECT '9/2/09 12:42', '9/2/09 17:00' UNION ALL
SELECT '9/2/09 14:57', '9/2/09 15:00' UNION ALL
SELECT '9/2/09 15:02', '9/2/09 16:05' UNION ALL
SELECT '9/2/09 16:18', '9/3/09 18:28'

select [Avg] = convert(varchar(10),avg(datediff(n,start,stop))/60)
+':'+ convert(varchar(10),avg(datediff(n,start,stop))%60)
,[MaxDiff] = convert(varchar(10),max(datediff(n,start,stop)) /60) +':'
+ convert(varchar(10), max(datediff(n,start,stop)) %60)
from @table

Jim

Everyday I learn something that somebody else already knew

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-18 : 08:53:31

Thank you! That is exactly what I needed. I have not had an opportunity to use Remainders with date/time and it never crossed my mind. I am so glad I stmbled on to this fantastic collection of expertise and knowledge.

John

"The smoke monster is just the Others doing barbecue"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-18 : 08:56:56

You're welcome. And I agree about this site. My signature line could just as well read "Every day I learn something that someone on SQL Team already knew".

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources