CUIC - Problem with order by clause

Unanswered Question
Mar 20th, 2017
User Badges:

Hi everyone,


I'm facing an issue with CUIC 11.5, I'm trying to run an Agent based report against Agent_Interval. The query contains an order by clause but when the report is executed cuic is not sorting the results at all.


Here's the query (Anonymous block):


SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
SELECT  
    Date         = CONVERT(char(10), AI.DateTime, 103),
    FullName     = Person.LastName + Person.FirstName,
    LoginName = Person.LoginName,
    SkillGroupName = ASGI.SGEnterpriseName,
    AgentLoggedOnTime = SUM(ISNULL( AI.LoggedOnTime,0)),
    TalkTime = sum(ASGI.TalkTime),
    HoldTime = sum(ASGI.HoldTime),
    WrapTime = sum(ASGI.WrapTime),
    AgentAvailTime = SUM(ISNULL(AI.AvailTime, 0)),
    AgentNotReady = SUM(ISNULL(AI.NotReadyTime, 0)) ,
    ReservedTime = sum(ASGI.ReservedTime),
    AgentBusyOtherTime = SUM(ISNULL(AI.LoggedOnTime,0))
                        -
                        (SUM(ISNULL(AI.AvailTime,0))
                        + SUM(ISNULL(ASGI.TalkTime,0))
                        + SUM(ISNULL(ASGI.HoldTime,0))
                        + SUM(ISNULL(ASGI.WrapTime,0))
                        + SUM(ISNULL(AI.NotReadyTime,0))
                        + SUM(ISNULL(ASGI.ReservedTime,0))),
    CallsHandled = SUM(ISNULL(ASGI.CallsHandled,0)),
    InternalCalls = SUM(ISNULL(ASGI.InternalCalls,0)),
    ConsultativeCalls = SUM(ISNULL(ASGI.ConsultativeCalls,0)),
    TotalCalls = SUM(ISNULL(ASGI.CallsHandled,0))
                + SUM(ISNULL(ASGI.InternalCalls,0))
                + SUM(ISNULL(ASGI.ConsultativeCalls,0))
                + SUM(ISNULL(ASGI.TransferInCalls,0))
                + SUM(ISNULL(ASGI.TransferOutCalls, 0)),
    OtrasCalls = SUM(ISNULL(ASGI.TransferInCalls,0)) + SUM(ISNULL(ASGI.TransferOutCalls, 0)),
    CallsAnswered  = sum(ASGI.CallsAnswered),
    AbandRingCalls = sum(ASGI.AbandRingCalls),
    AbandRingCallsTime = sum(ASGI.AbandRingCallsTime),
    AbandonHoldCalls = sum(ASGI.AbandonHoldCalls),
    TransferInCalls = sum(ASGI.TransferInCalls),
    TransferOutCalls = sum(ASGI.TransferOutCalls),
    InCallsOnHold = sum(ASGI.InCallsOnHold),
    InCallsOnHoldTime = sum(ASGI.InCallsOnHoldTime),
    IntCallsOnHold = sum(ASGI.IntCallsOnHold),
    IntCallsOnHoldTime = sum(ASGI.IntCallsOnHoldTime),
    HandledCallsTime = sum(ASGI.HandledCallsTime),
    AnswerWaitTime = sum(ASGI.AnswerWaitTime),
    ConsultativeCallsTime = sum(ASGI.ConsultativeCallsTime),
    HandledCallsTalkTime = sum(ASGI.HandledCallsTalkTime),
    InternalCallsRcvd = sum(ASGI.InternalCallsRcvd),
    InternalCallsRcvdTime = sum(ASGI.InternalCallsRcvdTime),
    InternalCallsTime = sum(ASGI.InternalCallsTime),
    TransferredInCallsTime = sum(ASGI.TransferredInCallsTime),
    TalkOtherTime = sum(ASGI.TalkOtherTime),
    TalkOutTime = sum(ASGI.TalkOutTime),
    TransOut = SUM(ASGI.TransOut),
    AHT = ISNULL(SUM(ASGI.HandledCallsTime) / SUM(ASGI.CallsHandled),0),
    AHoldT = ISNULL(SUM(ASGI.InCallsOnHoldTime) / SUM(ASGI.InCallsOnHold),0),
    AACW = ISNULL((SUM(ASGI.WrapTime) / SUM(ASGI.CallsHandled)),0),
    perWACW = ISNULL((SUM(ASGI.WrapTime) + SUM(ASGI.TalkTime) + SUM(ASGI.HoldTime) + SUM(ISNULL(AI.NotReadyTime,0)) )* 1.0 / SUM(ISNULL(AI.LoggedOnTime,0)),0),
    perWOACW = ISNULL((SUM(ASGI.HoldTime) + SUM(ISNULL(AI.NotReadyTime,0))+ SUM(ASGI.TalkTime))* 1.0 / SUM(ISNULL(AI.LoggedOnTime,0)),0)
