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: [email protected] [mailto:[email protected]] 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
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [email protected] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
