Here is a query to return average ticket close by category (which is set using freefield1). It should be relatively easy to adapt this to your needs... select ifnull(freetext1,'Unclassified'), avg(TO_DAYS(t.change_time) - TO_DAYS(t.create_time)) from ticket t, ticket_state ts where t.ticket_state_id = ts.id and ts.id in (2,10) and DATE_FORMAT(t.create_time,'%Y') = DATE_FORMAT(now(),'%Y') group by freetext1; I actually use this to feed a plot routine for my system that returns the data in the form of a graph For more comprehensive reporting (beyond a simple graph) we have implemented more of an SLA type of report and I've attached part of the SQL for that (we do it by queue/geography). It is implemented outside of the OTRS database since we didn't want to mess with adding new tables and impact our ability to easily upgrade, etc. Here is the table structure for that: Table bangalore_sla in database oc Field name Type Allow nulls? Key Default value Extras tn <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=0> varchar(50) No None month <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=1> tinyint(4) No None 0 year <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=2> smallint(6) No None 0 submitter <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=3> varchar(50) No None closed_by <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=4> varchar(50) No None subject <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=5> tinytext Yes None queue <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=6> varchar(200) No None type <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=7> varchar(150) No None category <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=8> varchar(150) No None status <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=9> varchar(100) No None create_time <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=10> datetime No None 0000-00-00 00:00:00 resp_time <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=11> datetime Yes None close_time <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=12> datetime Yes None hours_to_resolve <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=13> decimal(7,2) Yes None Here is the relevant part of the script we run to refresh the SLA tables... sladir='/home/oc_sla' date=`date -I` logfile=$sladir/refresh_oc_sla_$date.log # # Refresh SLA data # mysql -u $user -h $host -D otrs < $sladir/bangalore.sql > $logfile Hope it helps someone - have fun. Mike _____ From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Davis, Gary H Sent: Wednesday, January 04, 2006 12:29 PM To: otrs@otrs.org Subject: [otrs] Calculating avg. ticket open time by queue Is there a way to calculate the average time between states in OTRS? For example: we would like to know the average time between "open" and "closed successfully" for all tickets submitted to the "development" queue. Any help would be appreciated.... Thanks Gary Davis PSIC Lab Manger This email may contain material that is confidential, privileged and/or attorney work product for the sole use of the intended recipient. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies.