May 19, 2008 at 2:48 pm
Hi All,
I have a Select Statment, which as far as my knowledge goes with SQL should work, the issue seems to be the SPC expression;
ISNULL(CONVERT(DECIMAL(5,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) As SPC
The fields used in this query may contain zero's, but I thought I accounted for that by using ISNULL, but then again its not null its zero... So How do I handle to get the result I need...Ideally in DECIMAL(5,2) data type (i.e. return 0.00 or 100.00)
Full Statement;
DECLARE @Date as varchar(10)
SET @Date = CONVERT(Varchar, '16/05/2008', 103) --Change date for different dates
SELECT Agent.Firstname + ' ' + Agent.Lastname AS AgentName, Agent.AgentID, Groups.GroupName As CampaignName, CONVERT(DECIMAL(6,2),(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)*1.00)/3600) As DiallingTime,
CONVERT(DECIMAL(6,2),(SUM(TalkTime + HoldTime)*1.00)/3600) As TalkTime, CONVERT(DECIMAL(6,2),(SUM(WaitTime + PreviewTime)*1.00)/3600) As WaitTime, CONVERT(DECIMAL(6,2),(SUM(WrapTime + DeadTime)*1.00)/3600) As WrapTime,
SUM(CONVERT(NUMERIC,CRC.Success)) As Success, SUM(CONVERT(NUMERIC,CRC.Contact)) As Contacts, SUM(CONVERT(NUMERIC,CRC.FinalCRC)) As Completes, Count(History.DialID) As Connects,
ISNULL(CONVERT(DECIMAL(6,2),(SUM(CONVERT(NUMERIC,CRC.Contact))/(CONVERT(DECIMAL(6,2),(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)*1.00)/3600)))),0) As CPH,
ISNULL(CONVERT(DECIMAL(6,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(CONVERT(DECIMAL(6,2),(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)*1.00)/3600)))),0) As SPH
ISNULL(CONVERT(DECIMAL(5,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) As SPC
FROM Agent INNER JOIN
Groups INNER JOIN
GroupProject ON Groups.GroupID = GroupProject.GroupID INNER JOIN
Project ON GroupProject.ProjectID = Project.ProjectID INNER JOIN
History ON Project.ProjectID = History.ProjectID INNER JOIN
CRC ON History.CRC = CRC.CRC ON Agent.AgentID = History.AgentID
WHERE (CONVERT(Varchar, History.CallDateTime, 103) = @Date) AND (Agent.AgentID >1)
GROUP BY Agent.Firstname + ' ' + Agent.Lastname, Agent.AgentID, Groups.GroupName
May 19, 2008 at 3:54 pm
TRY PROBABLY THIS:?
CASE WHEN (SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) > 0.0 THENISNULL(CONVERT(DECIMAL(5,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) ELSE 0.0 END As SPC
May 19, 2008 at 5:03 pm
Can any of the values in the denominator be negative, or are all values greater than or equal to zero?
😎
May 19, 2008 at 5:11 pm
I was just providing you a hint.. You may want to :
CASE WHEN (SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) <> 0.0 THENISNULL(CONVERT(DECIMAL(5,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) ELSE 0.0 END As SPC
May 19, 2008 at 7:50 pm
a.thomson2 (5/19/2008)
Hi All,I have a Select Statment, which as far as my knowledge goes with SQL should work, the issue seems to be the SPC expression;
ISNULL(CONVERT(DECIMAL(5,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) As SPC
The fields used in this query may contain zero's, but I thought I accounted for that by using ISNULL, but then again its not null its zero... So How do I handle to get the result I need...Ideally in DECIMAL(5,2) data type (i.e. return 0.00 or 100.00)
...
ISNULL(CONVERT(DECIMAL(6,2),(SUM(CONVERT(NUMERIC,CRC.Contact))/(CONVERT(DECIMAL(6,2),(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)*1.00)/3600)))),0) As CPH,
ISNULL(CONVERT(DECIMAL(6,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(CONVERT(DECIMAL(6,2),(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)*1.00)/3600)))),0) As SPH
ISNULL(CONVERT(DECIMAL(5,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) As SPC
just wrap the denominator in nullif(expression,0). also, make your life (and the life of anyone who has to maintain that code) easier by eliminating the redundant converts and casting a single term as float. what's using this query should be able to just round the numbers to 2 digits, or you can just cast the final expression as numeric(N,2).
ISNULL(CRC.Contact
/nullif(SUM(convert(float,TalkTime) + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime),0)/3600
,0) As CPH,
ISNULL(CRC.Success
/nullif(SUM(convert(float,TalkTime) + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime),0)/3600
,0) As SPH
ISNULL(CRC.Success
/(SUM(CONVERT(float,CRC.Contact))*100)
,0) As SPC
May 20, 2008 at 8:33 am
I've found using NullIf on the denominator works for many of these cases. Instead of a div-0 error, you get a null result.
select 1/0
Gives div-0 error.
select 1/nullif(0,0)
Gives null result.
It can, of course, be wrapped in an IsNull.
select isnull(1/nullif(0,0), 0)
Gives a result of 0.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2011 at 10:16 am
The NullIf thing is just fabulous!! Saved a lot of pain 😀
June 13, 2011 at 2:44 pm
Yup, thanks GSquared. NullIf explanation saved me today.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply