If you want to include the ticket owner at the same time as the agent who added time, here's the query:
Code: Select all
select t.tn ticketnumber, t.user_id ownerid, CONCAT(u2.first_name, " ", u2.last_name) owner, a.create_by user_id, 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 from ticket t left join time_accounting ta on ta.ticket_id=t.id left join queue q on t.queue_id = q.id left join article a on a.id=ta.article_id left join users u on u.id = a.create_by left join users u2 on u2.id=t.user_id where ta.time_unit is not null order by create_time
ticketnumber, ticketownerid, ticketownername, articlecreatoruser_id, articlecreatorname, titleofticket, queue, customerusername, customercompanyname (or customer_id), howMuchTimeRecorded, whatDateArticlecreated, subjectofarticlethathastime, bodyofarticle
This is rather extensive. In general, you should at this point know a bunch of things with regard to the ticket. If you want more information, in the sql box, DESCRIBE ticket and clear the LIMIT. You should be able to add *any* field from ticket in this list. Note that some fields are different from the display names. freekey1, freetext1, for example-- just add t.freekey1 or t.freetext1 in the list. You can do the same thing with article (a.a_freekey1, a.a_freetext1).
If you're wanting to get names for ticket_state_id, for instance, you'll want to put
t.ticket_state_id state_id, s.name state,
in the top somewhere after select and then something like
left join ticket_state s on s.id=t.ticket_state_id
somewhere after ticket t and before "where".
If you don't follow what I'm saying, I would completely understand your confusion here.
here's a breakdown of what is being accomplished (notes after #. Do not copy and use the following.):
#sql command. what are we going to do? Select what follows
# I'm using shorthand here. t stands for the ticket table. tn is the field in ticket for ticket number. ticketnumber is a label for the output
CONCAT(u2.first_name, " ", u2.last_name) owner,
#CONCAT is a mysql command to concatenate (or "smoosh together") stuff that follows separated by commas.
#u2 is a shortcut to a copy of the users table that is different from the one I will use later.
#This time I'm looking up the owner of the ticket.
#owner is the label for the column
CONCAT(u.first_name, " ", u.last_name) name,
#same as before, but this is because I'm looking up the user for the agent who added the article with time
#use the q result to give me the name of the queue and put it in a column named queue
#customer_user is the username of the customer who submitted the ticket
#customer_id is the customer_id (maybe an email address, maybe a company name) of the customer attached to the ticket
#this is what was entered as time units for a given article
DATE_FORMAT(ta.create_time, "%m/%d/%y") create_time,
#this is a mysql command that formats the create_time as month/date/year. If you need to change that, you can look up formats in mysql help)
#this is the subject of the article that has had time added. Chances are it's a reply or a note, but it may be the first entry.
#this is the full body of the article. It may not be useful in a spreadsheet. Don't use it if you don't want it.
#from is "where are we getting the data from?"
#this is shorthand for "use 't' instead of typing out the whole word 'ticket'"
left join time_accounting ta on ta.ticket_id=t.id
#use "ta" instead of time_accounting, and return all the time_accounting entries where the time_accounting.ticket_id is the same as the current ticket id.
left join queue q on t.queue_id = q.id
#use "q" instead of queue, and return the record(s) in queue for the ticket's queue_id. (Useful to lookup a queue name)
left join article a on a.id=ta.article_id
#give me the article that matches the time_accounting article_id. I need to know that article's creator.
left join users u on u.id = a.create_by
#the article's create_by is who made the time entry. I'm using the users table to get the first and last name for that user.
left join users u2 on u2.id=t.user_id
#the ticket's user_id is who owns the ticket. I'm using the users table to get information, but since I've used u already, I added u2 for this purpose.
#where is mysql speak for "what conditions?"
ta.time_unit is not null
#I'm only reporting on things where I have time submitted.
#"how do I sort this?"
#I got sloppy here, but it's the create_time of the entry in time_accounting