Looking for MySQL query to select "Created in Queue" value

[nking]

I am working on a query to include in web page that needs to show information regarding ticket created in specific queues. I figured out everything else: how to select customer, date range, states. But if I use value query_id like below:

Code: Select all

select * from ticket where queue_id = '12';

it includes current Queue ticket is in, NOT the Queue it was created in... How do I do that?

[jojo]

you need to analyse the ticket_history for this...

[nking]

At ticket_history table I found history_type_id value that represents updating Queue... But how do I tie it all together. This is what I have so far to get what I need:

Code: Select all

mysql> select * from ticket where queue_id = '12' and customer_id = 'something' and create_time between date_sub(now(), interval 1 month) and now() and ticket_state_id = '2';

But like I said it selects current Queue.

[jojo]

in the history table you will find entries like this:
id: 6
name: %%2012091210000018%%Junk%%3 normal%%open%%2
history_type_id: 1
ticket_id: 2
article_id: NULL
type_id: 1
queue_id: 3
owner_id: 1
priority_id: 3
state_id: 4
valid_id: 1
create_time: 2012-09-12 22:03:30
create_by: 1
change_time: 2012-09-12 22:03:30
change_by: 1

type_id: 1 is for newly created tickets....

[nking]

I can query for particular name in column name in table ticket_history but how do I narrow results down to that particular client only or to ticket number?

Code: Select all

mysql> describe ticket_history;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name            | varchar(200) | NO   |     | NULL    |                |
| history_type_id | smallint(6)  | NO   | MUL | NULL    |                |
| ticket_id       | bigint(20)   | NO   | MUL | NULL    |                |
| article_id      | bigint(20)   | YES  | MUL | NULL    |                |
| type_id         | smallint(6)  | NO   | MUL | NULL    |                |
| queue_id        | int(11)      | NO   | MUL | NULL    |                |
| owner_id        | int(11)      | NO   | MUL | NULL    |                |
| priority_id     | smallint(6)  | NO   | MUL | NULL    |                |
| state_id        | smallint(6)  | NO   | MUL | NULL    |                |
| valid_id        | smallint(6)  | NO   | MUL | NULL    |                |
| create_time     | datetime     | NO   | MUL | NULL    |                |
| create_by       | int(11)      | NO   | MUL | NULL    |                |
| change_time     | datetime     | NO   |     | NULL    |                |
| change_by       | int(11)      | NO   | MUL | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
15 rows in set (0.02 sec)

[jojo]

you have the ticket_id and the queue_id in the table also...

[crythias]

select queue.name, ticket.tn from ticket_history left join ticket on ticket.id=ticket_history.ticket_id left join queue on queue.id=ticket_history.queue_id where ticket_history.history_type_id=1 and ticket.customer_id="myuser"

[nking]

I suppose I can use some kind of statement like...

Code: Select all

LEFT JOIN ticket_history USING queue_id WHERE ticket_history.queue_id = 12; 

12 is the queue id I am looking for.

[nking]

Thank you! Looks like I am on the right path after all

[nking]

So my task now is to include columns:

create_time
change_time

I understand I can't get close_time timestamp unless I implement that timestamp. I have done that before, will have to dig a little and do it again...

[crythias]

close_time is calculated from ticket_history of last state change to type closed.