I'm trying to figure out how to write a simple data extractor for our business dept. I create an ASP page that reads the database, which in turn can then be read into an Excel Spreadsheet via a web query by our manager. This is easily accomplished with the MySQL ODBC connector. (I do it this way so I can update the ASP without having to redistribute new spreadsheets every time the data methodology changes)
I have a SQL statement (see below) that almost does what I want. The goal is to calculate all the tickets with time assigned to them to see what the total time spent was, which will be read into a spreadsheet for further manipulation. What I'd like to do is add a field with the first 30 chars or so of the body of the first contact with the creator - usually this is enough of a description of the problem for our business manager to provide a quick and easy referral back without looking it up in OTRS, useful if there are many tickets to go through. We're using the subject to refer to the physical location of ticket site, as this is used for installations (i.e. Atlanta, Dallas, Toronto) so that's not available for this purpose. Because MySQL 4, which is what is bundled with OTRS and what we are using does not support sub queries - which I could use to do this, I was wondering if anyone else has run into this and if there is a SQL maven out there who may be able to suggest a solution, or at least a direction towards a solution. Regards, Keith THE SQL: SELECT tn AS 'Ticket Number', queue.name AS 'Queue', SUM(time_unit) AS 'Total Time (Hours)', title AS 'Subject', ticket.create_time AS 'Ticket Create Time', CONCAT(customer_user.first_name, ' ',customer_user.last_name) AS 'Customer Name', company AS 'Company', ticket.customer_id AS 'Customer Email', telephone AS 'Customer Phone', CONCAT(system_user.first_name, ' ',system_user.last_name) AS 'Agent Name' FROM time_accounting, ticket, system_user, customer_user, queue WHERE ticket.id = ticket_id AND system_user.id = ticket.change_by AND ticket.customer_id = customer_user.customer_id AND system_user.id = time_accounting.change_by AND queue.id = ticket.queue_id GROUP BY tn, title, ticket.create_time, queue.name, ticket.customer_id, company, telephone, customer_user.first_name, customer_user.last_name ______________________________________________________________________ _______________________________________________ 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/
