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" <sben...@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
-------- 2011 Training: http://bestpractical.com/services/training.html
