[rt-users] Time Worked Report

[Alex Young]

I had a bit of a go at doing this myself. It needs some more work as it
doesnt take into account if time has been removed from a ticket. It
happens sometimes because of typos etc.

 

If you get anywhere with this please share it, as I havent had the time
to work on it further.

 

PRNumber is an internal reference number that we book client work too,
so you wont need that, or you can change it for something else.

 

SELECT distinct SUM(Transactions.TimeTaken) AS 'Time Taken (Mins)',
Transactions.Created, Users.RealName, Tickets.Subject, Queues.Name AS
'Queue Name', Transactions.ObjectId AS 'Ticket ID',

(select ObjectCustomFieldValues.Content from ObjectCustomFieldValues
where ObjectCustomFieldValues.CustomField = '11' and
Transactions.ObjectId = ObjectCustomFieldValues.ObjectId order by
ObjectCustomFieldValues.id desc LIMIT 1) AS PRNumber

FROM Transactions

LEFT JOIN Users

ON Transactions.Creator = Users.Id

LEFT JOIN Tickets

ON Transactions.ObjectId = Tickets.id

LEFT JOIN Queues

ON Tickets.Queue = Queues.Id

WHERE Transactions.TimeTaken !=0

AND DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= Transactions.Created

GROUP BY Subject;

 

 

From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Stephen
Cochran
Sent: 03 March 2009 05:01
To: rt Users
Subject: [rt-users] Time Worked Report

 


I've written a sql query to pull out the time worked for all tickets
resolved in the last week among other things. The problem with this is
that it doesn't give a complete picture of time worked for any given
week since a ticket could have had time worked put in as part of a
transaction but the ticket might still be open. I could query the
Transactions table for the TimeTaken field, but that could lead to
double-counting if any of those transactions are part of a resolved
ticket. 

I know I could work through the db/sql and find the right query to pull
out the time worked in the last week, but I'm wondering if someone else
has already done it so I can save myself the trouble.

Thanks, 
Steve

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090303/3adf5288/attachment.htm>