avg on time - SQL Server Forums

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources