November 14, 2011 at 3:41 am
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
November 14, 2011 at 3:50 am
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
November 14, 2011 at 4:02 am
carl.meads (11/14/2011)
I have the followingSELECT #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)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy