如何避免对间隔中的重复值求和

StephB 发表于 Dev

斯蒂芬

我目前遇到了一个问题，希望您能帮助我。我需要将数据汇总为每天每15分钟产生一次的格式。这是我首先使用的代码：

SELECT TOP 1000
agi.date_time as [Date],
AGI.AGENT_URN as AgentID,
asgi.skillgroup_urn as SkillID,
count(distinct(asgi.skillgroup_urn)) as [# of skills],
sum(asgi.CALLS_HANDLED) as [Calls Handled In],
sum(agi.LOGGED_ON_TIME)/count(DISTINCT(asgi.SKILLGROUP_URN)) as [Logged On Time (Per Agent)],
sum(agi.NOT_READY_TIME)/count(DISTINCT(asgi.SKILLGROUP_URN)) as [Not Ready Time (Per Agent)],
sum(agi.AVAIL_TIME)/count(DISTINCT(asgi.SKILLGROUP_URN)) as [Available Time (Per Agent)]

FROM AGENT_INTERVAL AGI
LEFT OUTER JOIN AGENT_SKILLGROUP_INTERVAL ASGI ON AGI.AGENT_URN = ASGI.AGENT_URN AND AGI.DATE_TIME = ASGI.DATE_TIME


WHERE 1=1
AND AGI.DATE_TIME between '2018-07-26 16:15:00 ' and '2018-07-26 16:45:59'

GROUP BY 
AGI.DATE_TIME,
AGI.AGENT_URN,
asgi.skillgroup_urn

ORDER BY 2

结果如下：

日期范围

如您所见，每个代理的“登录时间”，“未就绪时间”和“可用时间”值正在重复，因为这与AgentID相关。这种观点没有说实话。代理以17:45的间隔记录了900秒，然后以18:00的间隔记录了900秒。如果我现在将其相加，它将给我9,000秒的结果，这对于Log on On Time是不正确的。未就绪时间和可用时间也不正确。

如果我从选择语句中删除了SkillID，则我的值会正确反映

没有技能代表

这次，我在17:45获得900秒，在18:00获得900秒，这是正确的。

我遇到问题的地方是将其回滚到一整天。

SELECT TOP 1000
cast(agi.date_time as date) as [Date],
AGI.AGENT_URN as AgentID,
asgi.skillgroup_urn as SkillID,
count(distinct(asgi.skillgroup_urn)) as [# of skills],
sum(asgi.CALLS_HANDLED) as [Calls Handled In],
sum(agi.LOGGED_ON_TIME)/count(DISTINCT(asgi.SKILLGROUP_URN)) as [Logged On Time (Per Agent)],
sum(agi.NOT_READY_TIME)/count(DISTINCT(asgi.SKILLGROUP_URN)) as [Not Ready Time (Per Agent)],
sum(agi.AVAIL_TIME)/count(DISTINCT(asgi.SKILLGROUP_URN)) as [Available Time (Per Agent)]

FROM AGENT_INTERVAL AGI
LEFT OUTER JOIN AGENT_SKILLGROUP_INTERVAL ASGI ON AGI.AGENT_URN = ASGI.AGENT_URN AND AGI.DATE_TIME = ASGI.DATE_TIME


WHERE 1=1
AND AGI.DATE_TIME between '2018-07-26 16:15:00 ' and '2018-07-26 16:45:59'

GROUP BY 
cast(AGI.DATE_TIME as date),
AGI.AGENT_URN,


ORDER BY 2

数据汇总到一天

现在我的登录时间为1500秒，比实际时间少300秒。

如何解决此问题，以便当我将数据汇总到一天时，我可以在报告中获得准确的登录时间，而不是准备时间和可用时间？

谢谢你的帮助！

每项技能的每日观看次数

斯蒂芬

通过以下列方式修改代码来找到解决问题的方法。

select 
AGI.*
,ROW_NUMBER() OVER (PARTITION BY DATE_TIME,AGENT_URN ORDER BY DATE_TIME) AS SEQNUM 
from AGENT_SKILLGROUP_INTERVAL AGI

)

SELECT 

CAST(AI.DATE_TIME AS DATE) [Date]
,SUM(AI.CALLS_HANDLED) AS [Calls Handled In]
,SUM(AI.HANDLED_CALLS_TALK_TIME)/nullif(sum(AI.calls_handled),0) as [Avg. Talk Time In]
,SUM(AI.INCOMING_CALLS_ON_HOLD_TIME)/nullif(sum(AI.calls_handled),0) as [Avg. Hold Time In]
,SUM(AI.HANDLED_CALLS_TIME-(AI.HANDLED_CALLS_TALK_TIME + AI.INCOMING_CALLS_ON_HOLD_TIME))/nullif(sum(AI.calls_handled),0) as [Avg. Wrap Time In]
,SUM(AI.[HANDLED_CALLS_TIME])/nullif(sum(AI.calls_handled),0) as [Avg. Handle Time In]
,SUM(CASE WHEN SEQNUM = 1 THEN AI.LOGGED_ON_TIME END) AS [Logged On Time (Per Agent)]
,SUM(ai.HANDLED_CALLS_TALK_TIME) as [Talk Time In]
,SUM(ai.HANDLED_CALLS_TALK_TIME + ai.INCOMING_CALLS_ON_HOLD_TIME) as [Phone Time In]
,SUM(ai.[INCOMING_CALLS_ON_HOLD_TIME]) as [Hold Time In]
,SUM(ai.HANDLED_CALLS_TIME-(ai.HANDLED_CALLS_TALK_TIME + ai.INCOMING_CALLS_ON_HOLD_TIME) ) AS [Wrap Time In]
,SUM(CASE WHEN SEQNUM = 1 THEN AI.NOT_READY_TIME END) as [Not Ready Time (Per Agent)]
,SUM(CASE WHEN SEQNUM = 1 THEN AI.AVAIL_TIME END) as [Available Time (Per Agent)]

FROM Agent_Interval AI


group by 
CAST(AI.DATE_TIME AS DATE)

通过给每个时间间隔一个等级，我现在可以准确地报告我的logging_on_time，avail_time和not_ready_time。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。