[rt-users] Saved Search with more information

[Scott Benson]

i don't want the entirety of the time worked...only time worked based on 
the month.  IE Time worked this month 30mins, time worked last month 255 
minutes.  Hence the "Transactions.Created between 
DATE_FORMAT(NOW(),"%Y-%m-01") - interval 0 month and 
DATE_FORMAT(NOW(),"%Y-%m-01") - interval -1 month"

-- 
Scott Benson
A1 Networks
(707)570-2021 x203


On 8/8/11 12:37 PM, Ruslan Zakirov wrote:
> 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
>>      
>