Weekly or monthly reports of billable time per person

[HPP]

Weekly or monthly reports of billable time per person
Hello,
We have many remote employees, a hard working group, but we need to be able to track their time accounted on a monthly or weekly basis, i.e...

- Jim totaled 1865 billable minutes this week.
or...
- Bill totaled 2015 billable minutes this month.

I've spent a lot of time searching forums for a method but have not found anything I could work with.

Surely other people have wanted to do this sort of thing.

Any suggestions?

[sparker]

I'd love to know an answer to this one as well. I've been playing in SQL to try go generate it, but can't figure out the relationships between all the tables properly. If anybody has done this, it'd be great if you could post the SQL here (or an .xml file for a new report)...

Steve

[crythias]

Here's a breakdown sql query: viewtopic.php?f=60&t=12546
You could "group by" and sum

[sparker]

Here's a breakdown sql query: http://forums.otrs.org/viewtopic.php?f=60&t=12546
You could "group by" and sum

Thanks Crythias! Just last night, I finally came up with the following SQL that does something similar.

Code: Select all

select distinct
  ticket.id, ticket.tn, ticket.title, 
  article.a_subject, queue.name, 
  users.login, time.change_time, time.time_unit
from 
  ticket ticket,
  ticket_history history,
  time_accounting time,
  article, 
  queue, 
  users 
where
  ticket.id  = history.ticket_id and
  ticket.id  = time.ticket_id and
  ticket.id  = article.ticket_id and
  ticket.queue_id = queue.id and
  ticket.user_id  = users.id and
  article.id = history.article_id and
  article.id = time.article_id
order by
  time.change_time
  ;

I'm going to run it and compare output to make sure that I came up with the same record information that your linked SQL shows.

Hopefully I got it "right", but if not, I'll use your example...

Have a GREAT day!

Steve