Thau, If you are using MySQL you can use the CONCAT_WS function to output a comma-separated (or whatever separator you want to use) list of values. If you redirect the output to a text file you can easily import it into Excel. The accounted time is stored in the time_accounting table, there is a database pic in your OTRS distribution at doc/otrs-database.dia or a .png export on the OTRS ftp server at: ftp://ftp.otrs.org/pub/otrs/misc/otrs-1.1-database.png that gives you all the details about the relationships. Good luck, Tom Hesp -----Original Message----- From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]On Behalf Of Mark Mertel Sent: donderdag 1 januari 2004 22:19 To: User questions and discussions about OTRS. Subject: Re: [otrs] Accounted Time. Thau, still new to OTRS, and the SQL will vary depending on your engine. This is a close approximation of the mysql version of your query: select t0.id, datediff(now(), t0.create_time), sum(t1.time_unit), (couldn't find subject), t2.name, t3.name, t4.name t5.name from ticket t0, time_accounting t1, ticket_state t2, queue t3, system_user t4, customer_user t5 where t0.id=t1.ticket_id and t0.ticket_state_id=t2.id and t0.queue_id=t3.id and t0.user_id=t4.id and t0.customer_user=t5.id group by t0.id, datediff(now(), t0.create_time), t2.name, t3.name, t4.name t5.name On Thu, 2004-01-01 at 21:39, Thau Thai wrote:

Hi,

For accounting purposes, at the end of each month I would like to collect the following info using SQL Query:

1. Total time spent on each ticket for that month, output details: Ticket Number, Age, Accounted Time, Subject, State, Queue, Owner & CustomerID 2. Total time spent by each agent for that month, output details: Ticket Number, Age, Accounted Time, Subject, State, Queue, Owner & CustomerID

How are the tickets linked up in the otrs database (relationships)?

I have updated a ticket with Accounted time of 90 minutes. I can't find this info in the otrs database, where is it stored?

Is there a way to collect the data and output it to a text file or .xls file?

Thanks.

Regards,

Thau. _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support oder Consulting für Ihr OTRS System? => http://www.otrs.de/ -- Mark Mertel mmertel@olympus.net Mobile 206.409.2018 Phone 206.322.9074

OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support oder Consulting fr Ihr OTRS System? => http://www.otrs.de/