May 29, 2008 at 2:50 pm
Hi All..
I am trying to produce a report which retuns the time spent in different states in a hh:MM:SS, I am using cascading rows, where the data is displayed as;
Page Break: Date
Group Break: CampaignName
Detail Break: The rest.
So I get results for all agents in the hh:mm:ss format, but i can't sum all these agent times in the GroupBreak(=Sum(Fields!DiallingTime.Value)) as all I do get is #Error. So If I get 5 agents on a campaign, each dialling 01:00:00 each, then I want the following (and I want to be able to count more than 24 hours);
CampaignName DiallingTime TalkTime
Campaign 1 05:00:00 03:45:00
Agent1 01:00:00 00:45:00
Agent2 01:00:00 00:45:00
Agent3 01:00:00 00:45:00
Agent4 01:00:00 00:45:00
Agent5 01:00:00 00:45:00
Campaign 2 615:00:00 612:00:00
Agent1 07:42:12 06:00:05
.........
Agent96 06:05:22 05:00:01
Full query code is as below:
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,
dbo.SFormat(SUM(TalkTime + HoldTime + WrapTime + WaitTime + PreviewTime + DeadTime)) As DiallingTime,
dbo.SFormat(SUM(TalkTime + HoldTime)) As TalkTime,
dbo.SFormat(SUM(WaitTime + PreviewTime)) As WaitTime,
dbo.SFormat(SUM(WrapTime + DeadTime)) 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 29, 2008 at 3:12 pm
I have found the folloowing which is almost what I want, its a function created by anothe rmember to return days, hours, minutes and seconds in a 3:6:35:31 (d:H:MM:SS) format, I need to edit to display hhh:mm:ss so in the above example 78:35:31...
How can I modify this code to get what I need?
Function secondsToString(seconds As Int64) As String
Dim myTS As New TimeSpan(seconds * 10000000)
Return String.Format("{0:G}:{1:G}:{2:G}:{3:G}", myTS.Days, myTS.Hours, myTS.Minutes, myTS.Seconds)
End Function
May 29, 2008 at 5:30 pm
If you want people to help you with this, I suggest you read:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Your post as is has way too many questions about the data structure to be able to answer this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply