<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; ">Hello,<DIV><BR class="khtml-block-placeholder"></DIV><DIV>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)</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>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.</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>For reference, here's the (php) code I'm already using: </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>$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 , ";</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN></DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>if ($DateRange != "week") {</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>$sql .= "DATE_FORMAT(date_sub(current_date, INTERVAL tens.i*10 + units.i $DateRange),'$DisplayFormat') as RangeName, ";</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>} else {</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>$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, ";<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN></DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>}</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN></DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>$sql .= "DATE_FORMAT(date_sub(current_date, interval tens.i*10 + units.i $DateRange),'$Format') as GroupFormat</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>FROM Integers AS units CROSS JOIN Integers AS tens </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>LEFT JOIN (</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>SELECT t.Started AS TransOpened, </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>if(date_format(t.Started,'%Y') = '1970','1970-01-01',if(trans2.Created < t.Starts,t.Starts,trans2.Created)) AS Trans2Created, </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>t.Created as Created, </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>t.ID as ID </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>FROM Tickets AS t</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>RIGHT JOIN (</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>SELECT DISTINCT(ObjectID) as SeverityID,<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>/* Severity */</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>Content AS SeverityContent </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>FROM ObjectCustomFieldValues</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>WHERE CustomField = 15</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>AND Disabled = 0 </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>) AS SeverityCF ON t.ID = SeverityCF.SeverityID ";</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN></DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>if ($Client != '0') {</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>$sql .= " RIGHT JOIN (</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>SELECT DISTINCT(ObjectID) as ObjectID </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>FROM ObjectCustomFieldValues</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>WHERE CustomField = 2 </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>AND Content IN ('" . $Client . "')<SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>/* Clients Listing */</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>AND Disabled = 0</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>) AS ClientCF ON t.ID = ClientCF.ObjectID ";</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>}</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN></DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>$sql .= ", Tickets AS trans2 WHERE t.Queue = 40 </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>AND t.ID = trans2.ID</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>AND (SeverityCF.SeverityContent = 'low' OR SeverityCF.SeverityContent IS NULL)</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>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</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>ON date_format(t.Created,'$Format') = date_format(date_sub(current_date, interval tens.i*10 + units.i $DateRange),'$Format')</DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>WHERE tens.i*10 + units.i between 1 and $TempRange </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>GROUP BY GroupFormat </DIV><DIV><SPAN class="Apple-tab-span" style="white-space:pre"> </SPAN>ORDER BY GroupFormat asc";</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Thanks,<BR><DIV> <SPAN class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><SPAN class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><SPAN class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><DIV>Chris Black</DIV><DIV>Systems and Business Support Engineer</DIV><DIV>Cell: 515-229-9282</DIV><DIV>Office: 949-255-5077</DIV><DIV>AIM: blackc2004</DIV><DIV><A href="mailto:chrisb@webreachinc.com">chrisb@webreachinc.com</A></DIV><SPAN class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "></SPAN><BR class="Apple-interchange-newline"></SPAN></SPAN></SPAN> </DIV><BR></DIV></BODY></HTML>