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"<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
