I'm currently running into an issue I hope you can help me with. I need to roll-up data to a daily format being produced every 15mins. Here's the code I am using first:
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 2Here's the result:
As you can see, the Logged On Time, Not Ready Time and Available Time per agent values are getting repeated since this is tied to the AgentID. This view is not telling the truth. Agent was logged for 900secs in the 17:45 interval and again 900 secs for the 18:00 interval. If I were to sum this now, it would give me a result of 9,000 secs which is incorrect for Logged On Time. Not Ready Time and Available Time are also incorrect.
If I remove SkillID from the select statement, my values reflect correctly
Without skills being represented
This time, I am getting 900 secs for 17:45 and 900 as well for 18:00 which is correct.
Where I'm having issues is rolling this back up to an entire day.
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 2Now my logged on time is 1500 secs which is 300secs less than reality.
How can I fix this so that when I roll-up data to the day,I get the accurate logged on time, not ready time and available time in my report?
Thanks for your help!
Found the solution to my problem by modifying my code in the following fashion.
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)
By giving a rank to each interval, I can now accurately report on my logged_on_time, avail_time and not_ready_time.