[rt-users] Time spent in category

[Chris Black]

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.i*10 + 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 at webreachinc.com


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20060816/1501c86a/attachment.htm>