[rt-users] Re: Technician reports (Jaime Kikpole)

[Damian]

>         My job wants to pull data from RT so as to list either the number of
> comments (not tickets) or the amount of time directly associated with
> each technician.  For example, my desktop tech might own a ticket, but
> I add two comments (5 minutes and 15 minutes) and a reply (10 minutes)
> to it.  I need to run a search that gives me credit for the 30 minutes
> that I put into that ticket and others.
> 

I have a similar report where on a per-user basis I total up their
time worked of the last N number of days.  Doesn't matter who
owns the ticket.  Here is the MySQL query:

               SELECT
                        DATE_FORMAT(Transactions.Created, '%b %d, %Y') as tdate,
                        Tickets.id,
                        Tickets.Status,
                        SUM(Transactions.TimeTaken),
                        Tickets.Subject
                FROM
                        Tickets, Users, Transactions
                WHERE
                        Transactions.Creator = Users.id AND
                        Users.Name='$userName' AND
                        Transactions.Ticket = Tickets.id AND
                        Transactions.TimeTaken > 0 AND
                        DATE_FORMAT(Transactions.Created, '%Y-%m-%d') > 
                                  DATE_SUB(CURDATE(), INTERVAL $intervalDB)
                GROUP BY
                        tdate, Tickets.id
                ORDER BY
                        tdate ASC, Tickets.id ASC

Hope this helps,
Damian