Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I run the following Query:

SELECT ticket.id, th.ticket_id , th.id, th.name, th.create_time 
  FROM ticket_history th
  JOIN ticket ON  th.ticket_id =  ticket.id 
 WHERE (
        th.name LIKE  '%\%Raw\%% %\%new\%%'
        AND EXISTS (SELECT * FROM ticket_history th1 
         WHERE th1.ticket_id = ticket.id AND th1.name LIKE '%\%Close')
       )
    OR (
        th.name LIKE  '%\%Close'
        AND EXISTS (SELECT * FROM ticket_history th2 
         WHERE th2.ticket_id = ticket.id AND th2.name LIKE '%\%Raw\%% %\%new\%%')
       )
 ORDER BY th.ticket_id , th.id ASC;

I get the following output:

+----+-----------+-----+-------------------------------------------+---------------------+
| id | ticket_id | id  |                   name                    |     create_time     |
+----+-----------+-----+-------------------------------------------+---------------------+
|  3 |         3 |   5 | %%2013060810000021%%Raw%%3 normal%%new%%3 | 2013-06-08 21:59:03 |
|  3 |         3 |  82 | %%Close                                   | 2013-06-10 11:50:49 |
|  5 |         5 |  64 | %%2013060910000019%%Raw%%3 normal%%new%%5 | 2013-06-09 17:12:09 |
|  5 |         5 |  85 | %%Close                                   | 2013-06-10 11:51:10 |
|  8 |         8 |  98 | %%2013061110000014%%Raw%%3 normal%%new%%8 | 2013-06-11 19:05:06 |
|  8 |         8 | 109 | %%Close                                   | 2013-06-17 23:57:35 |
+----+-----------+-----+-------------------------------------------+---------------------+

In the above output the entry which contains "Raw" and "new" keyword show me when that particular ticket was open and the entry which contains "Close" keyword shows me when that particular ticket was closed.

What changes do I need to make in order to get the output as below:

+----+-----------+----+----------------------+---------------------+
| id | ticket_id | id |      Open time       |     Close_time      |
+----+-----------+----+----------------------+---------------------+
|  3 |         3 |  5 | 2013-06-08 21:59:03  | 2013-06-10 11:50:49 |
|  5 |         5 | 64 | 2013-06-09 17:12:09  | 2013-06-10 11:51:10 |
|  8 |         8 | 98 | 2013-06-11 19:05:06  | 2013-06-17 23:57:35 |
+----+-----------+----+----------------------+---------------------+
share|improve this question
add comment

2 Answers

You don't seem to need the ticket table. The output does contain the ticket.id column, but it is always same as ticket_history.ticket_id, which is also included in the output.

In view of the above, the following query reads only ticket_history. It uses conditional aggregation to build the output you are after:

SELECT
  ticket_id,
  MIN(id) AS id,
  MAX(CASE name WHEN '%%Close' THEN NULL ELSE create_time END) AS open_time,
  MAX(CASE name WHEN '%%Close' THEN create_time ELSE NULL END) AS close_time
FROM ticket_history
GROUP BY
  ticket_id
HAVING COUNT(name LIKE '%\%Close'            OR NULL) = 1
   AND COUNT(name LIKE '%\%Raw\%% %\%new\%%' OR NULL) = 1
;

The HAVING clause makes sure a ticket has both open and close rows. The OR NULL trick is explained in this Stack Overflow answer:

The id column is derived as MIN(id) on the assumption that a Close row will always appear after the corresponding Open one, and therefore the latter's id will always be lower than the former's.

The open_time and close_time values are obtained using conditional aggregation. Depending on whether the name column matches the '%\%Close' pattern or not, the create_time column is aggregated as either close_time or open_time.

You can use this SQL Fiddle demo to try the solution.

share|improve this answer
add comment

You can join the ticket_history table twice:

SELECT t.id, 
       th1.id AS open_id,   th1.name AS open_name,   th1.create_time AS open_time,
       th2.id AS close_id,  th2.name AS close_name,  th2.create_time AS close_time
  FROM ticket AS t
    JOIN ticket_history AS th1 
      ON  th1.ticket_id = t.id 
      AND th1.name LIKE '%\%Raw\%% %\%new\%%'                   
    LEFT JOIN ticket_history AS th2 
      ON  th2.ticket_id = t.id
      AND th2.name LIKE '%\%Close'  
 ORDER BY t.id , th1.id ;

The above will also show the opened but not closed yet tickets. If you want only the closed ones, change the LEFT join to INNER join.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.