[rt-users] trying to calculate time worked per transaction / ticket using SQL

[Chris Herrmann]

Hi all,

I'm trying to put together a SQL query to present a view, that allows
summaries of data to be performed in reporting tools. I have a feeling that
this question has been asked before and answered, but my google-fu is
failing me, so apologies in advance... I've only found questions about
using RT-REST, for example (which I don't want to do).

I'm using RT 3.8.7 (yes I know it's old and it's in the pipeline to upgrade
but we have a bunch of other systems that are integrated with RT and so
it's not a simple "just upgrade RT" project for us.

Anyway, what I want to end up with is the following fields:

Tickets.EffectiveID
Queues.Name
Tickets.Owner
Transactions.Creator
Transactions.TimeTaken
Transactions.Created
Tickets.Status
Tickets.Started
Tickets.Resolved
Tickets.Created
Transactions.Type

So i can easily point various reports at it and work from there.

the sql I'm using is...
SELECT
Tickets.EffectiveId AS TicketID,
Queues.`Name` AS Queue,
Tickets.`Owner` AS OwnerID,
Transactions.Creator AS TransactionCreatorID,
Transactions.TimeTaken AS TimeTaken,
Transactions.Created AS TransactionCreated,
Tickets.`Status` AS TicketStatus,
Tickets.Started AS TicketStarted,
Tickets.Resolved AS TicketResolved,
Tickets.Created AS TicketCreated,
Transactions.Type AS TransactionType
FROM
((((Tickets
JOIN Transactions ON ((Transactions.ObjectId = Tickets.id)))
JOIN Queues ON ((Queues.id = Tickets.Queue)))))

but I'm not getting the results I expect...

or even something far simpler like:
SELECT
Transactions.Creator,
sum(Transactions.TimeTaken/60) AS TimeInHours,
Month(Transactions.Created) AS TransactionMonth,
Year(Transactions.Created) AS TransactionYear
FROM
Tickets JOIN Transactions ON Transactions.ObjectId = Tickets.id
where Transactions.Created > "2013-10-01"
group by Creator, Month(Transactions.Created), Year(Transactions.Created)

just to try and compare the numbers... that I'm seeing with timeworked.pl...

Now my problem is that the numbers I'm getting don't match those returned
by REST - for example using the timeworked.pl script..

Is there a definitive SQL somewhere that I should use to return these?...
and any pointers on what transaction types I should be avoiding or how to
avoid double counting merged transactions I would be very grateful...

Thankyou!

Chris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20131124/5110167c/attachment.htm>