open ticket report monthly

[kalyana1315]

Hi ,

we are using last few month OTRS. now trying monthly report but report showing wrong data.

if any one month ticket is open and other month ticket close.final report showing only close status.

Please any help how to find SQL query month wise that means one ticket worked two months we need two moth last status of tickets.

[crythias]

eh.
What do you want?
Define your parameters in English....
In the last two months, I need the State of all tickets? That seems big...
In the last two months, I need the State of all tickets that have been closed in the last two months? Oh... but they're closed.
In the last two months, I need the State of all tickets that have been created in the last two months?

Code: Select all

select tn, title, ts.name from ticket left join ticket_state ts on ts.id=ticket.ticket_state_id where ticket.create_time between "2014-02-01" and "2014-04-01"

[crythias]

Also, viewtopic.php?f=53&t=14242#p67562

[kalyana1315]

Also, viewtopic.php?f=53&t=14242#p67562

Hi crythaias,

Code: Select all

SELECT u.login owner, ts.name state, th.name fromto, th.create_time createtime, th.ticket_id ticket_id
FROM  `ticket_history` th
LEFT JOIN users u ON u.id = th.owner_id
LEFT JOIN ticket_state ts ON ts.id = th.state_id
WHERE th.history_type_id =  '27'
OR th.history_type_id = '1'
ORDER BY ticket_id

above query show same Ticket id multiple status and create time . we are trying monthly report ticket status of last update.

Example:

ticket_id -- create time -- state
1 -- 12-02-2014 -- new
1 -- 16-02-2014 -- Open
1 -- 17-02-2014 -- hold(open)
1 -- 1-03-2014 -- close

we are trying Report is :

if month of february report
1 -- 17-02-2014 -- hold(open)

if month of March report

1 -- 1-03-2014 -- close


any know help this query of monthly report ticket status

[kalyana1315]

Code: Select all

SELECT ticket.tn, ticket.id,ticket_history.state_id,ticket_state.name, max(ticket_history.change_time) AS change_time 
FROM ticket_history 
LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) 
LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id)
 LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id)
 WHERE ticket_history.history_type_id = 27 AND 
ticket_state_type.name !="closed" 
GROUP BY ticket.tn;

At the moment it just groups the records and only gets the max date and not the row associated with the max date

[crythias]

At the moment it just groups the records and only gets the max date and not the row associated with the max date

According to my demo data, it pulls 5 columns. For every ticket number, it shows the state of the ticket.

[kalyana1315]

At the moment it just groups the records and only gets the max date and not the row associated with the max date

According to my demo data, it pulls 5 columns. For every ticket number, it shows the state of the ticket.

database:

'100005', ----------- '6', ----------- 'open', ---------------'2013-06-21 12:43:09'
'100005', -----------'6', -----------'review update',- --- '2013-06-21 12:44:44'
'100005', -----------'6', ----------- 'work in progress',--- '2013-06-21 12:47:03'
'100005', ----------- '6', -----------'open', ----------- ----'2013-06-21 13:04:31'
'100005', ----------- '6', ----------- 'Hold', ----------- ----'2013-06-21 13:16:46'
'100005', ----------- '6', ----------- 'review update',----- '2013-06-21 16:30:19'
'100045', -----------'46', ---------- 'work in progress',--- '2013-08-28 19:36:40'
'100045', ----------- '46', ----------- 'Hold', -------------- '2013-10-15 15:47:03'
'100045', ----------- '46', ----------- 'work in progress',--- '2013-12-11 13:03:22'
'100045', ----------- '46', -----------'closed', ----------- '2014-02-10 13:50:20'

output :

100005 ----------- 6 ----------- open --------------- 2013-06-21 16:30:19
100045 ----------- 46 ----------work in progress-- 2014-02-10 13:50:20

only gets the max date and not the row associated with the max date ?

[crythias]

order by change_time desc

[kalyana1315]

order by change_time desc

Hi crythias ,

change_time or create time shows correct but other rows first value only show this query

[crythias]

Sorry... let me search this as it's not an OTRS question.

[kalyana1315]

Sorry... let me search this as it's not an OTRS question.

hi

any SQL query for OTRS Number open ticket last month with last update state status.