FROM
    Agent (nolock),
    Agent_Interval AI (nolock),
    Person (nolock),
    (Select
        A.DateTime,
        A.SkillTargetID,
        A.SGEnterpriseName,
        A.SGSkillTargetID,
        A.SGPeripheralID,
        CallsAnswered  = SUM(ISNULL(A.CallsAnswered,0)),
        CallsHandled = SUM(ISNULL(A.CallsHandled,0)),
        AbandRingCalls = SUM(ISNULL(A.AbandonRingCalls,0)),
        AbandRingCallsTime = SUM(ISNULL(A.AbandonRingTime,0)),
        AbandonHoldCalls = SUM(ISNULL(A.AbandonHoldCalls,0)),
        TransferInCalls = SUM(ISNULL(A.TransferredInCalls,0)),
        TransferOutCalls = SUM(ISNULL(A.TransferredOutCalls,0)),
        ConsultativeCalls = SUM(ISNULL(A.ConsultativeCalls,0)),
        InCallsOnHold = SUM(ISNULL(A.IncomingCallsOnHold,0)),
        InCallsOnHoldTime = SUM(ISNULL(A.IncomingCallsOnHoldTime,0)),
        IntCallsOnHold = SUM(ISNULL(A.InternalCallsOnHold,0)),
        IntCallsOnHoldTime = SUM(ISNULL(A.InternalCallsOnHoldTime,0)),
        TalkTime = sum(isnull(A.TalkInTime,0)) +
                    sum(isnull(A.TalkOutTime,0)) +
                    sum(isnull(A.TalkOtherTime,0)),
        HandledCallsTime = SUM(ISNULL(A.HandledCallsTime,0)),
        HoldTime = SUM(ISNULL(A.HoldTime,0)),
        ReservedTime = SUM(ISNULL(A.ReservedStateTime,0)),
        WrapTime = SUM(ISNULL(A.WorkNotReadyTime + A.WorkReadyTime,0)),
        AnswerWaitTime = SUM(ISNULL(A.AnswerWaitTime,0)),
        ConsultativeCallsTime = SUM(ISNULL(A.ConsultativeCallsTime,0)),
        HandledCallsTalkTime = SUM(ISNULL(A.HandledCallsTalkTime,0)),
        InternalCallsRcvd = SUM(ISNULL(A.InternalCallsRcvd,0)),
        InternalCallsRcvdTime = SUM(ISNULL(A.InternalCallsRcvdTime,0)),
        InternalCalls = SUM(ISNULL(A.InternalCalls,0)),
        InternalCallsTime = SUM(ISNULL(A.InternalCallsTime,0)),
        TransferredInCallsTime = SUM(ISNULL(A.TransferredInCallsTime,0)),
        TalkOtherTime = SUM(ISNULL(A.TalkOtherTime,0)),
        TalkOutTime = SUM(ISNULL(A.TalkOutTime,0)),
        TransOut = SUM(ISNULL(A.TransferredOutCalls, 0) + ISNULL(A.NetTransferredOutCalls, 0))
    FROM
        (Select
            Agent_Skill_Group_Interval.*,
            SGPeripheralID = Skill_Group.PeripheralID,
            SGEnterpriseName = Skill_Group.EnterpriseName,
            SGSkillTargetID = Skill_Group.SkillTargetID
        FROM
            Skill_Group(nolock), Agent_Skill_Group_Interval(nolock), Media_Routing_Domain(nolock)  
        WHERE
            Skill_Group.SkillTargetID = Agent_Skill_Group_Interval.SkillGroupSkillTargetID
        AND
            Skill_Group.MRDomainID = Media_Routing_Domain.MRDomainID
        AND
            (Skill_Group.SkillTargetID NOT IN (SELECT BaseSkillTargetID FROM Skill_Group (nolock) WHERE (Priority > 0) AND (Deleted <> 'Y')))
    UNION ALL
        Select
            Agent_Skill_Group_Interval.*,
            SGPeripheralID = Skill_Group.PeripheralID,
            SGEnterpriseName = Precision_Queue.EnterpriseName,
            SGSkillTargetID = Skill_Group.SkillTargetID
        FROM
            Skill_Group (nolock), Agent_Skill_Group_Interval(nolock), Media_Routing_Domain(nolock), Precision_Queue(nolock)
        WHERE
            Skill_Group.PrecisionQueueID = Agent_Skill_Group_Interval.PrecisionQueueID
        AND
            Skill_Group.PrecisionQueueID = Precision_Queue.PrecisionQueueID
        AND
            Skill_Group.MRDomainID = Media_Routing_Domain.MRDomainID)A
        GROUP BY
            A.SGEnterpriseName,
            A.SGSkillTargetID,
            A.SkillTargetID,
            A.DateTime,      
            A.SGPeripheralID) ASGI
WHERE
    Agent.SkillTargetID = ASGI.SkillTargetID
AND
    ASGI.SGSkillTargetID <> 5000
AND
    Agent.PersonID = Person.PersonID
AND
    Agent.SkillTargetID =  AI.SkillTargetID
AND
    Agent.PeripheralID = ASGI.SGPeripheralID
AND
    ASGI.DateTime = AI.DateTime
AND
    Agent.SkillTargetID in (:AG_list)
AND
    AI.DateTime >= :StartDate
AND
    AI.DateTime < :EndDate
GROUP BY
    Agent.SkillTargetID,
    ASGI.SGEnterpriseName,
    ASGI.SGSkillTargetID,
    Person.LastName,
    Person.FirstName,
    Person.LoginName,
    CONVERT(char(10), AI.DateTime, 103)
ORDER BY
    Person.LastName + ',' + Person.FirstName,
    Agent.SkillTargetID,
    ASGI.SGEnterpriseName
   

Thanks for the help

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.

Actions

This Discussion