SUM information but retain ResourceID (EmployeeNum)

Question

In the linked sample below you will see sample telephony data from Cisco UCCX Express from the agentconnectiondetail table.

This is the query I am running:

SELECT *
FROM agentconnectiondetail
WHERE startdatetime >= TO_DATE('01-01-2015 05:00','%m-%d-%Y %H:%M')
  AND sessionseqnum = 0

My question is how can I make the information unique to the SessionID which is the unique identifier for the call but retain the employeenumber of the agent who answered the call?

For example I highlighted in the document on the sample tab the sessionID 43000086306. The data shows that for sessionID 43000086306 the call came in and rang at two agent positions before it was picked up by the third. You see this because the resourceID changes and 10 seconds of ringtime is logged for those positions but the phone was not picked up since the talktime is 0. How can I sum the ringtimes together and retain the resourceID number for the agent who picked up the call.

https://www.dropbox.com/s/9ggxb1ndxp4vid6/sample_data.xlsx?dl=0

Answer 1

Untested on infomix. I'm not familiar with informix nor do I have a test environment for it. So I mocked up the SQL based on ANSII standards and avoided use of syntax that may be specific to database. This resulted in subqueries which may be able to be avoided if I knew syntax better...

Here's it working on SQL Server though a SQL Fiddle

SELECT O.SessionID, C.ResourceID, C.StartDateTime, C.EndDateTime,
  sum(O.ringTime) as TotalRingTime, sum(O.talkTime) as TotalTalkTime, 
  Sum(O.HoldTime) as TotalHoldTime, Sum(O.WorkTime) as TotalWorkTime,
  count(Distinct O.ResourceID) as CntofRes
FROM AgentConnectionDetail O
LEFT JOIN 
 (
  SELECT A.SessionID, A.ResourceID, A.StartDateTime, A.EndDateTime
  FROM AgentConnectionDetail A
  INNER JOIN (
       SELECT SessionID, Max(StartDateTime) as StartDateTime  
       FROM AgentConnectionDetail
       GROUP BY SessionID) B
    on A.SessioNID = B.SessionID
   and A.StartDateTime = B.StartDateTime) C
 on C.SessionID=O.SessionId
GROUP BY O.SessionID, C.ResourceID, C.StartDateTime, C.EndDateTime
ORDER BY SessionID

• Assuming the max StartDateTime will be the agent who answers the call... we get the session and datetime from inner most query labeled inline view as B.
• Also assuming a session will NEVER have two records with the exact same start datetime; we then join it back to base table A to get the resourceID on who answered the call. This results in a inline view containing the session, resource, startdatetime, endDateTime which I've Labeled C.
• We just join this back to the aggregate values. Since it's a one-to-one relationship we don't have to worry about falsely increasing the total values. thus a straight join should work.

I added a cntOfRes to show that 3 resources were involved in that session and only mocked up some of the data.