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

What version of Microsoft SQL Server are you running?

Do the time ranges ever overlap? Does the calculation have to be by
Agent, or is it across all Agent values? Is there a constraint in the
system to insure that when the "No." column is in order the times are
also in order? When you say 15:54 min is the 15 hours and 54 minutes,
or 15 minutes and 54 seconds?

One simple approach that might not be right, or best, depending on
information not given.

SELECT *,
DATEDIFF(second, A.End_Time,
(SELECT MIN(Start_Time)
FROM Whatever as B
WHERE A.Start_Time < B.End_Time)) as ElapsedSecond
FROM Whatever as A

Roy Harvey
Beacon Falls, CT

On Thu, 14 Aug 2008 00:19:54 -0700 (PDT), Sanjeev
<sanjeev.atvankar@gmail.com> wrote:

>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.se> wrote:

>Actually, he said SQL 2000. :-)

Oops!

Thanks, Erland.

Roy Harvey
Beacon Falls, CT

--- Sanjeev <sanjeev.atvankar@gmail.com> wrote:

> 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