Aaah, ok so there is no functionality
for this in the current OTRS user interface, but I will have to write my custom
web page.
I will try that.
Thanks for such a quick response
J
Olli
From:
otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Frans
Stekelenburg
Sent: 12 May 2009 11:11
To: User questions and discussions about OTRS.
Subject: Re: [otrs] How to create a report on time used
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