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

[Sanjeev]

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

[--CELKO--]

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.

[Erland Sommarskog]

Roy Harvey (SQL Server MVP) (ro********@snet.net) writes:

What version of Microsoft SQL Server are you running?

Actually, he said SQL 2000. :-)

--
Erland Sommarskog, SQL Server MVP, es****@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

[Roy Harvey (SQL Server MVP)]

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

>Actually, he said SQL 2000. :-)

Oops!

Thanks, Erland.

Roy Harvey
Beacon Falls, CT

[Roy Harvey (SQL Server MVP)]

--- Sanjeev <sa**************@gmail.comwrote:

Table def :
Create Table Callbycall(
Agent int,
Start_Time datetime,
End_Time datetime
)

and required output as follows

Agent Start_Time End_Time
Idle_Time(derived column)
================================================== =========
194 2008-05-30 10:19:00 2008-05-30 10:23:06.000 15:54
194 2008-05-30 10:39:00 2008-05-30 10:41:59.000 02:01
194 2008-05-30 10:44:00 2008-05-30 11:11:45.000 05:15
194 2008-05-30 11:17:00 2008-05-30 11:22:19.000 23:41
194 2008-05-30 11:46:00 2008-05-30 12:55:56.000 00:00
200 2008-05-30 13:02:00 2008-05-30 13:56:34.000 39:26
200 2008-05-30 14:36:00 2008-05-30 14:36:00.000 64:00
200 2008-05-30 15:40:00 2008-05-30 15:58:20.000 .....
200 2008-05-30 13:02:00 2008-05-30 13:56:34.000 00:00
.................
.................
.................
380 2008-05-30 13:02:00 2008-05-30 13:56:34.000 39:26
380 2008-05-30 14:36:00 2008-05-30 14:36:00.000 64:00
380 2008-05-30 15:40:00 2008-05-30 15:58:20.000 .....
380 2008-05-30 13:02:00 2008-05-30 13:56:34.000 00:00

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