CODEWITHSUNDEEP
sql-servererror-handlingoverflowaggregate-functions
wlfente
wlfente

Reputation: 153

SQL Throwing an Arithmetic Overflow Error

I'm getting an "Arithmetic overflow error converting expression to data type int" error when running a simple aggregate function (see code below). As I throttle back the date range, I can see that the results is in fact exceeding the INT data type limit...I've tried casting the whole expression into a BIGINT w/o success. Anyone know how I can resolve this issue?

All 3 of these queries throw the same above mentioned error:

Select  (SUM(ACDTalkTimeInSec) + SUM(TotAcwTimeInSec) + SUM(HoldTimeInSec)) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

Select  CAST(Cast(SUM(ACDTalkTimeInSec) AS BIGINT) + CAST(SUM(TotAcwTimeInSec) AS BIGINT) + CAST(SUM(HoldTimeInSec) AS BIGINT) AS BIGINT) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

Select  Cast(((SUM(ACDTalkTimeInSec) + SUM(TotAcwTimeInSec) + SUM(HoldTimeInSec))) AS BIGINT) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

Upvotes: 3

Views: 154

Answers (1)

Michael Bruesch
Michael Bruesch

Reputation: 660

SMor answered it in the comments, but here is the sql:

Select SUM(CAST(ACDTalkTimeInSec AS BIGINT)) + SUM(CAST(TotAcwTimeInSec AS BIGINT)) 
    + SUM(CAST(HoldTimeInSec AS BIGINT)) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

If SMor posts their answer then accept that. I did not test the code so pardon any typos.

Upvotes: 2

Related Questions