Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

CUIC - Problem with order by clause

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

23
Views
0
Helpful
0
Replies