ISERROR Function

  • 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

  • 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

  • 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/

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply