Dear Dobromir Velev and members of list, Thank you so much for the help, yes the sql query works perfectly, though i did add some, as to cancel the duplicates ones, and also to not to take into negative timings, please advice on this too,
select SEC_TO_TIME(unix_timestamp(concat(e.Date,' ',e.Time)) - unix_timestamp(concat(s.Date,' ',s.Time))) as time, s.CallingStationId, s.CalledStationId, s.Date, s.Time, s.AcctStatusType, e.Date, e.Time, e.AcctStatusType from VOIP s left join VOIP e on ( s.CallingStationId=e.CallingStationId and s.CalledStationId=e.CalledStationId and e.AcctStatusType='Stop' ) where s.AcctStatusType='Start' and s.Time < e.Time and s.Date <= e.Date group by s.Time,e.CallingStationId order by s.date,s.time; Thanks, Karma ----- Original Message ----- From: "Dobromir Velev" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, September 03, 2004 6:48 PM Subject: Re: HELP ME WITH THIS > Hi, > The only way I can think of is to join the table to itself. It should look > something like this: > > select unix_timestamp(concat(s.date,' ',s.time)) - > unix_timestamp(concat(e.date,' ',e.time)) > from table s left join table e on > ( s.CallingStationId=e.CallingStationId and > s.CalledStationId=e.CalledStationId and e.AcctStatusType='Stop' ) > where s.AcctStatusType='Start' > order by s.date,s.time; > > I haven't tested it so you will probably need to play a bit with the > date/time formating but it should be enough to give you the idea. It will only > work if there is only one session for each CallingStationId and > CalledStationId pair, otherwise you will receive a lot of irrelevant results. > > > -- > > Dobromir Velev > > On Friday 03 September 2004 15:21, Peter J Milanese wrote: > > If it were all in one row, you may be able to compare datetime fields. > > > > I do not know if you can do this with 2 rows, and the query will probably > > be rough. > > > > Did you design the table? Can you create it so that your row has start and > > stop times, instead of creating another row? > > > > > -----Original Message----- > > > From: Karma Dorji [mailto:[EMAIL PROTECTED] > > > Sent: Friday, September 03, 2004 5:06 AM > > > To: [EMAIL PROTECTED] > > > Subject: HELP ME WITH THIS > > > > > > Hello can anyone help me with this, > > > > > > 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 > > > > > > Thanking you all in advance. > > > > > > Karma > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
