CUIC - Splitting DateTime into separate fields. (Service Historical)

Document created by cdnadmin on Jan 25, 2014
Version 1Show Document
  • View in full screen mode
This document was generated from CDN thread

Created by: Kieran Byng on 04-01-2013 08:17:14 AM
Hello, I'm trying to change the Service Historical report to allow for Date and Time to be separate fields so that I can group by time (without Date) to allow us to compare time intervals performance across weeks & months. I've tried using 'CONVERT(char(8),CTHH.DateTime,108) as Time' & 'CONVERT(char(10),CTHH.DateTime,101) as Date' and although the report does validate it corrupts the report when trying to view/run them. (It corrupted all reports based on this def and wouldn't let me 'undo' and didn't like it when I restored it to it's previous state - painful - learnt my lesson!) Does anyone have any ideas on how I could get round this or what I should be trying? Any help is much appreciated! Thanks!

Subject: Re: New Message from Kieran Byng in Contact Center Reporting (CCR) - Contac
Replied by: Vladimir Lazovic on 04-01-2013 08:19:09 AM
Postovani,

Trenutno se nalazim na godisnjem odmoru.
Za sve hitne zahteve mozete kontaktirati Lazara Obradovica na email na lobradovic@algotech.rs ili na broj 0653537699.

Hvala na razumevanju

Pozdrav

Vladimir


Dear sender,

Thank you for your email.

I am out of the office till Monday, 08.01.13 and unable to respond at this time.

I will review your message following my return on Monday, 08.01.2013.

Thank you for your understanding.
Best regards,

Vladimir

Subject: RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Replied by: Luis Yrigoyen on 14-01-2013 02:19:22 PM
Did you get it to work?
What version of CUIC are you running?

Subject: RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Replied by: Kieran Byng on 15-01-2013 04:03:18 AM
We're using CUIC version 8.5(2) - I haven't managed to get it to work as yet.

Subject: RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Replied by: Dennis Browne on 15-01-2013 09:05:12 AM
Try this instead select CONVERT(CHAR(5),CTHH.DateTime,8) as Time, this will give you the hour and minutes only.

Subject: RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Replied by: Kieran Byng on 15-01-2013 09:45:33 AM
I added that to the report definition and the query validated / field created successfully.
Then when I tried to run a report based on the new definition it gave a 'dataset error - formula failed' message.

Subject: RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Replied by: Luis Yrigoyen on 15-01-2013 10:40:41 AM
This worked for me (report attached):
IN YOUR QUERY:
Delete the DateTime field and add these two:
Interval=dateadd(day,  datediff(day,0,  Service_Interval.DateTime), 0),
Time = CONVERT(varchar(8),Service_Interval.DateTime,108),
 
Make sure you add them to your Group By clause:
GROUP BY Service.EnterpriseName,   S
ervice_Interval.SkillTargetID, 
Service_Interval.TimeZone, 
dateadd(day, 
datediff(day,0, 
Service_Interval.DateTime), 0),
CONVERT(varchar(8),Service_Interval.DateTime,108),  
Service_Interval.DateTime,
Service_Interval.RecoveryKey,
Service_Interval.ServiceLevelType,
Service_Interval.DbDateTime
 
Optional -- Add the first one to the order by.      
ORDER BY
Service.EnterpriseName,
Service_Interval.SkillTargetID,
dateadd(day,  datediff(day,0,  Service_Interval.DateTime), 0)
 
Create your fields:
Make sure your Interval and Time fields' format is "1/15/13" and "None" respectively.  
ON THE PROPERTIES TAB: Change the Historical Key Field to Interval (Interval)
 
That's it for the Definition now the report groupings:
 
On the View, the fields the first fields should be EnterpriseName, Time, Interval.
On the report's Grouping: Number of groups = 2 Group 1 = EnterpriseName Group 2 = Time    

Subject: RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Replied by: Joseph Walsh on 15-01-2013 11:00:27 AM
I've had success doing:
CONVERT(varchar,,108) as Row_Time
in my Report Definition.  I haven't ever had an issue with it corrupting a report or not working during validation and creating fields.

Subject: RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Replied by: Dennis Browne on 15-01-2013 10:34:29 AM
You'll need to check on which field is causing the error.  I have this working in a report.

Subject: RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Replied by: Dennis Browne on 15-01-2013 11:48:02 AM
Just wanted to recommend a best practice here...
There are several different calculations that you can use to arrive at your desired result but the sql box in the report definition isn't a good place to experiment.  I recommend using a query development tool, like sql query analyzer to build your query before using CUIC to develop the report.  I keep documented copies of my sql queries so that I can always revisit the code with my own comments included and then strip them out before pasting the query into CUIC.  The key is to ensure the sql is correct before moving to CUIC.
 

Subject: RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Replied by: VARUN Gaur on 13-02-2013 05:43:36 AM
/* This is the report will give you good idea about how to seperate Date And Time Seperate
This Report is Call Type Historical, bydefault is half hour report. I made it as Hourly and Date and Time are seperate Columns*/.
BEGIN 
SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF 
SET NOCOUNT ON 
SET QUOTED_IDENTIFIER ON 
SET ROWCOUNT 0 
 
DECLARE 
@dtStartDateTime DATETIME, 
@dtEndDateTime DATETIME, 
@sDateRange AS CHAR(1) 
 
 
SET @sDateRange = :daterange 
 
DECLARE @TodaysDate DATETIME 
SET @TodaysDate = GETDATE() 
 
IF @sDateRange = 'D' --DAILY 
BEGIN 
SET @dtStartDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(10),@TodaysDate, 101)) 
SET @dtEndDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(10),@TodaysDate + 1, 101)) 
END 
 
