Ok i got this answered myself. The report that was looked at was the incorrect one. They were looking at the Agent Call Summary Report. This is broken down into two tasks. The first is to query for all calls that are given to the Agent via ACD. The below query is what I came up with to get that:
SELECT z.acd, x.nonacd, z.resourcename, (acd + nonacd) total, teamname, y.outbound FROM (
SELECT SUM(case when acd.talktime > '0' then 1 else 0 end) acd, resourcename, teamname
FROM agentconnectiondetail acd
INNER JOIN contactcalldetail ccd ON (ccd.sessionID = acd.sessionID
AND ccd.sessionSeqNum = acd.sessionSeqNum AND ccd.nodeID = acd.nodeID)
INNER JOIN resource r ON (acd.resourceID = r.resourceID AND acd.profileID = r.profileID)
INNER JOIN team t on (r.assignedteamid = t.teamid)
WHERE ccd.startdatetime > '2012-12-03 06:00:00.000' AND ccd.startdatetime < '2012-12-04 06:00:00.000'
AND ((
(WEEKDAY(ccd.startdatetime) <> '0' OR WEEKDAY(ccd.startdatetime) <> '6')
AND TO_CHAR(ccd.startdatetime,'%R') >= '14'
AND TO_CHAR(ccd.startdatetime,'%R') <= '24'
)
OR (
(WEEKDAY(ccd.startdatetime) = '6')
AND TO_CHAR(ccd.startdatetime,'%R') > '16' AND TO_CHAR(ccd.startdatetime,'%R') < '20')
)
AND t.teamname = 'Your Team Name Here'
GROUP BY resourcename, teamname) z
LEFT OUTER JOIN
(SELECT COUNT(*) nonacd, resourcename
FROM contactcalldetail ccd
INNER JOIN resource r ON (ccd.destinationID = r.resourceID AND ccd.profileID = r.profileID)
WHERE ccd.startdatetime > '2012-12-03 06:00:00.000' AND ccd.startdatetime < '2012-12-04 06:00:00.000'
AND ((
(WEEKDAY(ccd.startdatetime) <> '0' OR WEEKDAY(ccd.startdatetime) <> '6')
AND TO_CHAR(ccd.startdatetime,'%R') >= '14'
AND TO_CHAR(ccd.startdatetime,'%R') <= '24'
)
OR (
(WEEKDAY(ccd.startdatetime) = '6')
AND TO_CHAR(ccd.startdatetime,'%R') > '16' AND TO_CHAR(ccd.startdatetime,'%R') < '20')
)
GROUP BY resourcename) x ON z.resourcename = x.resourcename
LEFT OUTER JOIN
(SELECT COUNT(*) outbound, r.resourcename
FROM contactcalldetail ccd
INNER JOIN resource r ON (ccd.destinationID = r.resourceID AND ccd.profileID = r.profileID)
WHERE ccd.startdatetime > '2012-12-03 06:00:00.000' AND ccd.startdatetime < '2012-12-04 06:00:00.000'
AND ccd.originatorType ='1'
AND ((
(WEEKDAY(ccd.startdatetime) <> '0' OR WEEKDAY(ccd.startdatetime) <> '6')
AND TO_CHAR(ccd.startdatetime,'%R') >= '14'
AND TO_CHAR(ccd.startdatetime,'%R') <= '24'
)
OR (
(WEEKDAY(ccd.startdatetime) = '6')
AND TO_CHAR(ccd.startdatetime,'%R') > '16' AND TO_CHAR(ccd.startdatetime,'%R') < '20')
)
GROUP BY r.resourcename
ORDER BY r.resourcename) y ON z.resourcename = y.resourcename
GROUP BY z.resourcename, acd, outbound, nonacd, teamname ORDER BY z.resourcename
This will give you all calls given to the agent. Next you have to filter out the non-ACD calls that are transfers or direct calls. This is the query I came up with for that.
SELECT COUNT(*) common, z.resourcename FROM(
SELECT * FROM agentconnectiondetail acd
INNER JOIN contactcalldetail ccd ON (ccd.sessionID = acd.sessionID
AND ccd.sessionSeqNum = acd.sessionSeqNum AND ccd.nodeID = acd.nodeID)
INNER JOIN resource r ON (acd.resourceID = r.resourceID AND acd.profileID = r.profileID)
INNER JOIN team t on (r.assignedteamid = t.teamid)
WHERE ccd.startdatetime > '2012-12-03 06:00:00.000' AND ccd.startdatetime < '2012-12-04 06:00:00.000'
AND ((
(WEEKDAY(ccd.startdatetime) <> '0' OR WEEKDAY(ccd.startdatetime) <> '6')
AND TO_CHAR(ccd.startdatetime,'%R') >= '14'
AND TO_CHAR(ccd.startdatetime,'%R') <= '24'
)
OR (
(WEEKDAY(ccd.startdatetime) = '6')
AND TO_CHAR(ccd.startdatetime,'%R') > '16' AND TO_CHAR(ccd.startdatetime,'%R') < '20'))
ORDER BY r.resourceid, ccd.sessionid, ccd.startdatetime, teamname) z
INNER JOIN (SELECT *
FROM contactcalldetail ccd
INNER JOIN resource r ON (ccd.destinationID = r.resourceID AND ccd.profileID = r.profileID)
WHERE ccd.startdatetime > '2012-12-03 06:00:00.000' AND ccd.startdatetime < '2012-12-04 06:00:00.000'
AND ((
(WEEKDAY(ccd.startdatetime) <> '0' OR WEEKDAY(ccd.startdatetime) <> '6')
AND TO_CHAR(ccd.startdatetime,'%R') >= '14'
AND TO_CHAR(ccd.startdatetime,'%R') <= '24'
)
OR (
(WEEKDAY(ccd.startdatetime) = '6')
AND TO_CHAR(ccd.startdatetime,'%R') > '16' AND TO_CHAR(ccd.startdatetime,'%R') < '20'))
ORDER BY r.resourceid, ccd.startdatetime) x ON (z.sessionid = x.sessionid AND z.sessionseqnum = x.sessionseqnum AND z.profileID = x.profileID AND z.nodeID = x.nodeID) GROUP BY z.resourcename
The last task is to take both sets of results and subtract the second query from the first sets nonacd column. The second query insures that you are taking out calls that originated in ACD and are already counted. Hope this helps anyone that needs it. Oh one more thing. The custom filters you see with the TO_CHAR(ccd.startdatetime,'%R') is just filtering the time to just 8:00 am CST to 6:00 pm CST and making sure that the days are M-Saturday with the WEEKDAY function. Remember that the UCCX database is in UTC time and will need to be filtered according to your timezone. For CST my queries needed to add 6 hours to the time desired to get the correct data set.
