Arithmetic overflow error converting expression to data type int

Transact-SQL
1

Hi, I'm hoping someone here can help me. I'm running into an issue when attempting to divide 2 values to make a percentage, I am converting the value into a decimal to make it work which it does most of the time, but on some days I get the error
"Arithmetic overflow error converting expression to data type int."

FORMAT((Coalesce(Sum(Case When StatusKey In ('on vacation','gone home','out of the office', 'acdagentnotanswering', 'invalid status') Then Convert(Decimal(7,2),StateDuration) End)/NULLIF(Sum(Case When StatusKey is not null Then Convert(Decimal(7,2),Stateduration) End),0),0)),'P') as [Inactive%]

If I remove the decimal conversion then I return 0% for every row.

FORMAT((Coalesce(Sum(Case When StatusKey In ('on vacation','gone home','out of the office', 'acdagentnotanswering', 'invalid status') Then StateDuration End)/NULLIF(Sum(Case When StatusKey is not null Then Stateduration End),0),0)),'P') as [Inactive%]

How can I return the correct percentage without getting the Arithmetic overflow error?

An example output of the table is below. Stateduration is an INT

Capture
Capture1581×350 100 KB

Thanks in advance

David

2

Ok, I've isolated it down to this piece of the code. The first line works but the second fails. What could be causing this or can it be coded in a different way?

Sum(Case When StatusKey is not null Then StateDuration End)  as "Total",

Convert(VarChar,DateAdd(MS,Sum(Case When StatusKey is not null Then StateDuration End) * 1000,0),108) as "Total 2",
3

Please peovide sample data not as an image but real ddl and dml. Cause we dont have access to your sql server. We cant select * from image?

4

It's because StateDuration is an int

try converting to decimal

Convert(VarChar,DateAdd(MS,Sum(Case When StatusKey is not null Then cast(StateDuration as decimal(12,2)) End) * 1000.00,0),108) as "Total 2",

5

The result of the SUM is used in DATEADD - whether it is decimal or integer shouldn't cause an issue. There are 2 problems with that line of code though:

  1. Converting to VARCHAR - you should always specify the length for (n)char/(n)varchar strings.
  2. Adding MS to the 0 date will exceed the limits for DATEADD - causing the error
  3. If StatusKey is NULL - then a NULL will be returned and attempting to add NULL days in DATEADD will also cause an error.

What is the expected results from that line and why is it being converted to a string? I see other issues with just the little code provided - a lot of 'formatting' of data for presentation which really should not be done in SQL.

6

Thanks, Mike, I believe I've sorted that issue now, thanks for your input.

The only issue I now have left is when the total time exceeds 24 hours it doesn't hold the hour and just resets, this is ok if I'm only looking at days worth of data, but when I'm looking at months worth it's not correct.

Do you know how to hold the hour?

CONVERT(varchar, DATEADD(ms, ( Sum(Case When StatusKey In ('on call', 'campaign call', 'manual dial', 'on call manual') Then stateduration end) % 86400 ) * 1000, 0), 108) as "Connected",

Thanks

7

you're converting it to time (108), so it will never exceed 23:59:59. what are you expecting when you run this? You are summing the duration, so I don't know what converting it to a time will get for you

8

Hi Mike, I want to Sum the duration to give me the total about of time that one of my agents were in a specific StatusKey. I want it to display in the total about of Hours, Minutes and seconds do a supervisor can see how long the agents have been in that status over the course of a month.

9

But a time only has 24 hours - it will never be more than 24 hours.

For a duration, you should calculate the value in the smallest increment you want to display. If you want to display HHH:MM:SS then calculate the difference in seconds and return the number of seconds of the duration.

In the report you then convert that to hours, minutes and seconds using a combination of divide and modulo as needed.

10

That convert won't work. This should work, but depends on performance and formatting, which you can tweak

declare @i int = 87400 -- seconds
-- 86400
--434

select cast(@i/3600 as varchar(6)) + ':' + 
	   case when @i >= 3600 then
				cast(@i % 3600 /60 as varchar(2))
			else cast(@i/60 as varchar(6))
		end + ':' + 
	   case when @i >= 3600 then
				cast(((@i % 3600) % 60) /60 as varchar(2))
			when @i >= 60 then
				cast((@i%60) as varchar(6))
			else cast(@i as varchar(6))
		end
1 Like
11

This can be done much simpler:

Declare @timeInSeconds int = (25 * 60 * 60)     -- 25 hours
                           + (38 * 60)          -- 38 minutes
                           + 24;                -- 24 seconds

 Select @timeInSeconds / 3600           -- hours
      , (@timeInSeconds % 3600) / 60    -- minutes
      , (@timeInSeconds % 3600) % 60    -- seconds
      , FormattedTime = concat(@timeInSeconds / 3600, ':', (@timeInSeconds % 3600) / 60, ':', (@timeInSeconds % 3600) % 60);

Now - if you always want 2 positions for minutes and seconds then you need to modify it as:

Declare @timeInSeconds int = (25 * 60 * 60)    -- 25 hours
                           + (0 * 60)          -- 0 minutes
                           + 0;                -- 0 seconds

 Select @timeInSeconds / 3600           -- hours
      , (@timeInSeconds % 3600) / 60    -- minutes
      , (@timeInSeconds % 3600) % 60    -- seconds
      , FormattedTime = concat(@timeInSeconds / 3600, ':'
                             , right(concat('0', (@timeInSeconds % 3600) / 60), 2), ':'
                             , right(concat('0', (@timeInSeconds % 3600) % 60), 2));

This eliminates the need for CONVERT, but does not handle null seconds. The CASE expression will return a null value if there are no rows with any of the specified StatusKey values. If that can ever be true - then modify the case expression.

Because you need to use the seconds multiple times - you really don't want to repeat that code. What I would do here is pre-aggregate the values in a CTE - and then calculate the final totals/results instead of trying to do that all in a single query. For example:

  With totals
    As (
Select ...
     , InactiveDuration = sum(Case When StatusKey In (('on vacation','gone home','out of the office', 'acdagentnotanswering', 'invalid status') Then StateDuration Else 0 End)
     , TimeInSeconds = sum(Case When StatusKey Is Not Null Then StateDuration Else 0 End)
     , ...
 Where ...
 Group By
       ...
       )
Select ...
     , [Inactive%] = concat(cast(t.InactiveDuration * 1.0 / t.TimeInSeconds As decimal(3,2)), '%')
     , FormattedTime = concat(t.TimeInSeconds / 3600, ':'
                       , right(concat('0', (t.TimeInSeconds % 3600) / 60), 2), ':'
                       , right(concat('0', (@timeInSeconds % 3600) % 60), 2));
     , ...
  From totals      t

This will make it much easier to read - and much easier to calculate the values.

1 Like
12

That's great, thanks to Jeff, and to everyone else who contributed.