SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 avg on time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tyekhan786
Starting Member

United Kingdom
9 Posts

Posted - 07/12/2014 :  09:36:34  Show Profile  Reply with Quote
I need to know how to get a query that gets the avg in mm:ss

Im using the below

AVG(CAST(H.TalkTime / 3600.0 AS DECIMAL(8, 2))) AS AvgTalk

but i want it to only be AvgTalk on mobile.

SELECT cmp.call, AVG(CAST(History.TalkTime / 3600.0 AS DECIMAL(8, 2))) AS AvgTalk
FROM cmp INNER JOIN
Dial ON cmp.DialID = Dial.DialID INNER JOIN
History ON Dial.HistoryID = History.HistoryID INNER JOIN
CRC ON Dial.CRC = CRC.CRC
WHERE (D.LastCalled > CONVERT(DATETIME, '2014-07-12 08:00:00', 102)) AND (t.type = 1)
GROUP BY cmp.call
ORDER BY AvgTalk DESC

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/15/2014 :  08:56:06  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try this

SELECT cmp.call, CONVERT(CHAR(8),DATEADD(second,AVG(CAST(History.TalkTime / 3600.0 AS DECIMAL(8, 2))),0),108) AS AvgTalk
FROM cmp INNER JOIN
Dial ON cmp.DialID = Dial.DialID INNER JOIN
History ON Dial.HistoryID = History.HistoryID INNER JOIN
CRC ON Dial.CRC = CRC.CRC
WHERE (D.LastCalled > CONVERT(DATETIME, '2014-07-12 08:00:00', 102)) AND (t.type = 1)
GROUP BY cmp.call
ORDER BY AvgTalk DESC

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

tyekhan786
Starting Member

United Kingdom
9 Posts

Posted - 07/16/2014 :  03:37:48  Show Profile  Reply with Quote
Thanks,

but this returns all the time as,

00:00:00


quote:
Originally posted by madhivanan

Try this

SELECT cmp.call, CONVERT(CHAR(8),DATEADD(second,AVG(CAST(History.TalkTime / 3600.0 AS DECIMAL(8, 2))),0),108) AS AvgTalk
FROM cmp INNER JOIN
Dial ON cmp.DialID = Dial.DialID INNER JOIN
History ON Dial.HistoryID = History.HistoryID INNER JOIN
CRC ON Dial.CRC = CRC.CRC
WHERE (D.LastCalled > CONVERT(DATETIME, '2014-07-12 08:00:00', 102)) AND (t.type = 1)
GROUP BY cmp.call
ORDER BY AvgTalk DESC

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/16/2014 :  06:52:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Does it mean History.TalkTime is 0 for all rows?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

tyekhan786
Starting Member

United Kingdom
9 Posts

Posted - 07/16/2014 :  09:03:21  Show Profile  Reply with Quote
Yes on the query its returning 0 but they should have figures in them

quote:
Originally posted by madhivanan

Does it mean History.TalkTime is 0 for all rows?

Madhivanan

Failing to plan is Planning to fail

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000