<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:D="DAV:" xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Verdana","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-GB link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";
color:#1F497D'>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. <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> Stephen Cochran
[mailto:stephen.a.cochran.lists@cahir.net] <br>
<b>Sent:</b> 03 March 2009 14:27<br>
<b>To:</b> Alex Young<br>
<b>Subject:</b> Re: [rt-users] Time Worked Report<o:p></o:p></span></p>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal style='margin-bottom:12.0pt'>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):<br>
<br>
# total time worked in time_window from transactions<br>
select SUM(Transactions.TimeTaken) from ...<br>
where (Tickets.status = open or stalled or new) and<br>
Transactions.Created is within time_window<br>
<br>
+ <br>
<br>
# get total time worked from tickets resolved in time_window<br>
select Tickets.TimeWorked from ....<br>
where (Tickets.status = resolved) and<br>
Tickets.Resolved is within time_window<br>
<br>
- <br>
<br>
# subtract time from all transactions from tickets resolved in time_window<br>
select SUM(Transactions.TimeTaken) from ...<br>
where (Tickets.status = resolved) and<br>
Tickets.Resolved is within time_window and<br>
Transactions.ObjectId = Tickets.id<br>
<br>
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.<br>
<br>
Anyone see a flaw in this? Still think this is harder than it should be.
Explains why RT hasn't had these reports already ;)<br>
<br>
<o:p></o:p></p>
<div>
<p class=MsoNormal>On Tue, Mar 3, 2009 at 4:28 AM, Alex Young <<a
href="mailto:alexyoung@scoutsolutions.co.uk">alexyoung@scoutsolutions.co.uk</a>>
wrote:<o:p></o:p></p>
<div>
<div>
<p><span style='font-size:10.0pt;color:#1F497D'>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.</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;color:#1F497D'> </span><o:p></o:p></p>
<p><span style='font-size:10.0pt;color:#1F497D'>If you get anywhere with this
please share it, as I havent had the time to work on it further.</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;color:#1F497D'> </span><o:p></o:p></p>
<p><span style='font-size:10.0pt;color:#1F497D'>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.</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;color:#1F497D'> </span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>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',</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>(select
ObjectCustomFieldValues.Content from ObjectCustomFieldValues where
ObjectCustomFieldValues.CustomField = '11' and Transactions.ObjectId =
ObjectCustomFieldValues.ObjectId order by ObjectCustomFieldValues.id desc LIMIT
1) AS PRNumber</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>FROM Transactions</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>LEFT JOIN Users</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>ON Transactions.Creator
= Users.Id</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>LEFT JOIN Tickets</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>ON Transactions.ObjectId
= Tickets.id</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>LEFT JOIN Queues</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>ON Tickets.Queue =
Queues.Id</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>WHERE
Transactions.TimeTaken !=0</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>AND
DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= Transactions.Created</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;font-family:Consolas'>GROUP BY Subject;</span><o:p></o:p></p>
<p><span style='font-size:10.0pt;color:#1F497D'> </span><o:p></o:p></p>
<p><span style='font-size:10.0pt;color:#1F497D'> </span><o:p></o:p></p>
<div style='border:none;border-top:solid windowtext 1.0pt;padding:3.0pt 0cm 0cm 0cm;
border-color:-moz-use-text-color -moz-use-text-color'>
<p><b><span lang=EN-US style='font-size:10.0pt'>From:</span></b><span
lang=EN-US style='font-size:10.0pt'> <a
href="mailto:rt-users-bounces@lists.bestpractical.com" target="_blank">rt-users-bounces@lists.bestpractical.com</a>
[mailto:<a href="mailto:rt-users-bounces@lists.bestpractical.com"
target="_blank">rt-users-bounces@lists.bestpractical.com</a>] <b>On Behalf Of </b>Stephen
Cochran<br>
<b>Sent:</b> 03 March 2009 05:01<br>
<b>To:</b> rt Users<br>
<b>Subject:</b> [rt-users] Time Worked Report</span><o:p></o:p></p>
</div>
<div>
<div>
<p> <o:p></o:p></p>
<p><br>
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. <br>
<br>
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.<br>
<br>
Thanks, <br>
Steve<o:p></o:p></p>
</div>
</div>
</div>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
</body>
</html>