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