Report Dinition

[jmercado08]

Hello to all SQL experts,

Would you please help me alter my query below. This code I have my colleague created for me. I basically wanted to pull up the reason code from the agent event detail table. The query works fine and I think I am now ready to import this as a new report definition. But how do I change this code such that I will have the usual filter in CUIC? I hope to bring up the Relative Date Range and Absolute Date Range capability. Plus, the sql query pulls up record of everyone. Can I also get the filter for agents? I am sure this post

(https://supportforums.cisco.com/discussion/11530351/cuic-datetime-filter) but I really don't know how to incorporate this to my query below. Can somebody please help me.

I shall appreciate whatever help you can extend. Thanks very much in advanced

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON

SELECT Media = ASGI.Media,

Interval   = AI.DateTime,

           Year       = DATEPART(yy,AI.DateTime),

           Month      = DATEPART(mm,AI.DateTime),

           Week       = DATEPART(ww,AI.DateTime),

           DOY             = DATEPART(dy,AI.DateTime),

           DOW             = DATEPART(dw,AI.DateTime),

           Date       = CONVERT(char(10),AI.DateTime,101),

           FullName   = Person.LastName + ',' + Person.FirstName,

        AgentSkillID = Agent.SkillTargetID,

        SkillGroupName = ASGI.SGEnterpriseName,

     SkillGroupSkillTargetID = ASGI.SGSkillTargetID,

   AgentLoggedOnTime = SUM(ISNULL( AI.LoggedOnTime,0)),

     AgentAvailTime = SUM(ISNULL(AI.AvailTime, 0)),

       AgentNotReady = SUM(ISNULL(AI.NotReadyTime, 0)) ,

AgentBusyOtherTime = SUM(ISNULL(AI.LoggedOnTime - AI.AvailTime,0)),

        --Need to 're-sum' in order to make SQL think these are aggregate functions

                CallsAnswered = sum(ASGI.CallsAnswered),

                     CallsHandled = sum(ASGI.CallsHandled),

                AbandRingCalls = sum(ASGI.AbandRingCalls),

           AbandRingCallsTime = sum(ASGI.AbandRingCallsTime),

                   RedirectCalls = sum(ASGI.RedirectCalls),

              RedirectCallsTime = sum(ASGI.RedirectCallsTime),

                AbandonHoldCalls = sum(ASGI.AbandonHoldCalls),

                TransferInCalls = sum(ASGI.TransferInCalls),

                TransferOutCalls = sum(ASGI.TransferOutCalls),

              ConsultativeCalls = sum(ASGI.ConsultativeCalls),

              ConferenceInCalls = sum(ASGI.ConferenceInCalls),

           ConferenceOutCalls = sum(ASGI.ConferenceOutCalls),

                     OutExtnCalls = sum(ASGI.OutExtnCalls),

                     ShortCalls = sum(ASGI.ShortCalls),

                SupAssistCalls = sum(ASGI.SupAssistCalls),

                     BargeInCalls = sum(ASGI.BargeInCalls),

                InterceptCalls = sum(ASGI.InterceptCalls),

                     MonitorCalls = sum(ASGI.MonitorCalls),

                     WhisperCalls = sum(ASGI.WhisperCalls),

           EmergencyAssistCalls = sum(ASGI.EmergencyAssistCalls),

           SupAssistCallsTime = sum(ASGI.SupAssistCallsTime),

           AgentOutCallsOnHoldTime = sum(ASGI.AgentOutCallsOnHoldTime),

                   InCallsOnHold = sum(ASGI.InCallsOnHold),

              InCallsOnHoldTime = sum(ASGI.InCallsOnHoldTime),

                IntCallsOnHold = sum(ASGI.IntCallsOnHold),

           IntCallsOnHoldTime = sum(ASGI.IntCallsOnHoldTime),

                           TalkTime = sum(ASGI.TalkTime),

                HandledCallsTime = sum(ASGI.HandledCallsTime),

                           HoldTime = sum(ASGI.HoldTime),

                     --   AvailTime = sum(ASGI.AvailTime),

                     --NotReadyTime = sum(ASGI.NotReadyTime),

                     ReservedTime = sum(ASGI.ReservedTime),

                           WrapTime = sum(ASGI.WrapTime),

                -- BusyOtherTime = sum(ASGI.BusyOtherTime),

                AnswerWaitTime = sum(ASGI.AnswerWaitTime),

                     AutoOutCalls = sum(ASGI.AutoOutCalls),

                AutoOutCallsTime = sum(ASGI.AutoOutCallsTime),

           AutoOutCallsTalkTime = sum(ASGI.AutoOutCallsTalkTime),

           AutoOutCallsOnHold = sum(ASGI.AutoOutCallsOnHold),

           AutoOutCallsOnHoldTime = sum(ASGI.AutoOutCallsOnHoldTime),

                     PreviewCalls = sum(ASGI.PreviewCalls),

                PreviewCallsTime = sum(ASGI.PreviewCallsTime),

           PreviewCallsTalkTime = sum(ASGI.PreviewCallsTalkTime),

           PreviewCallsOnHold = sum(ASGI.PreviewCallsOnHold),

           PreviewCallsOnHoldTime = sum(ASGI.PreviewCallsOnHoldTime),

                     ReserveCalls = sum(ASGI.ReserveCalls),

                ReserveCallsTime = sum(ASGI.ReserveCallsTime),

           ReserveCallsTalkTime = sum(ASGI.ReserveCallsTalkTime),

           ReserveCallsOnHold = sum(ASGI.ReserveCallsOnHold),

           ReserveCallsOnHoldTime = sum(ASGI.ReserveCallsOnHoldTime),

                TalkAutoOutTime = sum(ASGI.TalkAutoOutTime),

                TalkPreviewTime = sum(ASGI.TalkPreviewTime),

                TalkReserveTime = sum(ASGI.TalkReserveTime),

AgentOutCallsTime= SUM(ISNULL(ASGI.AgentOutCallsTime, 0)),

           AgentOutCallsTalkTime = sum(ASGI.AgentOutCallsTalkTime),

           AgentTerminatedCalls = sum(ASGI.AgentTerminatedCalls),

                CallbackMessages = sum(ASGI.CallbackMessages),

           CallbackMessagesTime = sum(ASGI.CallbackMessagesTime),

           ConsultativeCallsTime = sum(ASGI.ConsultativeCallsTime),

           ConferencedInCallsTime = sum(ASGI.ConferencedInCallsTime),

           ConferencedOutCallsTime = sum(ASGI.ConferencedOutCallsTime),

           HandledCallsTalkTime = sum(ASGI.HandledCallsTalkTime),

              InternalCallsRcvd = sum(ASGI.InternalCallsRcvd),

           InternalCallsRcvdTime = sum(ASGI.InternalCallsRcvdTime),

                   InternalCalls = sum(ASGI.InternalCalls),

              InternalCallsTime = sum(ASGI.InternalCallsTime),

           TransferredInCallsTime = sum(ASGI.TransferredInCallsTime),

                   TalkOtherTime = sum(ASGI.TalkOtherTime),

                     TalkOutTime = sum(ASGI.TalkOutTime),

                           TimeZone = sum(ASGI.TimeZone),

                InterruptedTime = sum(ASGI.InterruptedTime),

                WorkNotReadyTime = sum(ASGI.WorkNotReadyTime),

                   WorkReadyTime = sum(ASGI.WorkReadyTime),

           NetConsultativeCalls = sum(ASGI.NetConsultativeCalls),

           NetConsultativeCallsTime = sum(ASGI.NetConsultativeCallsTime),

           NetConferencedOutCalls = sum(ASGI.NetConferencedOutCalls),

           NetConfOutCallsTime = sum(ASGI.NetConfOutCallsTime),

           NetTransferredOutCalls = sum(ASGI.NetTransferredOutCalls),

                     DbDateTime = max(ASGI.DbDateTime),

                                Assists = SUM(ASGI.Assists),

                           TransOut = SUM(ASGI.TransOut),

           AHT = ISNULL(SUM(ASGI.HandledCallsTime) / SUM(ASGI.CallsHandled),0),

     AHoldT = ISNULL(SUM(ASGI.InCallsOnHoldTime) / SUM(ASGI.InCallsOnHold),0),

     perActiveTime = SUM(ASGI.TalkTime) * 1.0 / SUM(ISNULL(AI.LoggedOnTime, 0)),

     perHoldTime = sum(ASGI.HoldTime) * 1.0 / SUM(ISNULL(AI.LoggedOnTime, 0)),

     perNotActive = ISNULL(SUM(AI.AvailTime) * 1.0 / SUM(AI.LoggedOnTime),0),

        perNotReady = ISNULL(SUM(AI.NotReadyTime) * 1.0 / SUM(AI.LoggedOnTime),0),

        perReserved = sum(ASGI.ReservedTime) * 1.0 / SUM(ISNULL(AI.LoggedOnTime, 0)),

          perWrapup = sum(ASGI.WrapTime) * 1.0 / SUM(ISNULL(AI.LoggedOnTime, 0)),

     perBusyOther = ISNULL(SUM(AI.LoggedOnTime - AI.AvailTime) * 1.0 / SUM(AI.LoggedOnTime),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),

     aed.ReasonCode

     FROM Agent (nolock),

           Agent_Interval AI (nolock),

           Person (nolock),

           --This nested Select statement is necessary in order to make AgentLoggedOnTime, AgentAvailTime, and AgentNotReadyTime work correctly

           (Select   Media      = A.Media,

                     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)),

                   RedirectCalls = SUM(ISNULL(A.RedirectNoAnsCalls,0)),

              RedirectCallsTime = SUM(ISNULL(A.RedirectNoAnsCallsTime,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)),

              ConferenceInCalls = SUM(ISNULL(A.ConferencedInCalls,0)),

           ConferenceOutCalls = SUM(ISNULL(A.ConferencedOutCalls,0)),

                     OutExtnCalls = SUM(ISNULL(A.AgentOutCalls,0)),

                     ShortCalls = SUM(ISNULL(A.ShortCalls,0)),

                SupAssistCalls = SUM(ISNULL(A.SupervAssistCalls,0)),

                     BargeInCalls = SUM(ISNULL(A.BargeInCalls,0)),

                InterceptCalls = SUM(ISNULL(A.InterceptCalls,0)),

                     MonitorCalls = SUM(ISNULL(A.MonitorCalls,0)),

                     WhisperCalls = SUM(ISNULL(A.WhisperCalls,0)),

           EmergencyAssistCalls = SUM(ISNULL(A.EmergencyAssists,0)),

           SupAssistCallsTime = SUM(ISNULL(A.SupervAssistCallsTime,0)),

           AgentOutCallsOnHoldTime = SUM(ISNULL(A.AgentOutCallsOnHoldTime,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)) +

                                        sum(isnull(A.TalkAutoOutTime,0)) +

                                        sum(isnull(A.TalkPreviewTime,0)) +

                                        sum(isnull(A.TalkReserveTime,0)),

                HandledCallsTime = SUM(ISNULL(A.HandledCallsTime,0)),

                           HoldTime = SUM(ISNULL(A.HoldTime,0)),

                     -- AvailTime = SUM(ISNULL(A.AvailTime,0)),

                     --NotReadyTime = SUM(ISNULL(A.NotReadyTime,0)),

                     ReservedTime = SUM(ISNULL(A.ReservedStateTime,0)),

                           WrapTime = SUM(ISNULL(A.WorkNotReadyTime + A.WorkReadyTime,0)),

                -- BusyOtherTime = SUM(ISNULL(A.BusyOtherTime,0)),

                AnswerWaitTime = SUM(ISNULL(A.AnswerWaitTime,0)),

                     AutoOutCalls = SUM(ISNULL(A.AutoOutCalls,0)),

                AutoOutCallsTime = SUM(ISNULL(A.AutoOutCallsTime,0)),

           AutoOutCallsTalkTime = SUM(ISNULL(A.AutoOutCallsTalkTime,0)),

           AutoOutCallsOnHold = SUM(ISNULL(A.AutoOutCallsOnHold,0)),

           AutoOutCallsOnHoldTime = SUM(ISNULL(A.AutoOutCallsOnHoldTime,0)),

                     PreviewCalls = SUM(ISNULL(A.PreviewCalls,0)),

                PreviewCallsTime = SUM(ISNULL(A.PreviewCallsTime,0)),

           PreviewCallsTalkTime = SUM(ISNULL(A.PreviewCallsTalkTime,0)),

           PreviewCallsOnHold = SUM(ISNULL(A.PreviewCallsOnHold,0)),

           PreviewCallsOnHoldTime = SUM(ISNULL(A.PreviewCallsOnHoldTime,0)),

                     ReserveCalls = SUM(ISNULL(A.ReserveCalls,0)),

                ReserveCallsTime = SUM(ISNULL(A.ReserveCallsTime,0)),

           ReserveCallsTalkTime = SUM(ISNULL(A.ReserveCallsTalkTime,0)),

           ReserveCallsOnHold = SUM(ISNULL(A.ReserveCallsOnHold,0)),

           ReserveCallsOnHoldTime = SUM(ISNULL(A.ReserveCallsOnHoldTime,0)),

                TalkAutoOutTime = SUM(ISNULL(A.TalkAutoOutTime,0)),

                TalkPreviewTime = SUM(ISNULL(A.TalkPreviewTime,0)),

                TalkReserveTime = SUM(ISNULL(A.TalkReserveTime,0)),

