Hi All,
Given this table:
+-----------------------------------------------------------------+-----------------------------------------------------------------+------------+------------+-
|sip_status | sip_method | sip_callid | username |
fromtag | totag | time | timestamp
|
+-----------------------------------------------------------------+-----------------------------------------------------------------+------------+------------+-
|200 | INVITE | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:27:39 | 2006-06-19
05:27:39 |
|200 | ACK | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:27:39 | 2006-06-19
05:27:39 |
|200 | INVITE | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:27:59 | 2006-06-19
05:27:59 |
|200 | ACK | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:00 | 2006-06-19
05:28:00 |
|200 | INVITE | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:15 | 2006-06-19
05:28:15 |
|200 | ACK | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:15 | 2006-06-19
05:28:15 |
|200 | INVITE | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:16 | 2006-06-19
05:28:16 |
|200 | ACK | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:16 | 2006-06-19
05:28:16 |
|200 | INVITE | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:16 | 2006-06-19
05:29:16 |
|200 | ACK | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:17 | 2006-06-19
05:29:17 |
|406 | REFER | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:19 | 2006-06-19
05:29:19 |
|200 | BYE | [EMAIL PROTECTED] | 0061396962022
|3359683324-99483 | e8936439e4bdfb0co0 | 2006-06-19 13:29:19 | 2006-06-19
05:29:19 |
|481 | BYE | [EMAIL PROTECTED] | 5743
|e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:19 | 2006-06-19
05:29:19 |
|200 | INVITE | [EMAIL PROTECTED] | 5093
| 73af10c095f4a93do1 | 3359711691-612956 | 2006-06-19 21:20:25 | 2006-06-19
13:20:25 |
|200 | ACK | [EMAIL PROTECTED] | 5093
| 73af10c095f4a93do1 | 3359711691-612956 | 2006-06-19 21:20:25 | 2006-06-19
13:20:25 |
|481 | BYE | [EMAIL PROTECTED] | 5093
| 73af10c095f4a93do1 | 3359711691-612956 | 2006-06-19 21:22:11 | 2006-06-19
13:22:11 |
|481 | BYE | [EMAIL PROTECTED] | 0027164306000
| 3359711691-612956 | 73af10c095f4a93do1 | 2006-06-19 21:22:14 | 2006-06-19
13:22:14 |
+------------------------------------------------------------------+------------------------------------------------------------------+------------+------------+---
I tried this query:
SELECT
t1.sip_callid as callid, t1.username as username,
t1.sip_method as t1meth, t2.sip_method as t2meth, t3.sip_method as t3meth,
t1.time as start, t2.time as stop,
TIMEDIFF(t2.time,t1.time) as timediff
FROM acc AS t1
INNER JOIN acc AS t2
ON t1.sip_callid = t2.sip_callid AND ((t1.fromtag = t2.fromtag AND t1.totag
= t2.totag) OR (t1.fromtag = t2.totag AND t1.totag = t2.fromtag))
LEFT JOIN acc AS t3
ON t1.sip_callid = t3.sip_callid AND ((t1.fromtag = t2.fromtag AND t1.totag
= t2.totag) OR (t1.fromtag = t2.totag AND t1.totag = t2.fromtag)) AND
t3.sip_method='INVITE' AND t1.time > t3.time
WHERE t1.sip_method='INVITE' AND t2.sip_method='BYE' AND t3.sip_method IS
NULL;
but i still get this result
+----------------------------------+----------+--------+--------+--------+---------------------+---------------------+----------+
| callid | username | t1meth | t2meth | t3meth |
start | stop | timediff |
+----------------------------------+----------+--------+--------+--------+---------------------+---------------------+----------+
| [EMAIL PROTECTED] | 5093 | INVITE | BYE | NULL |
2006-06-19 21:20:25 | 2006-06-19 21:22:11 | 00:01:46 |
| [EMAIL PROTECTED] | 5093 | INVITE | BYE | NULL |
2006-06-19 21:20:25 | 2006-06-19 21:22:14 | 00:01:49 |
| [EMAIL PROTECTED] | 5743 | INVITE | BYE | NULL |
2006-06-19 13:27:39 | 2006-06-19 13:29:19 | 00:01:40 |
| [EMAIL PROTECTED] | 5743 | INVITE | BYE | NULL |
2006-06-19 13:27:39 | 2006-06-19 13:29:19 | 00:01:40 |
+----------------------------------+----------+--------+--------+--------+---------------------+---------------------+----------+
What query should I use to make it look like these:
+----------------------------------+----------+--------+--------+--------+---------------------+---------------------+----------+
| callid | username | t1meth | t2meth | t3meth |
start | stop | timediff |
+----------------------------------+----------+--------+--------+--------+---------------------+---------------------+----------+
| [EMAIL PROTECTED] | 5093 | INVITE | BYE | NULL |
2006-06-19 21:20:25 | 2006-06-19 21:22:14 | 00:01:49 |
| [EMAIL PROTECTED] | 5743 | INVITE | BYE | NULL |
2006-06-19 13:27:39 | 2006-06-19 13:29:19 | 00:01:40 |
+----------------------------------+----------+--------+--------+--------+---------------------+---------------------+----------+
Hope someone can help me thank you
________________________________________________
Message sent using UebiMiau 2.7
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
