Finding the difference in days between different entries with same ticket

Question

I have the following table:

ticket    arrivaltime        resolution time    status

t1        '2012-09-03'        null               new
t2        '2012-09-04'        null               new
t1        (can be anything)   '2012-09-14'       verified    
t1        (can be anything)   '2012-09-16'       verified
t2        (can be anything)   '2012-09-06'       verified

Now I want to find, for example, for t1 the difference in days:

Age(t1)=(resolution time of last entry) - (arrival time of first entry)

Similarly I'll find it for t2.

How can I find this?

By "first" and "last", do you mean the latest arrival time minus the earliest arrival time? Or, are you referring to the ordering of entries in the table? — Gordon Linoff, Sep 21 '12 at 19:27
first means the "first entry" of that ticket and last means last entry of that ticket — Bhavik Shah, Oct 12 '12 at 04:28

score 0 · Accepted Answer · answered Sep 21 '12 at 11:32

0

You can do something like:

SELECT TICKET, DATE_DIFF(MAX(RESOLUTION_TIME) - MIN(ARRIVAL_TIME)) AS DIFF FROM TABLE GROUP BY TICKET

answered Sep 21 '12 at 11:32

SexyBeast

7,913
28
108
196

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

Refer the following link regarding syntax

Date Difference in MySQL to calculate age

SELECT TICKET, DATE_DIFF(MAX(RESOLUTION_TIME),MIN(ARRIVAL_TIME)) From TABLE

edited May 23 '17 at 12:12

Community

1
1

answered Sep 21 '12 at 11:37

Freelancer

9,008
7
42
81

But how would I get it for all the tickets in one query – Bhavik Shah Oct 12 '12 at 04:29

Finding the difference in days between different entries with same ticket

2 Answers2