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 |
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com