* Karma Dorji
> i have a table, like the one below,
> i need to find the time difference between the Start and Stop from a
> particular CallingStationId to particular CalledStationId.
>
> +------------+----------+--------------------+-------------------+
> ----------
> --------+-------------------+
> | Date | Time | CallingStationId | CalledStationId |
> AcctStatusType | AcctSessionTime |
> +------------+----------+--------------------+-------------------+
> ----------
> --------+-------------------+
> | 09/01/2004 | 17:28:27 | 02327125 | 00018151635 | Start
> | |
> | 09/01/2004 | 19:00:34 | 02320176 | 01181471822125 | Start
> | |
> | 09/01/2004 | 19:10:08 | 17113080 | 01022586815 | Start
> | |
> 09/01/2004 | 20:28:27 | 02327125 | 00018151635 | Sop
> | |
> | 09/01/2004 | 21:00:34 | 02320176 | 01181471822125 | Stop
> | |
> | 09/01/2004 | 22:10:08 | 17113080 | 01022586815 | Stop
> | |
First you need to pair the rows:
SELECT t1.Time,t2.Time
FROM tab t1,tab t2
WHERE
t1.CallingStationId = t2.CallingStationId AND
t1.CalledStationId = t2.CalledStationId AND
t1.AcctStatusType = 'Start' and
t2.AcctStatusType = 'Stop'
This should work with your example data, if you have duplicate combinations
of CallingStationId/CalledStationId/AcctStatusType you will also need to
check the date and time.
To calculate the time difference you could transform the time string to
seconds, and then just subtract. This will give you the answer in seconds,
but it will only work if your dates are valid mysql dates, in the format
YYYY-MM-DD:
SELECT unix_timestamp(concat(t2.Date,' ',t2.Time)) -
unix_timestamp(concat(t1.Date,' ',t1.Time))
FROM tab t1,tab t2
WHERE
t1.CallingStationId = t2.CallingStationId AND
t1.CalledStationId = t2.CalledStationId AND
t1.AcctStatusType = 'Start' and
t2.AcctStatusType = 'Stop'
If the date is irrelevant (allways same day) you can use any date:
SELECT unix_timestamp(concat('2004-09-01 ',t2.Time)) -
unix_timestamp(concat('2004-09-01 ',t1.Time))
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