AgentOutCallsTime = SUM(ISNULL(A.AgentOutCallsTime,0)),

           AgentOutCallsTalkTime = SUM(ISNULL(A.AgentOutCallsTalkTime,0)),

           AgentTerminatedCalls = SUM(ISNULL(A.AgentTerminatedCalls,0)),

                CallbackMessages = SUM(ISNULL(A.CallbackMessages,0)),

           CallbackMessagesTime = SUM(ISNULL(A.CallbackMessagesTime,0)),

           ConsultativeCallsTime = SUM(ISNULL(A.ConsultativeCallsTime,0)),

           ConferencedInCallsTime = SUM(ISNULL(A.ConferencedInCallsTime,0)),

           ConferencedOutCallsTime = SUM(ISNULL(A.ConferencedOutCallsTime,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)),

                           TimeZone = MAX(A.TimeZone),

                InterruptedTime = SUM(ISNULL(A.InterruptedTime,0)),

                WorkNotReadyTime = SUM(ISNULL(A.WorkNotReadyTime,0)),

                   WorkReadyTime = SUM(ISNULL(A.WorkReadyTime,0)),

           NetConsultativeCalls = SUM(ISNULL(A.NetConsultativeCalls,0)),

           NetConsultativeCallsTime = SUM(ISNULL(A.NetConsultativeCallsTime,0)),

           NetConferencedOutCalls = SUM(ISNULL(A.NetConferencedOutCalls,0)),

           NetConfOutCallsTime = SUM(ISNULL(A.NetConfOutCallsTime,0)),

           NetTransferredOutCalls = SUM(ISNULL(A.NetTransferredOutCalls,0)),

                     DbDateTime = MAX(A.DbDateTime),

                     TransOut = SUM(ISNULL(A.TransferredOutCalls, 0) + ISNULL(A.NetTransferredOutCalls, 0)),

                     Assists = SUM(ISNULL(A.EmergencyAssists, 0) + ISNULL(A.SupervAssistCallsTime, 0))

                     FROM (Select Agent_Skill_Group_Interval.*, SGPeripheralID = Skill_Group.PeripheralID, SGEnterpriseName = Skill_Group.EnterpriseName, SGSkillTargetID = Skill_Group.SkillTargetID, Media = Media_Routing_Domain.EnterpriseName 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, Media = Media_Routing_Domain.EnterpriseName

           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.Media,

                     A.DateTime,         

                     A.SGPeripheralID) ASGI,

                     (SELECT

SkillTargetID,

ReasonCode

FROM dbo.Agent_Event_Detail

WHERE DateTime >= '2016-01-26 00:00:00'

     AND DateTime < '2016-01-26 23:59:59'

     AND ReasonCode = '55') aed

   WHERE Agent.SkillTargetID = ASGI.SkillTargetID

     AND Agent.SkillTargetID = aed.SkillTargetID

     AND Agent.PersonID = Person.PersonID

     AND Agent.SkillTargetID = AI.SkillTargetID

         AND Agent.PeripheralID = ASGI.SGPeripheralID

     AND ASGI.DateTime = AI.DateTime

     AND Agent.SkillTargetID in (9438,9440,9484,9441,9442,9443,9444,9439,9445,9446,9447,9448,9449,9451,9452,9453,9464,9454,9455,9456,9457,9458,9733,9459,9460,9461,9462,9463,9465,9466,9467,9468,9469,9450,9473,9474,9475,9704,9478,9479,9698,9697,9481,9482,9483,9486,9487,9488,9489,9490,9729,9491,9495,9496,9499,9502,9550,9504,9505,9506,9507,9509,9510,9511,9512,9513,9514,9516,9517,9519,9520,9521,9522,9523,9524,9525,9526,9530,9532,9533,9534,9537,9538,9539,9540,9541,9542,9547,9548,9549,9515,9503,9551,9552,9554,9555,9556,9557,9558,9559,9560,9561,9563,9564,9566,9567,9568,9569,9570,9571,9573,9574,9575,9576,9577,9578,9579,9580,9581,9477,9584,9585,9586,9500,9587,9553,9589,9590,9591,9593,9594,9595,9596,9597,9598,9599,9601,9603,9604,9606,9607,9610,9611,9612,9613,9614,9615,9619,9620,9621,9622,9623,9728,9627,9628,9630,9631,9632,9633,9635,9637,9638,9640,9641,9699,9642,9643,9644,9645,9646,9647,9648,9649,9650,9651,9652,9653,9654,9655,9656,9657,9658,9660,9659,9661,9663,9664,9665,9666,9667,9668,9669,9670,9671,9672,9673,9674,9675,9676,9677,9679,9680,9681,9678,9682,9683,9592,9684,9470,9471,9472,9476,9485,9493,9501,9508,9518,9527,9528,9529,9531,9536,9543,9544,9545,9565,9583,9602,9605,9609,9616,9618,9624,9626,9629,9639,9662,9694,9695,9696,9685,9686,9687,9688,9689,9690,9691,9692,9693,9494,9582,9636)

     AND AI.DateTime >= '2016-01-26 00:00:00'

     AND AI.DateTime < '2016-01-26 23:59:59'

GROUP BY Agent.SkillTargetID,

           ASGI.SGEnterpriseName,

           ASGI.SGSkillTargetID,

           Person.LastName,

           Person.FirstName,

           ASGI.Media,

           AI.DateTime,

           DATEPART(yy,AI.DateTime) ,

           DATEPART(mm,AI.DateTime) ,

           DATEPART(ww,AI.DateTime) ,

           DATEPART(dy,AI.DateTime),

           DATEPART(dw,AI.DateTime)

           ,aed.ReasonCode

ORDER BY Person.LastName + ',' + Person.FirstName,

           ASGI.Media,

           Agent.SkillTargetID,

           ASGI.SGEnterpriseName,

           AI.DateTime

[Deepak Rawat]

It would be worth posting this at the below dedicated CUIC Reporting Developer forum as well while waiting here for an answer:

https://communities.cisco.com/community/developer/reporting

Regards

Deepak

[jmercado08]

Thanks Deepak, let me post it there, too. Thanks for the information.