[rt-users] Time Worked Report

[Kenneth Crocker]

Alex,


	We have many group managers that want a time report by cycle (week or 
month). I figured we would just create a new Table (Called Time_Cycle). 
Then, write a cron job to run every month that would put put the time 
worked for the month in the appropriate cycle bucket. If the amount was 
the same as last month, it would put in the difference, otherwise, it 
would enter 0. That way, an SQL report could look at the table and see 
the actual time worked per month on a ticket.
	I haven't seen or heard anything about an enhancement to basic RT to do 
this, so it would have to be at your own risk.


Kenn
LBNL

On 3/3/2009 6:52 AM, Alex Young wrote:
> Looks like that would work to me. Though I would need to get the time 
> taken no matter what the status is as I was aiming at pulling the info 
> out for time sheets for the developers and support department so the 
> accounts department can invoice clients. At the moment RT users are 
> putting all their work in RT, and then putting their time in a separate 
> timesheet, which management then compile and give to accounts.
> 
>  
> 
> It would be very useful to have a time sheet extension for this purpose, 
> and most of the code and info must already be available in RT to do this.
> 
>  
> 
>  
> 
>  
> 
> *From:* Stephen Cochran [mailto:stephen.a.cochran.lists at cahir.net]
> *Sent:* 03 March 2009 14:27
> *To:* Alex Young
> *Subject:* Re: [rt-users] Time Worked Report
> 
>  
> 
> Thinking through this, the only way to be completely accurate list of 
> time spend in some given time_window would be the following (in psudo sql):
> 
> # total time worked in time_window from transactions
> select SUM(Transactions.TimeTaken) from ...
> where (Tickets.status = open or stalled or new) and
> Transactions.Created is within time_window
> 
> +
> 
> # get total time worked from tickets resolved in time_window
> select Tickets.TimeWorked from ....
> where (Tickets.status = resolved) and
> Tickets.Resolved is within time_window
> 
> -
> 
> # subtract time from all transactions from tickets resolved in time_window
> select SUM(Transactions.TimeTaken) from ...
> where (Tickets.status = resolved) and
> Tickets.Resolved is within time_window and
> Transactions.ObjectId = Tickets.id
> 
> This will capture all times entered directly into the TimeWorked field 
> of the ticket as occurring during the time_window when the ticket was 
> resolved. Probably fairly accurate, and would never be double counted.
> 
> Anyone see a flaw in this? Still think this is harder than it should be. 
> Explains why RT hasn't had these reports already ;)
> 
> On Tue, Mar 3, 2009 at 4:28 AM, Alex Young 
> <alexyoung at scoutsolutions.co.uk <mailto:alexyoung at scoutsolutions.co.uk>> 
> wrote:
> 
> 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> 
> [mailto: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
> 
>  
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com