[rt-users] Time Worked Report

[Alex Young]

Its been suggested to make the Ticket.TimeWorked field uneditable by
users. That would stop anyone from editing the field directly, and it
would only change when adding or subtracted time worked in a
transaction. Besides, if youre removing time from a ticket you should be
saying why youre removing time. Same for adding time.

 

Stephen, dont forget to reply to all if you want the list to see your
replies. (Which I think you do)

 

 

From: Stephen Cochran [mailto:stephen.a.cochran.lists at cahir.net] 
Sent: 03 March 2009 15:33
To: Alex Young
Subject: Re: [rt-users] Time Worked Report

 


You could use the same logic and not restrict on resolved, but not sure
how you'd know not to double-count any time put in by adjusting the
Tickets.TimeWorked directly. It could also be adjusted downwards as
well, which raises all sorts of other questions about where that would
get subtracted from. Adjustments made to TimeWorked do cause a
transaction to be created, it just doesn't populate the TimeTaken field
of the transaction. 

Now that I think about it, that might be the simplest solution, beacuse
most of the complexity comes from trying to accurately deal with direct
adjustments made to the Tickets.TimeWorked field.

Anyone from BestP that could weigh in on the consequences of having a
change to TimeWorked also record the different in
Transactions.TimeTaken?



On Tue, Mar 3, 2009 at 9:52 AM, Alex Young
<alexyoung at scoutsolutions.co.uk> 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> 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] 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/71f09ead/attachment.htm>