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: [email protected] [mailto:[email protected]] On Behalf Of
Olli Aro
Sent: dinsdag 12 mei 2009 11:46
To: [email protected]
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
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
NEW! ENTERPRISE SUBSCRIPTION - Get more information NOW!
http://www.otrs.com/en/support/enterprise-subscription/