[crythias]

The data you seek is OTRS data, yes. The sql query to obtain that information is not unique to OTRS.

You are using a query to grab distinct or group by based upon the max value of a column (regardless of data) for that group.

That's not an OTRS question. That's a SQL question.

[crythias]

from the search, I found this post
which means ...

Code: Select all

SELECT ticket.tn, ticket.id,ticket_history.state_id,ticket_state.name, ticket.change_time AS change_time FROM ticket_history LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) INNER JOIN (SELECT ticket.tn, MAX(ticket.change_time) as max_change FROM ticket_history LEFT JOIN ticket on ticket.id = ticket_history.ticket_id group by ticket.tn) grpmax ON ticket.tn=grpmax.tn AND ticket.change_time = grpmax.max_change LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id) LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id) WHERE ticket_history.history_type_id = 27 AND ticket_state_type.name !="closed" GROUP BY ticket.tn

[kalyana1315]

from the search, I found this post
which means ...

Code: Select all

SELECT ticket.tn, ticket.id,ticket_history.state_id,ticket_state.name, ticket.change_time AS change_time FROM ticket_history LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) INNER JOIN (SELECT ticket.tn, MAX(ticket.change_time) as max_change FROM ticket_history LEFT JOIN ticket on ticket.id = ticket_history.ticket_id group by ticket.tn) grpmax ON ticket.tn=grpmax.tn AND ticket.change_time = grpmax.max_change LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id) LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id) WHERE ticket_history.history_type_id = 27 AND ticket_state_type.name !="closed" GROUP BY ticket.tn

Hi crythias,

above query also show wrong values. ticket change time showing last change time and same problem not the row associated with the max date

[crythias]

Code: Select all

SELECT ticket.tn,
       ticket.id,
       ticket_history.state_id,
       ticket_state.name,
       ticket.change_time AS change_time
FROM   ticket_history
       LEFT JOIN ticket
              ON ( ticket.id = ticket_history.ticket_id )
       INNER JOIN (SELECT ticket.tn,
                          Max(ticket.change_time) AS max_change
                   FROM   ticket_history
                          LEFT JOIN ticket
                                 ON ticket.id = ticket_history.ticket_id
       LEFT JOIN ticket_state
              ON ( ticket_state.id = ticket_history.state_id )
       LEFT JOIN ticket_state_type
              ON ( ticket_state_type.id = ticket_state.type_id )
                   WHERE  ticket_history.history_type_id = 27
                                    AND ticket_state_type.name != "closed"
                   GROUP  BY ticket.tn) grpmax
               ON ticket.tn = grpmax.tn
                  AND ticket.change_time = grpmax.max_change
       LEFT JOIN ticket_state
              ON ( ticket_state.id = ticket_history.state_id )
       LEFT JOIN ticket_state_type
              ON ( ticket_state_type.id = ticket_state.type_id )
WHERE  ticket_history.history_type_id = 27
       AND ticket_state_type.name != "closed"
GROUP  BY ticket.tn

[kalyana1315]

Code: Select all

SELECT ticket.tn,
       ticket.id,
       ticket_history.state_id,
       ticket_state.name,
       ticket.change_time AS change_time
FROM   ticket_history
       LEFT JOIN ticket
              ON ( ticket.id = ticket_history.ticket_id )
       INNER JOIN (SELECT ticket.tn,
                          Max(ticket.change_time) AS max_change
                   FROM   ticket_history
                          LEFT JOIN ticket
                                 ON ticket.id = ticket_history.ticket_id
       LEFT JOIN ticket_state
              ON ( ticket_state.id = ticket_history.state_id )
       LEFT JOIN ticket_state_type
              ON ( ticket_state_type.id = ticket_state.type_id )
                   WHERE  ticket_history.history_type_id = 27
                                    AND ticket_state_type.name != "closed"
                   GROUP  BY ticket.tn) grpmax
               ON ticket.tn = grpmax.tn
                  AND ticket.change_time = grpmax.max_change
       LEFT JOIN ticket_state
              ON ( ticket_state.id = ticket_history.state_id )
       LEFT JOIN ticket_state_type
              ON ( ticket_state_type.id = ticket_state.type_id )
