May 28, 2008 at 2:20 pm
Hi All,
I have a query where I need/want a decimal value returned (i.e. 8.00 instead of 8.009) but also where I get 8.00 instead of 8.01. My code is below, thank you inadvance...
DECLARE @Date as varchar(10)
SET @Date = '16/05/2008'
SELECTCONVERT(Varchar, History.CallDateTime, 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,
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)) 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 28, 2008 at 2:26 pm
What's the data types of these columns?
TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 2:37 pm
Int
Full layout of History table below;
[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,
May 29, 2008 at 9:17 am
...(i.e. 8.00 instead of 8.009) but also where I get 8.00 instead of 8.01....
you want 8.00 instead of 8.009. So you go from 3 spaces after to 2 spaces and it rounds up from 8.009 to 8.01. Sounds like you want to truncate you numbers instead of rounding them.
Also, what accuracy do you want? You'd rather have 8.00 than 8.01 even though you're loosing accuracy.
I'm guessing by "Absolute Values In Decimals" you meant "Integer Results with Decimals" or something like that.
Try select CONVERT(DECIMAL(6,2),convert(int, 8.009)). Converting your results to an integer to truncate it, then to your decimal. I tried FLOOR() but that returns 8 instead of 8.00.
P.S. 'Absoulute' value of 8.009 is 8.009, but of -8.009 is 8.009. Just the positive(absolute) value.
May 29, 2008 at 10:01 am
you should stop converting the numerators to numerics and use floats instead. ideally the app that's consuming this data should format the number, but if that's not possible, then use a convert to format the result (not the numerator):
CONVERT(DECIMAL(6,2),(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)*1.00)/3600
becomes
convert(decimal(6,2),
SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)/convert(float, 3600) )
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply