CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SqlLearner
SqlLearner

Reputation: 793

How to sum time datatype in sql server

I have query which gives sum of total talk time for an Agent all the years

SELECT 
CONVERT(TIME, DATEADD(s, SUM(( DATEPART(hh, TalkTime) * 3600 ) + ( DATEPART(mi, TalkTime) * 60 ) + DATEPART(ss, TalkTime)), 0))
  FROM [CRMIntegration].[dbo].[Five9CallLog]
  where AGENT_NAME = 'XYZ'

which is giving me '00:37:01.0000000'

but when i sum up total talk time for this year till now using the below query

SELECT 
CONVERT(TIME, DATEADD(s, SUM(( DATEPART(hh, TalkTime) * 3600 ) + ( DATEPART(mi, TalkTime) * 60 ) + DATEPART(ss, TalkTime)), 0))
  FROM [CRMIntegration].[dbo].[Five9CallLog]
  where AGENT_NAME = 'XYZ'
  and DateOfcall between 
  DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) and dateadd(day,datediff(day,1,GETDATE()),0)

result of the query is '23:34:38.0000000',but this should be always less than or equal to the first result

Is the function i am using for calculating total talk time is correct?

Upvotes: 0

Views: 183

Answers (1)

Hart CO
Hart CO

Reputation: 34774

I suspect your first result is greater than 1 day, but that is being truncated by using TIME. Instead of adding seconds to 0 and converting to TIME, you could use DATEDIFF() to get total minutes:

SELECT DATEDIFF(MINUTE,0,TalkTime)

Ideally you'd have a start/end time to use instead of 0, because you'll run into an overflow if you need to get too precise and try starting from 0. If it's always relative to the start of the day, you can use:

SELECT DATEDIFF(SECOND,CAST(TalkTime AS DATE),TalkTime)

Likewise, you could simply remove the TIME conversion from what you already have.

Upvotes: 1

Related Questions