Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Divide By Zero Error
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

39 Posts

Posted - 02/28/2006 :  16:25:41  Show Profile  Reply with Quote
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.

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 02/28/2006 :  17:16:33  Show Profile  Reply with Quote
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.

Be One with the Optimizer

Edited by - TG on 02/28/2006 17:30:48
Go to Top of Page

Posting Yak Master

173 Posts

Posted - 03/04/2006 :  02:45:08  Show Profile  Reply with Quote
Are you using crystal report for this?

Just add a code like :

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

Njoy Life
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000