Divide by zero

• Matt W

  SSC Journeyman

  Points: 97

  More actions

  May 11, 2006 at 9:40 am

  #190582

  I am trying to use the following SQL in a report:

   

  --set @StartDate='1 apr 2008'

  --set @EndDate='1 apr 2010'

  declare @StartDate varchar(50)

  declare @EndDate varchar(50)

  declare @a float

  declare @b-2 float

  declare @c float

  select  @a = count(*)

  from  tbEnquiry

  where (

   JobCompletedAP between @StartDate and @EndDate

   or

   JobCompletedAR between @StartDate and @EndDate

   or

   JobCompletedBLs between @StartDate and @EndDate

   or

   JobCompletedMA between @StartDate and @EndDate

   or

   JobCompletedSU between @StartDate and @EndDate

  &nbsp

  select  @b = count(*)

  from  tbEnquiry

  where (

   JobCompletedAP between @StartDate and @EndDate

   or

   JobCompletedAR between @StartDate and @EndDate

   or

   JobCompletedBLs between @StartDate and @EndDate

   or

   JobCompletedMA between @StartDate and @EndDate

   or

   JobCompletedSU between @StartDate and @EndDate

  &nbsp

   and

   (

   (JobCompletedAP between @StartDate and @EndDate and JobCompletedAP<=Deadline)

   or

   (JobCompletedAR between @StartDate and @EndDate and JobCompletedAR<=Deadline)

   or

   (JobCompletedBLs between @StartDate and @EndDate and JobCompletedBLs<=Deadline)

   or

   (JobCompletedMA between @StartDate and @EndDate and JobCompletedMA<=Deadline)

   or

   (JobCompletedSU between @StartDate and @EndDate and JobCompletedSU<=Deadline)

  &nbsp

  if (@a>0 and @b-2>0)

  set @c = (@b/@a)*100                                                                                                                                                                                                              

  else

  set @c = 0

  set @c=case @b-2 when 0 then 0 else convert(int,(@b/@a)*100) end

                                                      

  select '(a)' 'Report', convert(int,@a) 'Detail'

  union

  select '(b)', convert(int,@b)

  union

  select '(c)', convert(int,@c)

   

  Although i'm testing for a zero (and i've tried with IF @b-2=0, etc.) as well it won't let me use the SQL in Reporting Services as it must pre-parse the query.

  Anyone come across this issue and worked out how to get it stop complaining of the 'divide by zero'.

  The query runs fine in Query Analyzer.

   

  Cheers, Matt.

   

• Matt W

  SSC Journeyman

  Points: 97

  More actions

  May 11, 2006 at 9:44 am

  #637309

   

  Always the way.  You look at something for hours.

   

  Soon as you post the question you notice that it's just you being silly.

  *Stands in the corner*