May 24, 2008 at 5:29 pm
Hi All,
I have 2 issues,
1. The code won't work on some days but will on others.
2. The date I get in the select statement is always the current date, not the date from the where clause or parameter.
Issue 1:
This code does work but depending on the day. For example when i set the variable @Date to 15/05/2008 the query works, but if i change it to 14/05/2008 i get the following error:
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.
Now although it says line 4 if I rem out the case statements for SPH, CPH SPC and Wrap then it works fine.
Issue 2:
Whether the code works or not it always returns the current date the query was run rather than the date used in the where clause.
The full code is
DECLARE @Date as varchar(10)
SET @Date = '14/05/2008'--Change date for different dates
SELECTCONVERT(Varchar, GETDATE(), 103) As Date, Agent.Firstname + ' ' + Agent.Lastname AS AgentName, 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,
--CASE STATEMENT FOR DIVIDING NULLS
CASE WHEN (SUM(CONVERT(NUMERIC,CRC.Contact))*100) <> 0.0 THEN 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) ELSE 0.0 END As CPH,
CASE WHEN (SUM(CONVERT(NUMERIC,CRC.Success))*100) <> 0.0 THEN 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) ELSE 0.0 END As SPH,
CASE WHEN (SUM(CONVERT(NUMERIC,CRC.Success))*100) <> 0.0 THEN ISNULL(CONVERT(DECIMAL(5,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) ELSE 0.0 END As SPC,
CASE WHEN (CONVERT(DECIMAL(6,2),(SUM(WrapTime)*1.00)/3600)) <> 0.00 THEN CONVERT(DECIMAL(5,2),((CONVERT(DECIMAL(6,2),(SUM(WrapTime)*1.00)/3600))/(CONVERT(DECIMAL(6,2),(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)*1.00)/3600))))*100 ELSE 0.0 END As Wrap
FROMAgent 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 BYCONVERT(Varchar, History.CallDateTime, 103), Agent.Firstname + ' ' + Agent.Lastname, Agent.AgentID, Groups.GroupName
ORDER By Groups.GroupName
May 24, 2008 at 6:06 pm
Obviously, the following creates a zero somewhere...
TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime
What do you want to do when you have a zero in that simple bit of addition?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2008 at 3:23 am
Still add it... So 65 + 10 + 0 + 10 = 85
But surely you can still sum a zero....?
May 25, 2008 at 9:01 am
Yes, but that bit of math is the divisor in a larger formula...
CASE WHEN (SUM(CONVERT(NUMERIC,CRC.Contact))*100) <> 0.0 THEN 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) ELSE 0.0 END As CPH,
If ALL those things in blue are zero, then you'll have zero as the divisor for the big red division operator. You have to decide what you want to happen if you have a divide-by-zero or write criteria in the WHERE clause to ensure than only non-zero divisors are considered by the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2008 at 5:05 pm
Okay, I ran a query to check this theory using the following;
Select *
From History
Where (CONVERT(Varchar, History.CallDateTime, 103) = '14/05/2008') AND(TalkTime = 0) AND (HoldTime = 0) AND (WrapTime = 0) AND (WaitTime = 0) AND (PreviewTime = 0) AND (DeadTime = 0)
What i get is as the query asks but I get 0's in both 14th and 15 and every other day.
So also taking this into account I changes the case statement to sum all times, then if 0 then 0.00 else the main query. Do I need to use 2 case statements? Code below;
DECLARE @Date as varchar(10)
SET @Date = '14/05/2008'
SELECTCONVERT(Varchar, GETDATE(), 103) As Date, Agent.Firstname + ' ' + Agent.Lastname AS AgentName, 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,
--CASE STATEMENT FOR DIVIDING NULLS
CASE WHEN (SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)) <> 0 THEN 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) ELSE 0.00 END As CPH
--CASE WHEN (SUM(CONVERT(NUMERIC,CRC.Success))*100) <> 0.0 THEN 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) ELSE 0.0 END As SPH,
--CASE WHEN (SUM(CONVERT(NUMERIC,CRC.Success))*100) <> 0.0 THEN ISNULL(CONVERT(DECIMAL(5,2),(SUM(CONVERT(NUMERIC,CRC.Success))/(SUM(CONVERT(NUMERIC,CRC.Contact))))*100),0) ELSE 0.0 END As SPC,
--CASE WHEN (CONVERT(DECIMAL(6,2),(SUM(WrapTime)*1.00)/3600)) <> 0.00 THEN CONVERT(DECIMAL(5,2),((CONVERT(DECIMAL(6,2),(SUM(WrapTime)*1.00)/3600))/(CONVERT(DECIMAL(6,2),(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)*1.00)/3600))))*100 ELSE 0.0 END As Wrap
FROMAgent 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 BYCONVERT(Varchar, History.CallDateTime, 103), Agent.Firstname + ' ' + Agent.Lastname, Agent.AgentID, Groups.GroupName
ORDER By Groups.GroupName
May 25, 2008 at 5:56 pm
I don't see why you need a CASE statement to begin with...
CONVERT(DECIMAL(6,2),ISNULL(SUM(CONVERT(NUMERIC,CRC.Contact))/NULLIF(SUM(TalkTime+HoldTime+WrapTime+WaitTime+PreviewTime+DeadTime)/3600.0,0),0))
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2008 at 9:01 pm
That worked great... Never used or heard of NULLIF... Full working code is;
DECLARE @Date as varchar(10)
SET @Date = '14/05/2008'
SELECT@Date As Date, Agent.Firstname + ' ' + Agent.Lastname AS AgentName, 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,
CONVERT(DECIMAL(6,2),ISNULL(SUM(CONVERT(NUMERIC,CRC.Contact))/NULLIF(SUM(TalkTime+HoldTime+WrapTime+WaitTime+PreviewTime+DeadTime)/3600.0,0),0)) As CPH,
CONVERT(DECIMAL(6,2),ISNULL(SUM(CONVERT(NUMERIC,CRC.Success))/NULLIF(SUM(TalkTime+HoldTime+WrapTime+WaitTime+PreviewTime+DeadTime)/3600.0,0),0)) As SPH,
CONVERT(DECIMAL(6,2),ISNULL(SUM(CONVERT(NUMERIC,CRC.Success))/NULLIF(SUM(CONVERT(NUMERIC,CRC.Contact)),0),0))*100 As SPC,
CONVERT(DECIMAL(6,2),ISNULL((SUM(WrapTime + DeadTime)/3600.0)/NULLIF(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)/3600.0,0),0)*100) As Wrap
FROMAgent 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 BYCONVERT(Varchar, History.CallDateTime, 103), Agent.Firstname + ' ' + Agent.Lastname, Agent.AgentID, Groups.GroupName
ORDER By Groups.GroupName
May 26, 2008 at 7:07 am
One more quick question... which table are all the times (and other columns) coming from? I can't tell because you're not using table aliases on the columns. The reason I ask is because there might just be one more optimization I can show you... to do it, I need to know which columns are coming from which tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 26, 2008 at 1:40 pm
My dates & times are coming from a History table. This table contains a Key on HistoryID, and has the date and time in a DD/MM/YYYY HH:MM:SS:MMM format.
[HistoryID] [int] IDENTITY(1,1) NOT NULL,
[SystemID] [tinyint] NOT NULL CONSTRAINT [DF__History__SystemI__17036CC0] DEFAULT ((0)),
[ProjectID] [int] NOT NULL,
[ProjName] [varchar](35) NOT NULL,
[SourceTable] [varchar](32) NOT NULL,
[SourceField] [varchar](32) NOT NULL,
[SourceID] [varchar](32) NOT NULL,
[CallDateTime] [datetime] NOT NULL,
[PhoneNum] [varchar](15) NOT NULL,
[IbOb] [int] NULL,
[CallData] [varchar](240) NOT NULL,
[AttResult] [int] NOT NULL,
[TalkTime] [int] NOT NULL,
[HoldTime] [int] NOT NULL,
[WrapTime] [int] NOT NULL,
[QueueTime] [int] NOT NULL,
[ConnectTime] [int] NOT NULL,
[AgentID] [int] NOT NULL,
[DNIS] [varchar](10) NOT NULL,
[ANI] [varchar](15) NOT NULL,
[CRC] [varchar](5) NOT NULL,
[Transfer] [varchar](255) NOT NULL,
[IVRs] [varchar](200) NOT NULL,
[Notes] [varchar](255) NOT NULL,
[Abandoned] [bit] NOT NULL CONSTRAINT [DF__History__Abandon__17F790F9] DEFAULT ((0)),
[RoutePID] [int] NOT NULL CONSTRAINT [DF__History__RoutePI__18EBB532] DEFAULT ((0)),
[WaitTime] [int] NOT NULL CONSTRAINT [DF__History__WaitTim__19DFD96B] DEFAULT ((0)),
[DeadTime] [int] NOT NULL CONSTRAINT [DF__History__DeadTim__1AD3FDA4] DEFAULT ((0)),
[PreviewTime] [int] NOT NULL CONSTRAINT [DF__History__Preview__1BC821DD] DEFAULT ((0)),
[IsAlt] [bit] NOT NULL CONSTRAINT [DF__History__IsAlt__1CBC4616] DEFAULT ((0)),
[Attempt] [int] NOT NULL CONSTRAINT [DF__History__Attempt__1DB06A4F] DEFAULT ((0)),
[DialID] [int] NOT NULL CONSTRAINT [DF__History__DialID__1EA48E88] DEFAULT ((0)),
[Revenue] [money] NOT NULL CONSTRAINT [DF__History__Revenue__1F98B2C1] DEFAULT ((0)),
[SessionID] [int] NOT NULL CONSTRAINT [DF__History__Session__208CD6FA] DEFAULT ((0)),
[Line] [smallint] NULL,
[PreAnswerTime] [int] NULL,
[ParentID] [int] NULL,
[SubSystemID] [tinyint] NULL,
[CauseValue] [int] NULL,
[CallerDiscon] [bit] NULL,
CONSTRAINT [PK__History__160F4887] PRIMARY KEY CLUSTERED
This table is linked to a number of other tables (as you might tell from the query)... Any perfromance improvments are more than welcome.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply