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

Version 1
    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