Thanks Gerald.
This is what we ended up with, in case it helps anyone. This gets the last week’s worth of time data, as well as a dynamic field (in our case project code) and output to a csv file in /tmp/
select t.tn ticketnumber, CONCAT(u2.first_name, " ", u2.last_name) owner,
CONCAT(u.first_name, " ", u.last_name) name, t.title title, q.name queue, t.customer_user_id customer_user,
t.customer_id customer, ta.time_unit time_unit, DATE_FORMAT(ta.create_time, "%m/%d/%y") create_time,
a.a_subject subject, a.a_body body
,df.value_text project
from otrs.ticket t
left join otrs.time_accounting ta on ta.ticket_id=t.id
left join otrs.queue q on t.queue_id = q.id
left join otrs.article a on a.id=ta.article_id
left join otrs.users u on u.id = a.create_by
left join otrs.users u2 on u2.id=t.user_id
left join otrs.dynamic_field_value df on (df.object_id=t.id AND df.field_id=12)
where ta.time_unit is not null
AND df.value_text is not null
AND ta.create_time >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND ta.create_time < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
ORDER BY create_time
INTO OUTFILE '/tmp/otrs-time.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n’
Will Sheldon
IT Manager
Office: 604 568 0313 x114
Fax: 604 568 0314
Cell: 778 689 1244
Skype: will.sheldon
#300-152 West Hastings
Vancouver, BC, V6B 1G8 Canada
www.appnovation.com
Click here to unsubscribe from further Appnovation communications.
On August 25, 2014 at 1:09:33 PM, Gerald Young (crythias@gmail.com) wrote:
Hi Will,
This might (?) be helpful.
http://forums.otterhub.org/viewtopic.php?f=60&t=12546#p50624
If you're interested in tickets that have a dynamic field value, check out http://forums.otterhub.org/viewtopic.php?f=53&t=16009#p77686
WHERE create_time BETWEEN
NOW() and DATE_SUB(NOW(), INTERVAL 7 DAY)
On Mon, Aug 25, 2014 at 4:03 PM, Will Sheldon
