Time spent in category

Hello,

I’m trying to determine the amount of time a ticket spends in a
category. IE, we have added a “pending_client” category to our
install of RT, we’d like to know how long each ticket spends in this
category. Is there an easy way to do this? (Note that a ticket may go
in and out of this category many times)

I’ve already developed a SQL query to determine the amount of time a
ticket spends in the “new” status, so I’m hoping that someone can
point out a way to determine this.

For reference, here’s the (php) code I’m already using:
$sql = "SELECT if(avg((if((UNIX_TIMESTAMP(t.TransOpened) -
UNIX_TIMESTAMP(t.Trans2Created))/60 < 0,0,(UNIX_TIMESTAMP
(t.TransOpened) - UNIX_TIMESTAMP(t.Trans2Created))/60))) IS NULL,0,
avg(if((UNIX_TIMESTAMP(t.TransOpened) - UNIX_TIMESTAMP
(t.Trans2Created))/60 < 0,0,(UNIX_TIMESTAMP(t.TransOpened) -
UNIX_TIMESTAMP(t.Trans2Created))/60))) AS Summed , ";

if ($DateRange != "week") {
	$sql .= "DATE_FORMAT(date_sub(current_date, INTERVAL tens.i*10 +  

units.i $DateRange),‘$DisplayFormat’) as RangeName, ";
} else {
$sql .= "DATE_FORMAT(date_sub(date_sub(current_date, INTERVAL
tens.i*10 + units.i $DateRange),INTERVAL dayofweek(date_sub
(current_date, INTERVAL tens.i *10 + units.i week))-2
DAY),‘$DisplayFormat’) as RangeName, ";
}

$sql .= "DATE_FORMAT(date_sub(current_date, interval tens.i*10 +  

units.i $DateRange),‘$Format’) as GroupFormat
FROM Integers AS units CROSS JOIN Integers AS tens
LEFT JOIN (
SELECT t.Started AS TransOpened,
if(date_format(t.Started,‘%Y’) = ‘1970’,‘1970-01-01’,if
(trans2.Created < t.Starts,t.Starts,trans2.Created)) AS Trans2Created,
t.Created as Created,
t.ID as ID
FROM Tickets AS t
RIGHT JOIN (
SELECT DISTINCT(ObjectID) as SeverityID, /* Severity */
Content AS SeverityContent
FROM ObjectCustomFieldValues
WHERE CustomField = 15
AND Disabled = 0
) AS SeverityCF ON t.ID = SeverityCF.SeverityID ";

if ($Client != '0') {
	$sql .= " RIGHT JOIN (
			SELECT DISTINCT(ObjectID) as ObjectID
				FROM ObjectCustomFieldValues
				WHERE CustomField = 2
					AND Content IN ('" . $Client . "')						/* Clients Listing */
					AND Disabled = 0
		) AS ClientCF ON t.ID = ClientCF.ObjectID ";
}
	
$sql .= ", Tickets AS trans2 WHERE t.Queue = 40
AND t.ID = trans2.ID
	AND (SeverityCF.SeverityContent = 'low' OR  

SeverityCF.SeverityContent IS NULL)
AND CONVERT_TZ(t.Created,‘GMT’,‘US/Pacific’) BETWEEN ‘" . strftime
(’%Y-%m-%d %H:%m:%l’,$UnixStartDate) . “’ AND '” . strftime(‘%Y-%m-%d
%H:%m:%l’,$UnixEndDate) . “‘) AS t
ON date_format(t.Created,’$Format’) = date_format(date_sub
(current_date, interval tens.i10 + units.i $DateRange),‘$Format’)
WHERE tens.i
10 + units.i between 1 and $TempRange
GROUP BY GroupFormat
ORDER BY GroupFormat asc”;

Thanks,
Chris Black
Systems and Business Support Engineer
Cell: 515-229-9282
Office: 949-255-5077
AIM: blackc2004
chrisb@webreachinc.com