How to find out difference between two rows....

Dear Gurus,

I have below table with data

No.| Agent Start_Time End_Time
==========================================
1 | 194 2008-05-30 10:19:00 2008-05-30 10:23:06.000
2 | 194 2008-05-30 10:39:00 2008-05-30 10:41:59.000
3 | 194 2008-05-30 10:44:00 2008-05-30 11:11:45.000
4 | 194 2008-05-30 11:17:00 2008-05-30 11:22:19.000
5 | 194 2008-05-30 11:46:00 2008-05-30 12:55:56.000
6 | 194 2008-05-30 13:02:00 2008-05-30 13:56:34.000
7 | 194 2008-05-30 14:36:00 2008-05-30 14:36:08.000
8 | 194 2008-05-30 17:41:00 2008-05-30 18:18:26.000

I want to find Idle time duration as follows

for record no. 1 =>
Idle time = (start_time of record no. 2) - (End_time of record no. 1)

for record no. 2 =>
Idle time = (start_time of record no. 3) - (End_time of record no. 2)

and so on.

Means output as follows
No.| Agent Start_Time End_Time
Idle_Time (in min or sec)
================================================== ===========
1 | 194 2008-05-30 10:19:00 2008-05-30 10:23:06.000 15:54 min
2 | 194 2008-05-30 10:39:00 2008-05-30 10:41:59.000 02:01 min
3 | 194 2008-05-30 10:44:00 2008-05-30 11:11:45.000 05:15 min
4 | 194 2008-05-30 11:17:00 2008-05-30 11:22:19.000 .......
5 | 194 2008-05-30 11:46:00 2008-05-30 12:55:56.000 .......
6 | 194 2008-05-30 13:02:00 2008-05-30 13:56:34.000 .......
7 | 194 2008-05-30 14:36:00 2008-05-30 14:36:08.000 ......
8 | 194 2008-05-30 17:41:00 2008-05-30 18:18:26.000 00:00 min


So Is there any formula/inbuilt function available in SQL Server 2000

Could any one help me for above problem ?

Thanking in advance
Sanjeev

re: How to find out difference between two rows....

1) Please learn why rows are not records.
2) Please post real DDL and not narrative.
3) Add an activity_code column which includes 'idle' as one of its
values. This will give you what you want now and can expand when you
want more details.

re: How to find out difference between two rows....

Roy Harvey (SQL Server MVP) (roy_harvey@snet.net) writes:Actually, he said SQL 2000. :-)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: How to find out difference between two rows....

On Thu, 14 Aug 2008 21:49:14 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.sewrote:
Oops!

Thanks, Erland.

Roy Harvey
Beacon Falls, CT

re: How to find out difference between two rows....

--- Sanjeev <sanjeev.atvankar@gmail.comwrote:
The first issue is calculating the seconds.

SELECT *,
DATEDIFF(second, A.End_Time,
(SELECT MIN(Start_Time)
FROM Callbycall as B
WHERE A.Agent = B.Agent
AND A.Start_Time < B.End_Time)) as ElapsedSecond
FROM Callbycall as A

One way to format the time expression, which you should be able to
adapt to your needs:

declare @i int
set @i = 12345
SELECT convert(char(8), dateadd(second, @i, 0), 114)

--------
03:25:45

Roy Harvey
Beacon Falls, CT