CUIC - Different Data Results

pingaunis · ‎01-08-2020

So short story, CUIC ver 10.5. Someone previously created a custom report based off Agent Skill Weekly Historical. They made some minor tweaks to the report definition. The report works with any dates were the week includes only 2019 or 2020 data. If it goes from 2019 to 2020 (ex. 12/30/2019 - 1/5/2020) the report doesn't tally right. When I say work each agent has all the data tallied neatly for every Skill for that week. When it doesn't there are multiple lines with week starting 1/1/2020. Here's the SQL statement. Any suggestions are appreciated. I'm sorry I never learned SQL.

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
SET DATEFIRST 1; <---Added I assume to start the week on Monday
SELECT Media = ASGI.Media,
Week = CASE WHEN DATEPART(week,ASGI.DateTime) = 1
THEN DATEADD(day,-(DATEPART(dayofyear,ASGI.DateTime)-1),ASGI.DateTime)
ELSE DATEADD(day,-(DATEPART(weekday,ASGI.DateTime)-1),DATEADD(day,DATEDIFF(day,0,ASGI.DateTime),0)) END,

~~Year = DATEPART(yy, ASGI.DateTime),~~ <-----Removed Not Sure why
~~Month = DATEPART(mm, ASGI.DateTime),~~ <-----Removed Not Sure why
FullName = Person.LastName + ', ' + Person.FirstName,
SkillGroupName = ASGI.SGEnterpriseName,
AgentSkillID = Agent.SkillTargetID,
SkillGroupID = ASGI.SGSkillTargetID ,
CallsAnswered = SUM(ISNULL(ASGI.CallsAnswered, 0)),
CallsHandled = SUM(ISNULL(ASGI.CallsHandled, 0)),
AbandRingCalls = SUM(ISNULL(ASGI.AbandonRingCalls, 0)),
AbandRingCallsTime = SUM(ISNULL(ASGI.AbandonRingTime, 0)),
RedirectCalls = SUM(ISNULL(ASGI.RedirectNoAnsCalls, 0)),
RedirectCallsTime = SUM(ISNULL(ASGI.RedirectNoAnsCallsTime, 0)),
AbandonHoldCalls = SUM(ISNULL(ASGI.AbandonHoldCalls, 0)),
TransferInCalls = SUM(ISNULL(ASGI.TransferredInCalls, 0)),
TransferOutCalls = SUM(ISNULL(ASGI.TransferredOutCalls, 0)),
ConsultativeCalls = SUM(ISNULL(ASGI.ConsultativeCalls, 0)),
ConferenceInCalls = SUM(ISNULL(ASGI.ConferencedInCalls, 0)),
ConferenceOutCalls = SUM(ISNULL(ASGI.ConferencedOutCalls, 0)),
OutExtnCalls = SUM(ISNULL(ASGI.AgentOutCalls, 0)),
ShortCalls = SUM(ISNULL(ASGI.ShortCalls, 0)),
SupAssistCalls = SUM(ISNULL(ASGI.SupervAssistCalls, 0)),
BargeInCalls = SUM(ISNULL(ASGI.BargeInCalls, 0)),
InterceptCalls = SUM(ISNULL(ASGI.InterceptCalls, 0)),
MonitorCalls = SUM(ISNULL(ASGI.MonitorCalls, 0)),
WhisperCalls = SUM(ISNULL(ASGI.WhisperCalls, 0)),
EmergencyAssistCalls= SUM(ISNULL(ASGI.EmergencyAssists, 0)),
SupAssistCallsTime = SUM(ISNULL(ASGI.SupervAssistCallsTime, 0)),
AgentOutCallsOnHold = SUM(ISNULL(ASGI.AgentOutCallsOnHold, 0)),
AgentOutCallsOnHoldTime= SUM(ISNULL(ASGI.AgentOutCallsOnHoldTime, 0)),
InCallsOnHold = SUM(ISNULL(ASGI.IncomingCallsOnHold, 0)),
InCallsOnHoldTime = SUM(ISNULL(ASGI.IncomingCallsOnHoldTime, 0)),
IntCallsOnHold = SUM(ISNULL(ASGI.InternalCallsOnHold, 0)),
IntCallsOnHoldTime = SUM(ISNULL(ASGI.InternalCallsOnHoldTime, 0)),
TalkTime = sum(isnull(ASGI.TalkInTime,0)) +
sum(isnull(ASGI.TalkOutTime,0)) +
sum(isnull(ASGI.TalkOtherTime,0)) +
sum(isnull(ASGI.TalkAutoOutTime,0)) +
sum(isnull(ASGI.TalkPreviewTime,0)) +
sum(isnull(ASGI.TalkReserveTime,0)),
HandledCallsTime = SUM(ISNULL(ASGI.HandledCallsTime, 0)),
LoggedOnTime = SUM(ISNULL(ASGI.LoggedOnTime, 0)),
HoldTime = SUM(ISNULL(ASGI.HoldTime, 0)),
-- AvailTime = SUM(ISNULL(ASGI.AvailTime, 0)),
--NotReadyTime = SUM(ISNULL(ASGI.NotReadyTime, 0)),
ReservedTime = SUM(ISNULL(ASGI.ReservedStateTime, 0)),
WrapTime = SUM(ISNULL(ASGI.WorkNotReadyTime + ASGI.WorkReadyTime, 0)),
--BusyOtherTime = SUM(ISNULL(ASGI.BusyOtherTime, 0)),
AnswerWaitTime = SUM(ISNULL(ASGI.AnswerWaitTime, 0)),
AutoOutCalls = SUM(ISNULL(ASGI.AutoOutCalls, 0)),
AutoOutCallsTime = SUM(ISNULL(ASGI.AutoOutCallsTime, 0)),
AutoOutCallsTalkTime= SUM(ISNULL(ASGI.AutoOutCallsTalkTime, 0)),
AutoOutCallsOnHold = SUM(ISNULL(ASGI.AutoOutCallsOnHold, 0)),
AutoOutCallsOnHoldTime= SUM(ISNULL(ASGI.AutoOutCallsOnHoldTime, 0)),
PreviewCalls = SUM(ISNULL(ASGI.PreviewCalls, 0)),
PreviewCallsTime = SUM(ISNULL(ASGI.PreviewCallsTime, 0)),
PreviewCallsTalkTime= SUM(ISNULL(ASGI.PreviewCallsTalkTime, 0)),
PreviewCallsOnHold = SUM(ISNULL(ASGI.PreviewCallsOnHold, 0)),
PreviewCallsOnHoldTime= SUM(ISNULL(ASGI.PreviewCallsOnHoldTime, 0)),
ReserveCalls = SUM(ISNULL(ASGI.ReserveCalls, 0)),
ReserveCallsTime = SUM(ISNULL(ASGI.ReserveCallsTime, 0)),
ReserveCallsTalkTime= SUM(ISNULL(ASGI.ReserveCallsTalkTime, 0)),
ReserveCallsOnHold = SUM(ISNULL(ASGI.ReserveCallsOnHold, 0)),
ReserveCallsOnHoldTime= SUM(ISNULL(ASGI.ReserveCallsOnHoldTime, 0)),
TalkAutoOutTime = SUM(ISNULL(ASGI.TalkAutoOutTime, 0)),
TalkPreviewTime = SUM(ISNULL(ASGI.TalkPreviewTime, 0)),
TalkReserveTime = SUM(ISNULL(ASGI.TalkReserveTime, 0)),
AgentOutCallsTime= SUM(ISNULL(ASGI.AgentOutCallsTime, 0)),
AgentOutCallsTalkTime= SUM(ISNULL(ASGI.AgentOutCallsTalkTime, 0)),
AgentTerminatedCalls= SUM(ISNULL(ASGI.AgentTerminatedCalls, 0)),
CallbackMessages = SUM(ISNULL(ASGI.CallbackMessages, 0)),
CallbackMessagesTime= SUM(ISNULL(ASGI.CallbackMessagesTime, 0)),
ConsultativeCallsTime= SUM(ISNULL(ASGI.ConsultativeCallsTime, 0)),
ConferencedInCallsTime= SUM(ISNULL(ASGI.ConferencedInCallsTime, 0)),
ConferencedOutCallsTime= SUM(ISNULL(ASGI.ConferencedOutCallsTime, 0)),
HandledCallsTalkTime= SUM(ISNULL(ASGI.HandledCallsTalkTime, 0)),
InternalCallsRcvd = SUM(ISNULL(ASGI.InternalCallsRcvd, 0)),
InternalCallsRcvdTime= SUM(ISNULL(ASGI.InternalCallsRcvdTime, 0)),
InternalCalls = SUM(ISNULL(ASGI.InternalCalls, 0)),
InternalCallsTime = SUM(ISNULL(ASGI.InternalCallsTime, 0)),
TransferredInCallsTime= SUM(ISNULL(ASGI.TransferredInCallsTime, 0)),
TalkOtherTime = SUM(ISNULL(ASGI.TalkOtherTime, 0)),
TalkOutTime = SUM(ISNULL(ASGI.TalkOutTime, 0)),
TimeZone = MAX(ASGI.TimeZone),
InterruptedTime = SUM(ISNULL(ASGI.InterruptedTime, 0)),
WorkNotReadyTime = SUM(ISNULL(ASGI.WorkNotReadyTime, 0)),
WorkReadyTime = SUM(ISNULL(ASGI.WorkReadyTime, 0)),
NetConsultativeCalls= SUM(ISNULL(ASGI.NetConsultativeCalls, 0)),
NetConsultativeCallsTime= SUM(ISNULL(ASGI.NetConsultativeCallsTime, 0)),
NetConferencedOutCalls= SUM(ISNULL(ASGI.NetConferencedOutCalls, 0)),
NetConfOutCallsTime = SUM(ISNULL(ASGI.NetConfOutCallsTime, 0)),
NetTransferredOutCalls= SUM(ISNULL(ASGI.NetTransferredOutCalls, 0)),
DbDateTime = MAX(ASGI.DbDateTime),
--AgentLoggedOnTime = sum(ISNULL( AI.LoggedOnTime,0)),
--AgentAvailTime = SUM(ISNULL(AI.AvailTime, 0)),
--AgentNotReady = SUM(ISNULL(AI.NotReadyTime, 0)) ,
AHT = ISNULL(SUM(ASGI.HandledCallsTime) / SUM(ASGI.CallsHandled), 0),
AHoldT = ISNULL(SUM(ASGI.IncomingCallsOnHoldTime) / SUM(ASGI.IncomingCallsOnHold), 0),
-- perActiveTime = ISNULL(sum(isnull(ASGI.TalkInTime,0)) +
-- sum(isnull(ASGI.TalkOutTime,0)) +
-- sum(isnull(ASGI.TalkOtherTime,0)) +
-- sum(isnull(ASGI.TalkAutoOutTime,0)) +
-- sum(isnull(ASGI.TalkPreviewTime,0)) +
-- sum(isnull(ASGI.TalkReserveTime,0)),0) * 1.0 /
-- SUM(ISNULL(ASGI.LoggedOnTime, 0)),
-- perHoldTime = ISNULL(SUM(ASGI.HoldTime) * 1.0 / SUM(ASGI.LoggedOnTime), 0),
-- perNotActive = ISNULL(SUM(ASGI.AvailTime) * 1.0 / SUM(ASGI.LoggedOnTime), 0),
-- perNotReady = ISNULL(SUM(ASGI.NotReadyTime) * 1.0 / SUM(ASGI.LoggedOnTime), 0),
-- perReserved = ISNULL(SUM(ASGI.ReservedStateTime) * 1.0 / SUM(ASGI.LoggedOnTime), 0),
-- perWrapup = ISNULL(SUM(ASGI.WorkNotReadyTime + ASGI.WorkReadyTime) * 1.0 / SUM(ASGI.LoggedOnTime), 0),
-- perBusyOther = ISNULL(SUM(ASGI.BusyOtherTime) * 1.0 / SUM(ASGI.LoggedOnTime), 0),
TransOut = SUM(ISNULL(ASGI.TransferredOutCalls, 0) + ISNULL(ASGI.NetTransferredOutCalls, 0)),
Assists = SUM(ISNULL(ASGI.EmergencyAssists, 0) + ISNULL(ASGI.SupervAssistCallsTime, 0))
-- AACW = ISNULL((SUM(ISNULL(ASGI.WorkNotReadyTime + ASGI.WorkReadyTime, 0))) / SUM(isnull(ASGI.CallsHandled, 0)), 0),
--perWACW = ISNULL(((sum(isnull(ASGI.TalkPreviewTime,0)) + sum(isnull(ASGI.TalkReserveTime,0))) + (SUM(ISNULL(ASGI.WorkNotReadyTime + ASGI.WorkReadyTime, 0))) + SUM(ISNULL(ASGI.NotReadyTime,0)) )* 1.0 / SUM(ISNULL(ASGI.LoggedOnTime,0)), 0),
--perWOACW = ISNULL(((sum(isnull(ASGI.TalkPreviewTime,0)) + sum(isnull(ASGI.TalkReserveTime,0))) + (SUM(ISNULL(ASGI.WorkNotReadyTime + ASGI.WorkReadyTime, 0))) )* 1.0 / SUM(ISNULL(ASGI.LoggedOnTime,0)), 0)
FROM Agent (nolock),
(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.EnterpriseName not like '%Voice.defau%'
AND Skill_Group.PrecisionQueueID = Precision_Queue.PrecisionQueueID
AND Skill_Group.MRDomainID = Media_Routing_Domain.MRDomainID)ASGI,
Agent_Interval AI (nolock),
Person (nolock)
WHERE Agent.PeripheralID = ASGI.SGPeripheralID
AND Agent.SkillTargetID = ASGI.SkillTargetID
AND Agent.PersonID = Person.PersonID
AND Agent.SkillTargetID= AI.SkillTargetID
AND ASGI.SGEnterpriseName not like '%Voice.defau%'
AND ASGI.DateTime = AI.DateTime
AND Agent.SkillTargetID in (9656,11669,11537,11781,11715,9760,8249,11721,11667,9641,11782,11731,11718,11187,11518,11639,11181,8252,8253,11739,10768,11660,11485,11149,11674,11675,8263,11653,11678,11716,11714,11733,11736,11649,10197,8266,11742,11783,11725,10447,11784,10696,11657,11785,11677,11786,8271,11661,11787,11717,11499,11557,9943,8273,10930,11788,9467,9095,11159,11740,11789,11673,11434,11790,11746,10199,11327,11734,11765,11719,11791,11735,11189,11792,11793,9771,11663,11664,9259,10973,11743,11167,11668,11502,11722,9470,11646,8281,11140,8596,8246,11655,11644,8287,11642,11662,11794,11641,11726,11640,8280,11647,11745,11795,11648,11404,11443,11744,11332,8303,11638,11796,11507,11163,11521,9675,11681,8305,11651,11658,11797,9783,11652,11676,8308,8310,11335,11650,11145,11320,11146,11194,9709,9786,11637,11164,11723,11520,11741,11724,8321,11509,11671,9266,11732,11679,11147,11720,8325,11730,11737,11738,9654,8328,11536,11659,11656,11643,11798,11713,9725,11070,11799,11682,9468,9109,9047,11672,11665,11680,11645,11666,9840,11800,10979,11636,11670,11801)
AND AI.DateTime >= '2020-01-01 00:00:00'
AND AI.DateTime < '2020-01-05 23:59:59'
GROUP BY Agent.SkillTargetID,
ASGI.SGSkillTargetID,
Person.LastName,
Person.FirstName,
ASGI.SGEnterpriseName,
ASGI.Media,
CASE WHEN DATEPART(week,ASGI.DateTime) = 1
THEN DATEADD(day,-(DATEPART(dayofyear,ASGI.DateTime)-1),ASGI.DateTime)
ELSE DATEADD(day,-(DATEPART(weekday,ASGI.DateTime)-1),DATEADD(day,DATEDIFF(day,0,ASGI.DateTime),0)) END

~~DATEPART(yy, ASGI.DateTime) ,~~ <-----Removed Not Sure why
~~DATEPART(mm, ASGI.DateTime)~~ <-----Removed Not Sure why

ORDER BY Person.LastName + ', ' + Person.FirstName,
ASGI.Media,
Agent.SkillTargetID,
ASGI.SGEnterpriseName