[rt-users] Saved Search with more information

[Ruslan Zakirov]

If everything works as expected then ticket's time worked field should hold
correct sum. This value you can display in search results without custom
code.

Regards, Ruslan. From phone.
08.08.2011 22:39 пользователь "Scott Benson" <sbenson at a-1networks.com>
написал:
> Thanks for the reply Gerard. Not sure what ColumnMap is, but basically
> what we're looking to do is only list tickets in a "custom search" that
> have had time worked, within a certain time frame. Also showing the
> Sum(Transactions.TimeWorked) as a field.
>
> This can be accomplished via Mysql because you can join databases and
> select data on a per transaction level.
>
> Example Mysql Code:
> select Tickets.EffectiveId, Tickets.Subject, Tickets.LastUpdated,
> Transactions.Created, sum(Transactions.TimeTaken) from Tickets left join
> Transactions on Tickets.id = Transactions.ObjectId where
> Transactions.Created between DATE_FORMAT(NOW(),"%Y-%m-01") - interval 0
> month and DATE_FORMAT(NOW(),"%Y-%m-01") - interval -1 month group by
> Tickets.EffectiveId;
>
> Example Mysql Output:
>
>
+-------------+--------------------+--------------------------+-------------------------+-------------------------------------+

>
> | EffectiveId | Subject | LastUpdated |
> Created | sum(Transactions.TimeTaken) |
>
+-------------+--------------------+--------------------------+-------------------------+-------------------------------------+

>
> | 21984 | Example Ticket 1 | 2011-08-03 23:56:50 | 2011-08-03
> 00:02:50 | 510 |
> | 23322 | Example Ticket 2 | 2011-08-06 00:11:39 | 2011-08-03
> 00:52:14 | 480 |
> | 25497 | Example Ticket 3 | 2011-08-01 22:25:10 | 2011-08-01
> 22:25:10 | 180 |
> | 29560 | Example Ticket 4 | 2011-08-02 00:16:59 | 2011-08-02
> 00:16:58 | 45 |
>
+-------------+--------------------+--------------------------+-------------------------+-------------------------------------+

>
> 4 rows in set (0.13 sec)
>
> I hope this information helps.
>
> --
> Scott Benson
> A1 Networks
> (707)570-2021 x203
>
>
> On 8/8/11 8:34 AM, Gerard FENELON wrote:
>> I am not sure but you might be looking for ColumnMap
>> Gerard
>>
>> On 2011-08-05 17:43, Scott Benson wrote:
>>> No update on this?
>>>
>>> --
>>> Scott Benson
>>> A1 Networks
>>> (707)570-2021 x203
>>>
>>>
>>> On 8/2/11 2:13 PM, Scott Benson wrote:
>>>> Is it possible to get custom information inside the "Query
>>>> Builder"? We are trying to get "time worked" based on transactions
>>>> between a time frame to show in a custom search. Basically we want
>>>> to have a page for clients to log in with their user/pass and see a
>>>> page that shows, tickets with time added between 2011-07-01 and
>>>> 2011-07-31, and run a sum on the time. We have a query that works,
>>>> but it requires joining Tickets and Transactions, and selecting
>>>> information from the Transactions database. Does anyone know of a
>>>> way that this can be accomplished. Thanks in advance.
>>
>>
>> --------
>> 2011 Training: http://bestpractical.com/services/training.html
>>
>
> --------
> 2011 Training: http://bestpractical.com/services/training.html
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110808/6900d528/attachment.htm>