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

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.

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

What version of Microsoft SQL Server are you running?

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

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

>Actually, he said SQL 2000. :-)

Oops!

Thanks, Erland.

Roy Harvey
Beacon Falls, CT

--- Sanjeev <sanjeev.atvankar@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