lundi 29 juin 2015

Select Last Distinct Value and TIMEDIFF Based on Selection?

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?

Aucun commentaire :

Enregistrer un commentaire