WHERE  ticket_history.history_type_id = 27
       AND ticket_state_type.name != "closed"
GROUP  BY ticket.tn

crythias thank you very much your help above query some error i got

Error Code: 1054. Unknown column 'ticket_state_type.name' in 'on clause'

i am working this query i will update soon

[crythias]

unable to replicate. copy/paste in phpmyadmin yielded no errors.

[kalyana1315]

unable to replicate. copy/paste in phpmyadmin yielded no errors.

Hi

final i got result the row associated with the max date

Code: Select all

SELECT ticket.tn,
       ticket.id,
       ticket_history.state_id,
       ticket_state.name,
       ticket_history.change_time AS change_time
FROM   ticket_history
       LEFT JOIN ticket
              ON ( ticket.id = ticket_history.ticket_id )
       INNER JOIN (SELECT ticket.tn,
                          Max(ticket_history.change_time) AS max_change
                   FROM   ticket_history
                          LEFT JOIN ticket
                                 ON ticket.id = ticket_history.ticket_id
                                   --  AND ticket_state_type.name != "closed"
                   WHERE  ticket_history.history_type_id = 27
                    or ticket_history.history_type_id =1
                   GROUP  BY ticket.tn) grpmax
               ON ticket.tn = grpmax.tn
                  AND ticket_history.change_time = grpmax.max_change
       LEFT JOIN ticket_state
              ON ( ticket_state.id = ticket_history.state_id )
       LEFT JOIN ticket_state_type
              ON ( ticket_state_type.id = ticket_state.type_id )
WHERE  ticket_history.history_type_id = 27
      or ticket_history.history_type_id = 1
     -- AND ticket_state_type.name != "closed"
GROUP  BY ticket.id

now i am trying avoid closed ticket ..........

[crythias]

avoid it in the inner join.

[kalyana1315]

avoid it in the inner join.

final

Open Ticket :

Code: Select all

SELECT ticket.tn,
       ticket.id,
       ticket_history.state_id,
       ticket_state.name,
       ticket_history.change_time AS change_time
FROM   ticket_history
       LEFT JOIN ticket
              ON ( ticket.id = ticket_history.ticket_id )
       inner JOIN  (SELECT ticket.tn,
                          Max(ticket_history.change_time) AS max_change
                   FROM   ticket_history
                          LEFT JOIN ticket
                                 ON ticket.id = ticket_history.ticket_id
					LEFT JOIN ticket_state
              ON ( ticket_state.id = ticket_history.state_id )
       LEFT JOIN ticket_state_type
              ON ( ticket_state_type.id = ticket_state.type_id )
                                   
                   WHERE ( ticket_history.history_type_id = 27
                or ticket_history.history_type_id =1)
                   AND ticket_state_id != "closed"
                   GROUP  BY ticket.tn) grpmax
               ON ticket.tn = grpmax.tn
                  AND ticket_history.change_time = grpmax.max_change
       LEFT JOIN ticket_state
              ON ( ticket_state.id = ticket_history.state_id )
       LEFT JOIN ticket_state_type
              ON ( ticket_state_type.id = ticket_state.type_id )
WHERE  (ticket_history.history_type_id = 27
     or ticket_history.history_type_id = 1)
    AND ticket_state_type.name != "closed"
GROUP  BY ticket.id

Close ticket:

Code: Select all

SELECT * FROM ticket where ticket_state_id = 2 

or

Code: Select all

SELECT ticket.tn, ticket.id,ticket_history.state_id,ticket_state.name, max(ticket_history.change_time) AS change_time 
FROM ticket_history 
LEFT JOIN ticket ON (ticket.id = ticket_history.ticket_id) 
LEFT JOIN ticket_state ON (ticket_state.id = ticket_history.state_id)
 LEFT JOIN ticket_state_type ON (ticket_state_type.id = ticket_state.type_id)
 WHERE ticket_history.history_type_id = 27 AND 
ticket_state_type.name ="closed" 
GROUP BY ticket.id;

New ticket :

Code: Select all

SELECT * FROM ticket 
LEFT JOIN ticket_state
              ON ( ticket_state.id = ticket.ticket_state_id )
where ticket_state.name = 'new'