How to get tickets that were open in May?

Asked

Viewed 719 times

6

I’m working with a database that contains all the tickets registered in the otrs of the company where I work.

I intend to do a query where I return all tickets that were open in May.

The problem is that a ticket can be opened and closed on the same day and if you try to filter through all tickets opened between the day 2015-05-01 00:00:00 and 2015-05-01 23:59:59, query will return tickets that have been opened and that have already been closed.

I am working with the following tables

  • ticket_history
  • ticket_state
  • ticket_type
  • ticket_history_type
  • ticket_state_type

I have the following query:

SELECT 
    th.id,
    th.name,
    th.history_type_id,
    th.ticket_id,
    th.type_id,
    th.queue_id,
    th.state_id,
    th.create_time,
    th.change_time

FROM 
    sapec_db.ticket_history as th
    left join ticket_state ts
    on th.state_id=ts.id
    left join ticket_type tt
    on th.type_id=tt.id
WHERE
    th.change_time between '2015-05-01 00:00:00' and '2015-05-31 23:59:59' 
    -- 4 Incident 
    and tt.name like 'Incident'
    -- 1 new 4 open
    and ts.name like 'open' 
group by 
    ticket_id
;
mysql sql

  • What defines a ticket has been closed? You have a close date in your table?

  • Good morning Pedro, what defines a closed ticket is state_id when a ticket is opened a record is created in the ticket_history table as an open ticket if the ticket undergoes any changes always recorded in that table, and when it is closed a record as closed is created, ticket status passes to closed Successful in the state_id column

  • 1

    So don’t just put it in your clause WHERE to consider only records that the state_id is different from closed? Something like that: AND ts.state_id != closed_id

  • If it has been closed and reopened then are created two lines? If that is just set the Where by state_id = closed and the date. Is there a column that dictates the ticket status as closed, or opened directly by id?

  • Peter the problem is that if I enter it clause will give me the tickets that have already been open and are currently closed, because I am working with a table that records all changes of tickets

  • Khaosdoctor in the ticket_history table are all states of tickets a ticket may have been opened at 10am today and have been closed at 11am there is a column in the ticket_history table that is called change_time that records the date and time of each change, yes whenever there is a change is recorded. with this clause will give me the tickets that have been opened but are now closed because that same day have already been open, and I just want you to give me the tickets that have state_id open

Show 1 more comment

3 answers

1

We can make a LEFT with sapec_db.ticket_history itself, excluding ticket_id that have any record of type closed Successful.

Can you please check how the command below will behave? I needed to keep only ticket_id in SELECT due to the GROUP BY you used. 

SELECT
    th.ticket_id
FROM sapec_db.ticket_history AS th
LEFT JOIN ticket_state ts
    ON th.state_id = ts.id
LEFT JOIN ticket_type tt
    ON th.type_id = tt.id
LEFT JOIN sapec_db.ticket_history AS th2
    ON th2.ticket_id = th.ticket_id
LEFT JOIN ticket_state ts2
    ON th2.state_id = ts2.id
    AND ts2.name = 'closed successful'
WHERE th.change_time BETWEEN '2015-05-01 00:00:00' AND '2015-05-31 23:59:59'
AND tt.name LIKE 'Incident'
AND ts.name LIKE 'open'
AND ts2.id IS NULL
GROUP BY th.ticket_id;

  • I’ve come up with another solution, thank you all, but it’s impossible to extract the right data this way. I created a database and imported the csv each month, instead of making querys to a production base where tickets are constantly changing status. Thank you all.

1

Just add in the WHERE clause the condition to ignore "closed tickets", from what I saw in the comments, you said they are closed when state_id = 'closed' then just add state_id != 'closed' as below:

SELECT 
    th.id,
    th.name,
    th.history_type_id,
    th.ticket_id,
    th.type_id,
    th.queue_id,
    th.state_id,
    th.create_time,
    th.change_time

FROM 
    sapec_db.ticket_history as th
    left join ticket_state ts
    on th.state_id=ts.id
    left join ticket_type tt
    on th.type_id=tt.id
WHERE
    th.change_time between '2015-05-01 00:00:00' and '2015-05-31 23:59:59' 
    -- 4 Incident 
    and tt.name like 'Incident'
    -- 1 new 4 open
    and ts.name like 'open' 
    -- remove os tickets fechado
    and state_id != 'closed'
group by 
    ticket_id
;

  • Edson, if you notice the state table (by name, in English) is ticket_state (or the alias ts), so if the condition is not removed: "and ts.name like 'open'" your code will not return what you want but only open tickets (and which are not closed rsrs)

0

I believe your select needs to contain an undercurrent when searching for the state, like...

(SELECT LAST(state_id) FROM ticket_history WHERE state_id LIKE 'open' AND id = th.id)

I don’t know if the syntax is correct, but from a query about "subquery" you should find something more accurate.

Maybe this link will help you: Related darlings

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.