ELSE IF @sDateRange = 'M' -- MONTHLY 
BEGIN 
SET @dtStartDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(2), DATEPART(MM,@TodaysDate)) + '/1/' + CONVERT(VARCHAR(4), DATEPART(YY,@TodaysDate))) 
SET @dtEndDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(10),@TodaysDate + 1, 101)) 
END 
 
ELSE IF @sDateRange = 'C' -- CUSTOM DATE RANGE 
BEGIN 
SET @dtStartDateTime =  :startdate
SET @dtEndDateTime = :enddate  
END 
 
ELSE -- WEEKLY (IF User specified anyother value than these 4 than also Weekly will execute.) 
 
BEGIN 
SET @dtStartDateTime = DATEADD(D, -1 * ((CASE (DATEPART(DW, @TodaysDate) - 2) WHEN -1 THEN 6 ELSE (DATEPART(DW, @TodaysDate) - 2) END)), @TodaysDate) 
SET @dtEndDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(10),@TodaysDate + 1, 101)) 
END 
 
SELECT 
CTHH.CallTypeID,
sum(isnull(CTHH.RouterQueueWaitTime,0)) as RouterQueueWaitTime,   
sum(isnull(CTHH.RouterQueueCalls,0)) as RouterQueueCalls, 
AvgRouterDelay = sum(isnull(CTHH.AvgRouterDelayQ,0)), 
sum(isnull(CTHH.RouterCallsAbandQ,0)) as RouterCallsAbandQ,   
sum(isnull(CTHH.RouterQueueCallTypeLimit,0)) as RouterQueueCallTypeLimit,   
sum(isnull(CTHH.RouterQueueGlobalLimit,0)) as RouterQueueGlobalLimit,   
sum(isnull(CTHH.CallsRouted,0)) as CallsRouted,   
sum(isnull(CTHH.ErrorCount,0)) as ErrorCount,   
sum(isnull(CTHH.ICRDefaultRouted,0)) as ICRDefaultRouted,   
sum(isnull(CTHH.NetworkDefaultRouted,0)) as NetworkDefaultRouted,    
sum(isnull(CTHH.ReturnBusy,0)) as ReturnBusy,   
sum(isnull(CTHH.ReturnRing,0)) as ReturnRing,   
sum(isnull(CTHH.NetworkAnnouncement,0)) as NetworkAnnouncement,   
sum(isnull(CTHH.AnswerWaitTime,0)) as AnswerWaitTime,   
sum(isnull(CTHH.CallsHandled,0)) as CallsHandled,   
sum(isnull(CTHH.CallsOffered,0)) as CallsOffered,   
sum(isnull(CTHH.HandleTime,0)) as HandleTime,   
sum(isnull(CTHH.ServiceLevelAband,0)) as ServiceLevelAband,   
sum(isnull(CTHH.ServiceLevelCalls,0)) as ServiceLevelCalls,   
sum(isnull(CTHH.ServiceLevelCallsOffered, 0)) as ServiceLevelCallsOffered,   
avg(isnull(CTHH.ServiceLevel,0)) as ServiceLevel,   
sum(isnull(CTHH.TalkTime,0)) as TalkTime,   
sum(isnull(CTHH.OverflowOut,0)) as OverflowOut,   
sum(isnull(CTHH.HoldTime,0)) as HoldTime,   
sum(isnull(CTHH.IncompleteCalls,0)) as IncompleteCalls,   
Datepart(yy, CTHH.DateTime) as Year,
Datepart(mm, CTHH.DateTime) as Month,
Datepart(ww, CTHH.DateTime) as Week,
Datepart(dy, CTHH.DateTime) as DOY,
Datepart(dw, CTHH.DateTime) as DOW,
CONVERT(DATETIME, CONVERT(VARCHAR(15), CTHH.DateTime , 101)) as Date,
CONVERT(VARCHAR(2), CTHH.DateTime, 108)+':00-'+CONVERT(VARCHAR(2),DATEADD(HOUR,1,(CONVERT(VARCHAR(10), CTHH.DateTime, 108))),108)+':00' AS HOUR,
Call_Type.EnterpriseName,
sum(isnull(CTHH.ShortCalls , 0))  as ShortCalls,
sum(isnull(CTHH.DelayQAbandTime , 0))  as DelayQAbandTime,
sum(isnull(CTHH.CallsAnswered , 0))  as CallsAnswered,
sum(isnull(CTHH.CallsRoutedNonAgent , 0))  as CallsRoutedNonAgent,
sum(isnull(CTHH.CallsRONA , 0)) as CallsRONA,
sum(isnull(CTHH.ReturnRelease , 0))  as ReturnRelease,
sum(isnull(CTHH.CallsQHandled , 0))  as CallsQHandled,
sum(isnull(CTHH.VruUnhandledCalls , 0)) as VruUnhandledCalls,
sum(isnull(CTHH.VruHandledCalls , 0)) as VruHandledCalls,
sum(isnull(CTHH.VruAssistedCalls , 0))  as VruAssistedCalls,
sum(isnull(CTHH.VruOptOutUnhandledCalls, 0)) as VruOptOutUnhandledCalls,
sum(isnull(CTHH.VruScriptedXferredCalls, 0)) as VruScriptedXferredCalls,
sum(isnull(CTHH.VruForcedXferredCalls , 0)) as VruForcedXferredCalls,
sum(isnull(CTHH.VruOtherCalls, 0)) as VruOtherCalls,
CTHH.ServiceLevelType as ServiceLevelType,
CTHH.BucketIntervalID as BucketIntervalID,
sum(isnull(CTHH.AnsInterval1,0)) as AnsInterval1,
sum(isnull(CTHH.AnsInterval2,0)) as AnsInterval2,
sum(isnull(CTHH.AnsInterval3,0)) as AnsInterval3, 
sum(isnull(CTHH.AnsInterval4,0)) as AnsInterval4,
sum(isnull(CTHH.AnsInterval5,0)) as AnsInterval5, 
sum(isnull(CTHH.AnsInterval6,0)) as AnsInterval6,
sum(isnull(CTHH.AnsInterval7,0)) as AnsInterval7, 
sum(isnull(CTHH.AnsInterval8,0)) as AnsInterval8, 
sum(isnull(CTHH.AnsInterval9,0)) as AnsInterval9, 
sum(isnull(CTHH.AnsInterval10,0)) as AnsInterval10,
sum(isnull(CTHH.AbandInterval1,0)) as AbandInterval1 ,
sum(isnull(CTHH.AbandInterval2,0)) as AbandInterval2,
sum(isnull(CTHH.AbandInterval3,0)) as AbandInterval3 ,
sum(isnull(CTHH.AbandInterval4,0)) as AbandInterval4,
sum(isnull(CTHH.AbandInterval6,0)) as AbandInterval6,
sum(isnull(CTHH.AbandInterval7,0)) as AbandInterval7 ,
sum(isnull(CTHH.AbandInterval8,0)) as AbandInterval8,
sum(isnull(CTHH.AbandInterval9,0)) as AbandInterval9,
sum(isnull(CTHH.AbandInterval10,0)) as AbandInterval10,
sum(isnull(CTHH.RouterCallsAbandToAgent,0)) as RouterCallsAbandToAgent,   
sum(isnull(CTHH.TotalCallsAband, 0)) as TotalCallsAband,   
sum(isnull(CTHH.DelayAgentAbandTime,0)) as DelayAgentAbandTime,   
sum(isnull(CTHH.CallDelayAbandTime,0)) as CallDelayAbandTime,   
sum(isnull(CTHH.CTDelayAbandTime,0)) as CTDelayAbandTime,   
sum(isnull(CTHH.ServiceLevelError ,0)) as ServiceLevelError,   
sum(isnull(CTHH.ServiceLevelRONA,0)) as ServiceLevelRONA,   
sum(isnull(CTHH.AgentErrorCount,0)) as AgentErrorCount,   
sum(isnull(CTHH.VRUTime,0)) as VRUTime,   
ReturnBR = sum(isnull(CTHH.ReturnBusy,0)) + sum(isnull(CTHH.ReturnRing,0)) + sum(isnull(CTHH.ReturnRelease,0)),
sum(isnull(CTHH.CTVRUTime,0))  as CTVRUTime, 
asa= case when sum(isnull(CTHH.CallsAnswered,0)) = 0 then 0
            else sum(isnull(CTHH.AnswerWaitTime,0)) * 1.0
            / sum(isnull(CTHH.CallsAnswered,0))
            end,
      per_aban = (sum(isnull(CTHH.TotalCallsAband,0)) * 1.0) 
            / (sum(isnull(CTHH.CallsHandled,0))
            + sum(isnull(CTHH.TotalCallsAband,0))
            + sum(isnull(CTHH .IncompleteCalls,0))
            + sum(isnull(CTHH.ReturnBusy,0))
            + sum(isnull(CTHH.ReturnRing,0))
            + sum(isnull(CTHH.ICRDefaultRouted,0))
            + sum(isnull(CTHH.NetworkDefaultRouted,0))
            + sum(isnull(CTHH.OverflowOut,0))
            + sum(isnull(CTHH.CallsRONA,0))
            + sum(isnull(CTHH.ReturnRelease,0))
            + sum(isnull(CTHH.CallsRoutedNonAgent,0))
            + sum(isnull(CTHH.ShortCalls,0))
            + sum(isnull(CTHH.AgentErrorCount,0))
            + sum(isnull(CTHH.ErrorCount,0))),
      avg_aban_delay = case when sum(isnull(CTHH.TotalCallsAband,0)) = 0 then 0
            else (sum(isnull(CTHH.CallDelayAbandTime,0)) * 1.0) 
            / sum(isnull(CTHH.TotalCallsAband,0))
            end,
      totalerrorcount= sum(isnull(CTHH.ErrorCount,0)) 
            + sum(isnull(CTHH.IncompleteCalls,0)) + sum(isnull(CTHH.AgentErrorCount,0)),
      CompletedTasks = sum(isnull(CTHH.CallsHandled,0))
            + sum(isnull(CTHH.TotalCallsAband,0))
            + sum(isnull(CTHH.IncompleteCalls,0))
            + sum(isnull(CTHH.ReturnBusy,0))
            + sum(isnull(CTHH.ReturnRing,0))
            + sum(isnull(CTHH.ICRDefaultRouted,0))
            + sum(isnull(CTHH.NetworkDefaultRouted,0))
            + sum(isnull(CTHH.OverflowOut,0))
            + sum(isnull(CTHH.CallsRONA,0))
            + sum(isnull(CTHH.ReturnRelease,0))
            + sum(isnull(CTHH.CallsRoutedNonAgent,0))
            + sum(isnull(CTHH.ShortCalls,0))
            + sum(isnull(CTHH.AgentErrorCount,0))
            + sum(isnull(CTHH.ErrorCount,0)),
      Other = sum(isnull(CTHH.CallsRONA,0))
            + sum(isnull(CTHH.CallsRoutedNonAgent,0))
            + sum(isnull(CTHH.ShortCalls,0)),   
      per_queued = case when sum(isnull(CTHH.CallsHandled,0)) = 0 then 0
            else sum(isnull(CTHH.CallsQHandled,0)) * 1.0
            / sum(isnull(CTHH.CallsHandled,0))
            End
