<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
</head>
<body style="word-wrap:break-word">
Why don’t use you want to use REST? You’re shooting yourself in the foot; when you upgrade RT, your queries will no longer work and will have to be updated. If you use the REST interface, you can upgrade with impunity and not have to worry about your queries
not working anymore.
<div><br>
</div>
<div>—</div>
<div>Tom</div>
<div><br>
<div>
<div>On Dec 1, 2013, at 11:18 PM, Chris Herrmann <<a href="mailto:chrisherrmann7@gmail.com">chrisherrmann7@gmail.com</a>> wrote:</div>
<br class="Apple-interchange-newline">
<blockquote type="cite">
<div dir="ltr">
<div>
<div>
<div>Hi all,<br>
<br>
</div>
Just bumping this one - does anyone have any suggestions? Am I approaching this the wrong way? Using MySQL btw if that makes a difference.<br>
<br>
</div>
Thanks,<br>
<br>
</div>
Chris<br>
<div><br>
<div class="gmail_quote">---------- Forwarded message ----------<br>
From: <b class="gmail_sendername">Chris Herrmann</b> <span dir="ltr"><<a href="mailto:chrisherrmann7@gmail.com">chrisherrmann7@gmail.com</a>></span><br>
Date: 24 November 2013 22:11<br>
Subject: trying to calculate time worked per transaction / ticket using SQL<br>
To: <a href="mailto:rt-users@lists.bestpractical.com">rt-users@lists.bestpractical.com</a><br>
<br>
<br>
<div dir="ltr">
<div>
<div>
<div>
<div>Hi all,<br>
<br>
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).<br>
<br>
</div>
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.<br>
<br>
Anyway, what I want to end up with is the following fields:<br>
<br>
</div>
Tickets.EffectiveID<br>
Queues.Name<br>
</div>
Tickets.Owner<br>
</div>
<div>Transactions.Creator<br>
</div>
<div>Transactions.TimeTaken<br>
</div>
<div>Transactions.Created<br>
</div>
<div>Tickets.Status<br>
</div>
<div>Tickets.Started<br>
</div>
<div>Tickets.Resolved<br>
</div>
<div>Tickets.Created<br>
</div>
<div>Transactions.Type<br>
<br>
</div>
<div>So i can easily point various reports at it and work from there.<br>
</div>
<div><br>
</div>
<div>the sql I'm using is...<br>
SELECT<br>
Tickets.EffectiveId AS TicketID,<br>
Queues.`Name` AS Queue,<br>
Tickets.`Owner` AS OwnerID,<br>
Transactions.Creator AS TransactionCreatorID,<br>
Transactions.TimeTaken AS TimeTaken,<br>
Transactions.Created AS TransactionCreated,<br>
Tickets.`Status` AS TicketStatus,<br>
Tickets.Started AS TicketStarted,<br>
Tickets.Resolved AS TicketResolved,<br>
Tickets.Created AS TicketCreated,<br>
Transactions.Type AS TransactionType<br>
FROM<br>
((((Tickets<br>
JOIN Transactions ON ((Transactions.ObjectId = Tickets.id)))<br>
JOIN Queues ON ((Queues.id = Tickets.Queue))))) <br>
<br>
</div>
<div>but I'm not getting the results I expect...<br>
</div>
<div><br>
</div>
<div>or even something far simpler like:<br>
SELECT<br>
Transactions.Creator,<br>
sum(Transactions.TimeTaken/60) AS TimeInHours,<br>
Month(Transactions.Created) AS TransactionMonth,<br>
Year(Transactions.Created) AS TransactionYear<br>
FROM<br>
Tickets JOIN Transactions ON Transactions.ObjectId = Tickets.id<br>
where Transactions.Created > "2013-10-01"<br>
group by Creator, Month(Transactions.Created), Year(Transactions.Created)<br>
<br>
</div>
<div>just to try and compare the numbers... that I'm seeing with timeworked.pl...<br>
</div>
<div><br>
</div>
<div>Now my problem is that the numbers I'm getting don't match those returned by REST - for example using the
<a href="http://timeworked.pl/" target="_blank">timeworked.pl</a> script..<br>
</div>
<div><br>
</div>
<div>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...<br>
<br>
</div>
<div>Thankyou!<span class="HOEnZb"><font color="#888888"><br>
<br>
</font></span></div>
<span class="HOEnZb"><font color="#888888">Chris<br>
</font></span></div>
</div>
<br>
</div>
</div>
</blockquote>
</div>
<br>
</div>
<br>
<br>
<font color="Blue" face="Arial" size="2">This e-mail message is confidential and is intended solely for the use of the addressee(s) named above. If you are not the intended recipient, or the person responsible to deliver it to the recipient, you are hereby
advised that any dissemination, distribution or copying of this communication is prohibited. If you have received this e-mail in error, please notify the sender by return e-mail. Thank you.</font>
</body>
</html>