[rt-users] Saved Search with more information

[Scott Benson]

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
>