How to obtain the tickets that were opened in the month of May?

Question

How to obtain the tickets that were opened in the month of May?

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

6

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

I want 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 for all tickets opened between day 2015-05-01 00:00:00 and 2015-05-01 23:59:59 , the query will return the tickets that were opened and have already been closed.

I'm 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

asked by anonymous 16.12.2015 / 12:02

3 answers

Conflicts with the ConfideUser class Rename the url with the contents of the database

score 1 · Answer 1

We can make a LEFT with sapec_db.ticket_history itself, excluding ticket_id that has some closed-type record.

Can you please check how the command below will behave? I needed to keep only the ticket_id in SELECT because of GROUP BY that 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;

score 1 · Answer 2

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
;

score 0 · Answer 3

I believe your select needs to contain a subquery when it searches the state, type ...

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

I do not know if the syntax is correct, but of a searched for "subquery" that you should find something more precise.

Maybe this link will help you: Related queries