Divide By Zero Error - 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

General SQL Server Forums

Script Library

Divide By Zero Error

Author

Topic

Splics
Starting Member

39 Posts

Posted - 2006-02-28 : 16:25:41

I am writing a report that deals with time in seconds. I am trying to figure out the average handle time for each call.
To Get this number i use 5 fields. the formula is

(((cs_acd_trk_t + cs_acd_ext_c)/(cs_acd_trk_c + cs_acd_ext_c)) + ((cs_t_acd_wrk)/(cs_acd_trk_c + cs_acd_ext_c))/(cs_acd_trk_c + cs_acd_ext_c))

Which translates to ((Incoming Handle Time) + (Work))/(Number of Incoming Calls)

When it does the calculations some times the cs_acd_trk_c + cs_acd_ext_c (number of incoming calls) is zero.

I dont know how to get around this ... i tried to set that value to <>0 in my query but it threw my other numbers off for number of calls which is a different value on the report.
Any Help on this would be super appreciated.
Thanks,
Mitch

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-28 : 17:16:33

what I typically do is test for the 0 in a case statement:


declare @a int
	,@b int
select	@a = 2
	,@b = null

select	case 
	when @a+@b is null = 0 then 0
	else 10 / (@a + @b)
	end as [noDevideByZeroError]

btw, you should use the t-sql forum or some other forum. Script Library is just for posting cool/helpfull code.
thanks

Be One with the Optimizer
TG

swatib
Posting Yak Master

173 Posts

Posted - 2006-03-04 : 02:45:08

Are you using crystal report for this?

Just add a code like :

NumberVar AvgHandleTime:=0;
If (Number of Incoming Calls) = 0 then
AvgHandleTime:=0
Else
AvgHandleTime:=((Incoming Handle Time) + (Work))/(Number of Incoming Calls)

Njoy Life

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources