0

I'm trying to setup a query that does a DATEDIFF between two times, based on when the last unique value in one column is present. The data is structured as follows:

  row   ticket_id   create_time      change_time    owner_id     queue_id
 1         11234    5/12/2014 13:47 5/12/2014 13:47        2        4
 2         11234    5/12/2014 13:47 5/12/2014 13:47        2        4
 3         11234    5/12/2014 13:47 5/12/2014 13:47        8        11
 4         11234    5/12/2014 13:47 5/12/2014 13:47        8        11
 5         11234    5/12/2014 14:02 5/12/2014 14:02        3        9
 6         11234    5/12/2014 14:10 5/12/2014 14:10       17        5
 7         11234    5/14/2014 12:00 5/14/2014 12:00       17        5
 8         11234    5/15/2014 12:27 5/15/2014 12:27       17        5

Basically, I want to do a datediff between rows 6 and 8 for the "change_time" column. I want to select the final distinct number in either the owner_id column or queue_id column for each ticket_id and calculate the difference in change times. Is there a way this could be setup using MySQL? Using a MAX() function won't work unfortunately because highest and second highest change times are not always associated with the final queue id or owner id. I know in SAS a similar operation can be performed using a combination of do loops and counter+1, but is something like this possible with SQL?

Galma88
  • 2,398
  • 6
  • 29
  • 50
tylerpol
  • 11
  • 3

1 Answers1

0

Use a subquery to get the last owner_id for each ticket_id, using one of the techniques in SQL Select only rows with Max Value on a Column. JOIN this back with the table to get the first and last change time for this ticket and owner.

SELECT t1.ticket_id, DATEDIFF(MAX(t1.change_time), MIN(t1.change_time)) AS diff
FROM YourTable AS t1
JOIN (SELECT a.ticket_id, a.owner_id AS last_owner
      FROM YourTable AS a
      JOIN (SELECT ticket_id, MAX(row) AS max_row
            FROM YourTable
            GROUP BY ticket_id) AS b
      ON a.ticket_id = b.ticket_id AND a.owner_id = b.max_row) AS t2
ON t1.ticket_id = t2.ticket_id AND t1.owner_id = t2.last_owner
GROUP BY t1.ticket_id

DEMO

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks! But I beleive the MAX() function will take the highest value in the column, rather than the final distinct value. In the table above on the queue column, it would take the DATEDIFF() for queue_id 11, rather than 5, which is what I'm looking for. Any other ideas/suggestions? – tylerpol Jun 30 '15 at 13:10
  • Apparently I misunderstood, because I thought that "final distinct value" meant the highest one. – Barmar Jun 30 '15 at 15:27