ISERROR Function

• carl.meads

  Mr or Mrs. 500

  Points: 519

  More actions

  November 14, 2011 at 3:41 am

  #246056

  I have the following

  SELECT #callhandling2.client

  ,#callhandling2.daycat+' '+#callhandling2.timecat as [Category]

  ,Sum(#callhandling2.calls) as [Total Calls]

  ,[Calls Per 30 Mins] = Case

  When #callhandling2.daycat+' '+#callhandling2.timecat='Weekend 20:00-08:00' then if(iserror(Sum(#callhandling2.calls)/2208.0)),0,Sum(#callhandling2.calls)/2208.0

  When #callhandling2.daycat+' '+#callhandling2.timecat='Weekend 08:00-20:00' then Sum(#callhandling2.calls)/2208.0

  When #callhandling2.daycat+' '+#callhandling2.timecat='Weekday 20:00-08:00' then Sum(#callhandling2.calls)/5304.0

  When #callhandling2.daycat+' '+#callhandling2.timecat='Weekday 08:00-20:00' then Sum(#callhandling2.calls)/5304.0

  Else 'I dont know'

  End

  But have a problem with the if(iserror) bit of my case statement.

  Basically I want to say if there is a division by 0 error then NA or something similar

• Devendrakumar

  SSC-Forever

  Points: 42493

  More actions

  November 14, 2011 at 3:50 am

  #1407033

  Basically I want to say if there is a division by 0 error then NA or something similar

  Check it before you Divide By Zero (0).

  Something like this:

  CASE

  WHEN B = 0 THEN 'NA'

  ELSE A / B

  END

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  November 14, 2011 at 4:02 am

  #1407040

  carl.meads (11/14/2011)

  ---

  I have the following

  SELECT #callhandling2.client

  ,#callhandling2.daycat+' '+#callhandling2.timecat as [Category]

  ,Sum(#callhandling2.calls) as [Total Calls]

  ,[Calls Per 30 Mins] = Case

  When #callhandling2.daycat+' '+#callhandling2.timecat='Weekend 20:00-08:00' then if(iserror(Sum(#callhandling2.calls)/2208.0)),0,Sum(#callhandling2.calls)/2208.0

  When #callhandling2.daycat+' '+#callhandling2.timecat='Weekend 08:00-20:00' then Sum(#callhandling2.calls)/2208.0

  When #callhandling2.daycat+' '+#callhandling2.timecat='Weekday 20:00-08:00' then Sum(#callhandling2.calls)/5304.0

  When #callhandling2.daycat+' '+#callhandling2.timecat='Weekday 08:00-20:00' then Sum(#callhandling2.calls)/5304.0

  Else 'I dont know'

  End

  But have a problem with the if(iserror) bit of my case statement.

  Basically I want to say if there is a division by 0 error then NA or something similar

  This looks like an Access query rather than a SQL query, is it?

  Try something like this: -

  SELECT #callhandling2.client

  ,#callhandling2.daycat + ' ' + #callhandling2.timecat AS [Category]

  ,SUM(#callhandling2.calls) AS [Total Calls]

  ,[Calls Per 30 Mins] = (CASE WHEN #callhandling2.calls = 0

  THEN 'N/A'

  ELSE (CASE WHEN #callhandling2.daycat + ' ' + #callhandling2.timecat = 'Weekend 20:00-08:00'

  THEN #callhandling2.calls / 2208.0

  WHEN #callhandling2.daycat + ' ' + #callhandling2.timecat = 'Weekend 08:00-20:00'

  THEN #callhandling2.calls / 2208.0

  WHEN #callhandling2.daycat + ' ' + #callhandling2.timecat = 'Weekday 20:00-08:00'

  THEN #callhandling2.calls / 5304.0

  WHEN #callhandling2.daycat + ' ' + #callhandling2.timecat = 'Weekday 08:00-20:00'

  THEN #callhandling2.calls / 5304.0

  ELSE 'I dont know' END )

  END)

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---