We have the need for simple reports including this basic info: Name of Employee/Extension Number, Dials, Conversations, Time on Phone.
I’m unfamiliar with the various flags used in the crd table but I’ve created these two queries to capture stats for incoming and outgoing calls. I’ve had issues where I’m filtering out some edge cases so I’m not sure I’m getting all the possible data.
Can anyone verify if these are doing as I suspect? I would appreciate if you can clarify any of the flags used in different fields so I’m more aware of the proper filtering as well.
-- outgoing calls
SELECT FLOOR(SUM(`billsec`)/60) as minutes, SUM(`billsec`) % 60 as seconds, dst as extension
FROM cdr
WHERE dcontext IN ('ext-local', 'from-did-direct')
AND `calldate` BETWEEN '2010-05-11 00:00:00' AND '2010-05-12 00:00:00'
AND LENGTH(`dst`) = 3
GROUP BY extension
ORDER BY extension;
-- incoming calls
SELECT FLOOR(SUM(`billsec`)/60) as minutes, SUM(`billsec`) % 60 as seconds, SUBSTRING(`channel` FROM 5 FOR 3) as extension
FROM cdr
WHERE LENGTH(`src`) >= 7
AND LENGTH(`dst`) >= 7
AND `calldate` BETWEEN '2010-05-11 00:00:00' AND '2010-05-12 00:00:00'
GROUP BY extension
ORDER BY extension;
I’ve added a few more to get number of dials, conversations etc:
What I’m not sure about is how calls that are transferred are handled. If anyone has any input or suggestions on the validity of these as well I would appreciate feedback.
-- outgoing calls
SELECT dst as extension, FLOOR(SUM(`billsec`)/60) as minutes, SUM(`billsec`) % 60 as seconds
FROM cdr
WHERE dcontext IN ('ext-local', 'from-did-direct')
AND `calldate` BETWEEN '2010-05-11 00:00:00' AND '2010-05-12 00:00:00'
AND LENGTH(`dst`) = 3
GROUP BY extension
ORDER BY extension;
-- outgoing dials
SELECT SUBSTRING(`channel` FROM 5 FOR 3) as extension, COUNT(`lastapp`) as dials
FROM cdr
WHERE dcontext IN ('from-internal')
AND `lastapp` = 'Dial'
AND LENGTH(`dst`) >= 7
AND `calldate` BETWEEN '2010-05-11 00:00:00' AND '2010-05-12 00:00:00'
GROUP BY extension
ORDER BY extension;
-- number of outgoing conversations
SELECT SUBSTRING(`channel` FROM 5 FOR 3) as extension, COUNT(`disposition`) as conversations
FROM cdr
WHERE dcontext IN ('from-internal')
AND `lastapp` = 'Dial'
AND `disposition` = 'ANSWERED'
AND LENGTH(`dst`) >= 7
AND `calldate` BETWEEN '2010-05-11 00:00:00' AND '2010-05-12 00:00:00'
GROUP BY extension
ORDER BY extension;
-- incoming calls
SELECT SUBSTRING(`channel` FROM 5 FOR 3) as extension, FLOOR(SUM(`billsec`)/60) as minutes, SUM(`billsec`) % 60 as seconds
FROM cdr
WHERE LENGTH(`src`) >= 7
AND LENGTH(`dst`) >= 7
AND `calldate` BETWEEN '2010-05-11 00:00:00' AND '2010-05-12 00:00:00'
GROUP BY extension
ORDER BY extension;
-- number of incoming conversations
SELECT dst as extension, COUNT(`disposition`) as conversations
FROM cdr
WHERE dcontext IN ('ext-local', 'from-did-direct')
AND `lastapp` = 'Dial'
AND `disposition` = 'ANSWERED'
AND `calldate` BETWEEN '2010-05-11 00:00:00' AND '2010-05-12 00:00:00'
GROUP BY extension
ORDER BY extension;