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 on time

Author  Topic 

tyekhan786
Starting Member

9 Posts

Posted - 2014-07-12 : 09:36:34
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

22864 Posts

Posted - 2014-07-15 : 08:56:06
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

9 Posts

Posted - 2014-07-16 : 03:37:48
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

22864 Posts

Posted - 2014-07-16 : 06:52:56
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

9 Posts

Posted - 2014-07-16 : 09:03:21
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
   

- Advertisement -