[rt-users] Getting SetTimeWorkedAutomatically Working in RT 4.2.9

[Jeffrey Pilant]

geraldsnm writes:
> Our management is adamant about having time tracked automatically on
> tickets.
>
> I've been banging my head against a wall for 2 months trying to get this to
> work VIA pure SQL until I realized that the data model seems to not support
> it. Specifically although there's a transaction logged every time the ticket
> goes from open to stalled back to opened, there's no idea of a transaction
> session so that if I try to write SQL that joins transaction to transaction,
> and tries to subtract the latest time stamp for "opened" from the last
> "opened" there's no way to determine which two "Opened" transactions
> corelate to the transaction before it, so it ends up double counting
> (imagine a ticket that goes from Opened, to Stalled, to Opened, to Stalled
> and so-on), I end up having subtracting all latest records from the original
> record so it double counts.
>
> So I've tried to use the Scrips to do this, and noticed there's already a
> user-contributed Scrip for "SetTimeWorkedAutomatically":
>
> https://rt-wiki.bestpractical.com/wiki/SetTimeWorkedAutomatically
>
> However I can't get this to work on my version of RT....
<snip>
> Can anyone help out with this?

Can't help with the scrip.

Rolling my own, I would do the following:
1) Determine the deltas.
	Join each transaction A of a ticket with each transaction B of the same ticket where A.timestamp > B.timestamp, and find the minimum delta time for each A, noting A.state as OldState, B.state as NewState, and diff(A.timestamp, B.timestamp) as Delta.
2) Determine if the delta counts.
	For each ticket, if [OldState,NewState] is countable, add to total
3) Print results.
	For each ticket, print total and other needed details.

Writing SQL for the above is likely hard.  However, a procedural method like this could be used instead:
1) Select all transactions for all relevant tickets, sorting by [Ticket Number, Timestamp]
2) Process each transaction in turn
      If the ticket number changes:
         If already on a ticket, print details.
         Set TotalTime = 0
         Set StartTime = timestamp
         Set CurrentTime = timestamp
         Set CurrentState = State
      If this transaction ends a countable time, add it:
         If [CurrentState,State] should be counted:
            Set TotalTime = TotalTime + (timestamp - CurrentTime)
      Update tracking variables:
         Set CurrentTime = timestamp
         Set CurrentState = State
3) Print the last entry
      Print details

/jeff
________________________________________________________________________
The information contained in this e-mail is for the exclusive use of the 
intended recipient(s) and may be confidential, proprietary, and/or 
legally privileged.  Inadvertent disclosure of this message does not 
constitute a waiver of any privilege.  If you receive this message in 
error, please do not directly or indirectly use, print, copy, forward,
or disclose any part of this message.  Please also delete this e-mail 
and all copies and notify the sender.  Thank you. 
________________________________________________________________________