FROM Call_Type_Interval CTHH (nolock),   
Call_Type (nolock)    
 
WHERE ( CTHH.CallTypeID = Call_Type.CallTypeID ) AND ( CTHH.DateTime BETWEEN @dtStartDateTime AND @dtEndDateTime ) AND ( CTHH.CallTypeID IN (:Call_Type) )
 
Group By CONVERT(DATETIME, CONVERT(VARCHAR(15), CTHH.DateTime , 101)),
CONVERT(VARCHAR(2), CTHH.DateTime, 108)+':00-'+CONVERT(VARCHAR(2),DATEADD(HOUR,1,(CONVERT(VARCHAR(10), CTHH.DateTime, 108))),108)+':00',
Datepart(yy, CTHH.DateTime) ,
Datepart(mm, CTHH.DateTime) ,
Datepart(ww, CTHH.DateTime) ,
Datepart(dy, CTHH.DateTime) ,
Datepart(dw, CTHH.DateTime) ,
Call_Type.EnterpriseName,
Call_Type.EnterpriseName,
CTHH.CallTypeID,      
CTHH.BucketIntervalID,
CTHH.ServiceLevelType
ORDER BY Call_Type.EnterpriseName,CONVERT(DATETIME, CONVERT(VARCHAR(15), CTHH.DateTime , 101)),
CONVERT(VARCHAR(2), CTHH.DateTime, 108)+':00-'+CONVERT(VARCHAR(2),DATEADD(HOUR,1,(CONVERT(VARCHAR(10), CTHH.DateTime, 108))),108)+':00'
END

Attachments

    Outcomes