Hello Ralf (and others! :))
I can't help much with the advanced analytics I'm afraid, but against the SupportWorks database you may find the following useful. Script one groups the ages of call by timeframe (e.g. less than 10 days, 10 - 20 days etc.), whilst the second script provides an "audit", giving call references and the age of calls - this can be expanded to add other information as you wish. Here they are:
SELECT
SUM(
CASE
WHEN (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) < 10
THEN 1
ELSE 0
END)
AS "< 10"
, SUM(
CASE
WHEN (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) BETWEEN 10 AND 20
THEN 1
ELSE 0
END)
AS "10 - 20"
, SUM(
CASE
WHEN (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) BETWEEN 20 AND 30
THEN 1
ELSE 0
END)
AS "20 - 30"
, SUM(
CASE
WHEN (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) > 30
THEN 1
ELSE 0
END)
AS "> 30"
, SUM(1) AS "Total Calls"
FROM opencall
WHERE `status` NOT IN (6, 16, 17, 18)
[/CODE]
The second script (audit):
[CODE]
SELECT callref
, (((UNIX_TIMESTAMP(NOW())) - logdatex) / 86640) AS "Age (Days)"
FROM opencall o
WHERE `status` NOT IN (6, 16, 17, 18)
ORDER BY 2 DESC
[/CODE]
I hope they help somewhat.
Matt
