Hi All,
I have this test table
+----------+------+------+------+--------+----------+
| username | key1 | key2 | key3 | method | timer |
+----------+------+------+------+--------+----------+
| nhadie | aaa | bbb | ccc | INVITE | 00:00:10 |
| nhadie | aaa | bbb | ccc | ACK | 00:00:14 |
| nhadie | aaa | bbb | ccc | INVITE | 00:00:16 |
| nhadie | aaa | bbb | ccc | ACK | 00:00:20 |
| nhadie | aaa | bbb | ccc | INVITE | 00:00:22 |
| nhadie | aaa | bbb | ccc | ACK | 00:00:26 |
| nhadie | aaa | bbb | ccc | BYE | 00:00:42 |
| nhadie | ddd | fff | eee | INVITE | 00:00:20 |
| nhadie | ddd | fff | eee | ACK | 00:00:24 |
| nhadie | ddd | fff | eee | INVITE | 00:00:26 |
| nhadie | ddd | fff | eee | ACK | 00:00:30 |
| nhadie | ddd | fff | eee | INVITE | 00:00:32 |
| nhadie | ddd | fff | eee | ACK | 00:00:36 |
| nhadie | ddd | fff | eee | BYE | 00:00:52 |
+----------+------+------+------+--------+----------+
I tried this query:
select t1.username as username, t1.method as t1method, t2.method as
t2method, t1.timer as start,
t2.timer as stop, unix_timestamp(t2.timer)-unix_timestamp(t1.timer) as
timediff
from test t1,test t2 where (t1.method = 'INVITE' and t2.method = 'BYE') and
((t1.key1 = t2.key1)
and (t1.key2 = t2.key2) and (t1.key3 = t2.key3));
and this is the result:
+----------+----------+----------+----------+----------+----------+
| username | t1method | t2method | start | stop | timediff |
+----------+----------+----------+----------+----------+----------+
| nhadie | INVITE | BYE | 00:00:10 | 00:00:42 | 00:00:32 |
| nhadie | INVITE | BYE | 00:00:16 | 00:00:42 | 00:00:26 |
| nhadie | INVITE | BYE | 00:00:22 | 00:00:42 | 00:00:20 |
| nhadie | INVITE | BYE | 00:00:20 | 00:00:52 | 00:00:32 |
| nhadie | INVITE | BYE | 00:00:26 | 00:00:52 | 00:00:26 |
| nhadie | INVITE | BYE | 00:00:32 | 00:00:52 | 00:00:20 |
+----------+----------+----------+----------+----------+----------+
but what i want to actually achieve is to get only the time of the first
INVITE and then it's corresponding BYE
so it should look like this
+----------+----------+----------+----------+----------+----------+
| username | t1method | t2method | start | stop | timediff |
+----------+----------+----------+----------+----------+----------+
| nhadie | INVITE | BYE | 00:00:10 | 00:00:42 | 00:00:32 |
| nhadie | INVITE | BYE | 00:00:20 | 00:00:52 | 00:00:32 |
-------------------------------------------------------------------
Is that possible? What else can I do to have this output?
Thank You in Advanced.
Regards,
Nhadie
________________________________________________
Message sent using UebiMiau 2.7
