Previous:
Busy = sum(Dialing + WaitingFor + Busy + BackOffice + HandlingOutgoingInteraction)
TotalHandlingOutgoingTime = sum(HandlingOutgoingInteraction)
BackOffice:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Backoffice - Administrative')
HandlingOutgoingInteraction:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Handling - Outgoing Interactions')
Current:
Busy = sum(Dialing + WaitingFor + Busy + BackOfficeBusy + HandlingOutgoingInteractionBusy)
TotalHandlingOutgoingTime = sum(HandlingOutgoingInteractionIdle)
BackOfficeIdle:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Backoffice - Administrative')
and F_AgentLife.InteractionId is null
BackOfficeBusy:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Backoffice - Administrative')
and F_AgentLife.InteractionId is not null
HandlingOutgoingInteractionIdle:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Handling - Outgoing Interactions')
and F_AgentLife.InteractionId is null
HandlingOutgoingInteractionBusy:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Handling - Outgoing Interactions')
and F_AgentLife.InteractionId is not null
Previous:
Busy = sum(Dialing + WaitingFor + Busy + BackOffice + HandlingOutgoingInteraction)
BackOffice = sum(BackOffice)
TotalHandlingOutgoingTime = sum(HandlingOutgoingInteraction)
BackOffice:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Backoffice - Administrative')
HandlingOutgoingInteraction:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Handling - Outgoing Interactions')
Current:
Busy = sum(Dialing + WaitingFor + Busy + BackOfficeBusy + HandlingOutgoingInteractionBusy)
BackOffice = sum(BackOfficeIdle)
HandlingOutgoingInteraction = sum(HandlingOutgoingInteractionIdle)
BackOfficeIdle:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Backoffice - Administrative')
and F_AgentLife.InteractionId is null
BackOfficeBusy:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Backoffice - Administrative')
and F_AgentLife.InteractionId is not null
HandlingOutgoingInteractionIdle:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Handling - Outgoing Interactions')
and F_AgentLife.InteractionId is null
HandlingOutgoingInteractionBusy:
sum(case
when F_AgentLife.starttime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, F_AgentLife.EndTime)
when F_AgentLife.StartTime >= D_TimeSlice.tBeginTime and F_AgentLife.EndTime > D_TimeSlice.tEndTime
then datediff(second, F_AgentLife.StartTime, D_TimeSlice.tEndTime)
when F_AgentLife.StartTime < D_TimeSlice.tBeginTime and F_AgentLife.EndTime <= D_TimeSlice.tEndTime
then datediff(second, D_TimeSlice.tBeginTime, F_AgentLife.EndTime)
else datediff(second, D_TimeSlice.tBeginTime, D_TimeSlice.tEndTime)
end)
F_AgentLife.Status in('Handling - Outgoing Interactions')
and F_AgentLife.InteractionId is not null
procedures changed:
DW.spRpt_AgentInteractionSummaryReport
dbo.spRpt_GetAgentStatusesDataset
new source F_AgentAssignments
Agent Name - F_AgentLife.AgentName -> F_AgentAssignments.AgentName(Deleted) (Deleted) will added if D_Agent.Disabled = 1
BP Name - F_AgentLife.BusinessProcessName -> F_AgentAssignments.BusinessProcessName
Start Time(new field) - F_AgentAssignments.StartTime
End Time(new field) - F_AgentAssignments.EndTime
Changed By - F_AgentLife.SupervisorName -> F_AgentAssignments.ModifiedBy
logic added for new parametes AddCurrentAssignments, AddLastEndedAssignments
where
(@AddCurrentAssignments = 1 and F_AgentAssignments.EndTime is null)
or
(@AddCurrentAssignments = 0 and F_AgentAssignments.StartTime between @StartTime and @EndTime)
or
(
@AddLastEndedAssignments = 1
and exists (max(F_AgentAssignments.EndTime) where F_AgentAssignments.EndTime < @StartTime group by F_AgentAssignments.Agent)
)
procedures changed:
DW.spRpt_AgentAssignmentToBP
new procedures:
DW.spRpt_Common_Prm_ChangedBy - fill parameter values from F_AgentAssignments.ModifiedBy
Initiator - PendingTaskManager.dbo.DelegatedInteraction.InitiatorName -> F_InteractionDelegated.InitiatorName
Converser - PendingTaskManager.dbo.DelegatedInteraction.Converser -> F_InteractionDelegated.Converser
Entry Time - DateAdd(mi, PendingTaskManager.dbo.DelegatedInteraction.EntryTimeTicks * 1440, '1900-01-01') -> F_InteractionDelegated.EntryDateTime
Interaction Type - PendingTaskManager.dbo.DelegatedInteraction.InteractionTypeName -> F_InteractionDelegated.InteractionTypeName
Business Process - PendingTaskManager.dbo.DelegatedInteraction.BusinessProcessName -> F_InteractionDelegated.BusinessProcessName
Last State - PendingTaskManager.dbo.DelegatedInteraction.LastState -> F_InteractionDelegated.DelegatedState
Campaign Name - PendingTaskManager.dbo.DelegatedInteraction.InteractionCampaignName -> F_InteractionDelegated.CampaignName
Closing Time - DateAdd(mi, PendingTaskManager.dbo.DelegatedInteraction.UpdateTimeTicks * 1440, '1900-01-01') -> F_InteractionDelegated.UpdateDateTime
Agent Remark - PendingTaskManager.dbo.DelegatedInteraction.DelegateDetails -> F_InteractionDelegated.Details
Supervisor Remark - PendingTaskManager.dbo.DelegatedInteraction.SupervisorCloseMessage -> F_InteractionDelegated.SupervisorCloseMessage
Customer ID - PendingTaskManager.dbo.DelegatedInteraction.CustomerID -> F_InteractionDelegated.CustomerID
procedures changed:
DW.spRpt_DelegatedInteractions
Completed Callbacks(logic that has been added to an existing one):
if exists
count(distinct F_Interactions.InteractionId) > 1 group by InteractionGUIDID
and F_Callbacks.LastInteractionStateID in ('Purged', 'Canceled')
then count as completed
procedures changed:
dbo.spRpt_GetCallbacksDataset
dbo.spRpt_GetInteractionsDataset
fc.LastInteractionStateID
procedures changed:
DW.spRpt_BusinessProcessCallbackPerformance
DW.spRpt_BusinessProcessCallbackPerformance_SubDs1
dbo.spRpt_GetCallbacksDataset
dbo.spRpt_GetInteractionsDataset
CallbackInteractionGUIDID:
F_Callbacks.InteractionGUIDID
InteractionGUIDID:
F_Callbacks.InteractionGUIDID
Overflowed:
F_Callbacks.Overflowed
BusinessProcessInitiated:
F_Callbacks.RequestedBusinessProcess
procedures changed:
DW.spRpt_CompletedCallbackInteraction