Hi Olli,

Maybe not a direct answer to your question, but here three examples I am working with.

They may give you a clue. The second one might even be close to your solution (Sum( t.time_unit ))

#1 time spent per agent

SELECT

t.create_by,

u.first_name,

u.last_name,

Sum(t.time_unit)

FROM

time_accounting AS t

Left Join users AS u ON t.create_by = u.id WHERE

year(t.create_time) =  '2009'

GROUP BY

t.create_by

#2 tickets created in March 2009 for customer FOOBAR

SELECT t.ticket_id, c.tn AS 'NSC#', c.freetext1 AS 'CustRef', c.title AS Title, tt.name AS
TYPE , c.ticket_priority_id AS 'Prio', c.customer_id AS 'Customer', c.freetext11 AS Site, Sum( t.time_unit ) AS min, concat( floor( Sum( t.time_unit ) /60 ) , ':', mod( Sum( t.time_unit ) , 60 ) ) AS mytime
FROM time_accounting AS t
LEFT JOIN ticket AS c ON t.ticket_id = c.id
LEFT JOIN ticket_type AS tt ON c.type_id = tt.id
WHERE year( t.create_time ) = '2009'
AND month( t.create_time ) = '3'
AND c.customer_id = 'FOOBAR'
GROUP BY t.ticket_id
LIMIT 0 , 30

# 3 report on all tickets closed in 3/2009 of customer ‘FOOBAR’ that are NOT Changes

    This one has a little bug somewhere, I think merged tickets get counted double.

SELECT tn as 'Case#',t.freetext1 as 'CustRef', t.title as Title,t.freetext11 as Site, tt.name as Type,q.name as 'Queue',tp.name as 'Prio', t.create_time as Opened, th.create_time as Closed, ts.name as State, u.first_name as Engineer, Sum( ta.time_unit ) AS 'real min', concat( floor( Sum( ta.time_unit ) /60 ),':', lpad(truncate(mod(sum(ta.time_unit),60),0),2,0) ) AS 'real time', t.customer_user_id as 'Requester'

FROM `ticket_history` as th

LEFT JOIN ticket AS t ON th.ticket_id = t.id

LEFT JOIN ticket_priority as tp ON th.priority_id = tp.id

LEFT JOIN ticket_type as tt ON t.type_id = tt.id

LEFT JOIN ticket_state as ts ON th.state_id = ts.id

LEFT JOIN time_accounting as ta ON t.id = ta.ticket_id

LEFT JOIN users as u ON th.owner_id = u.id

LEFT JOIN queue as q ON t.queue_id = q.id

where

(th.history_type_id = 27 and th.state_id in (2,3,10) and year(th.create_time) = 2009 and month(th.create_time) = 3)

AND customer_id = 'ACME'

AND tt.name != 'Change'

GROUP by t.tn

Any corrections are welcome :=)

gr,

Frans

From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Olli Aro
Sent: dinsdag 12 mei 2009 11:46
To: otrs@otrs.org
Subject: [otrs] How to create a report on time used

Hi all,

Does anyone know how I can compile a list of fix time used to fix tickets for all tickets in a single queue?

I use field “Time units (work units)” in order to record time for every ticket, but now it would be useful to be able to see somehow the sum of time for all tickets.

Regards,

Olli