help with query from a sql newbie

hope someone can help

i have a table a temp table that gets created on a daily basis andcan have between 10 -100 rows in it which looks like this

idstarttimeduration

110:00:00600

210:10:00300

311:33:0015

etc

duration is in seconds

what i want to be able to do is add the start time from a row to the duration from the same row and sutract it from the next rows startime.

Andy

is the value of column id in running ? What is the version of SQL Server are you using ?|||

apwhelan wrote:

what i want to be able to do is add the start time from a row to the duration from the same row and sutract it from the next rows startime.

That is a bit confusing.

What is 'next'?

The row chronologically following?

What are you attempting to 'discover'?

It seems like you may be after the amount of time between events -but that isn't too clear. So in your sample data, there is no 'missing' time between rows 1 and 2, but quite a bit of 'missing' time between rows 2 and 3.

Is that what you wish to display?

|||

I agree this is a little confusing but will the following get you somewhere close to where you want to be?

select t1.id as endoftask, t1.id2 as startoftask, datediff(s,endtime, t2.starttime) as idletime

from (SELECT *, dateadd(s, duration, starttime) as endtime, id+1 as id2

FROM table1) AS t1

inner join table1 t2

on t1.id2 = t2.id

This assumes that your id column is sequential with no missing numbers. If this doesn't work, i guess you could go down the cursor route.

Hope this helps!

|||

Another potential alternative might be something like:

Code Snippet

declare @.temp table
( id integer,
startTime datetime,
duration integer
)

insert into @.temp
select 1, '10:00:00', 600 union all
select 2, '10:10:00', 300 union all
select 3, '11:33:00', 15
--select * from @.temp

;with tempSeq as
( select id,
startTime,
duration,
dateadd(ss, duration, startTime) as endTime,
row_number() over
(order by startTime, id) as Seq
from @.temp
), tempSeq2 as
( select id,
startTime,
duration,
dateadd(ss, duration, startTime) as endTime,
1 + row_number() over
(order by startTime, id) as Seq2
from @.temp
)
select a.id,
a.Seq,
convert(varchar(10), a.startTime, 108) as a_startTime,
a.duration,
convert(varchar(10), a.endTime, 108) as a_endTime,
convert(varchar(10),
case when Seq2 is null then 0
else a.endTime - b.StartTime
end, 108)
as timeDifference
from tempSeq a
left join tempSeq2 b
on a.Seq = b.Seq2

/*
id Seq a_startTime duration a_endTime timeDifference
-- -- -- - --
1 1 10:00:00 600 10:10:00 00:00:00
2 2 10:10:00 300 10:15:00 00:15:00
3 3 11:33:00 15 11:33:15 01:23:15
*/

|||All of these work, but the bigger question is why you need to store that data. The best thing to do is to store the start and end of a thing, and then you can use those to calculate any kind of interval you need, including averages, sums and the like. Remember, avoid storing what you can calculate, unless it becomes a performance issue to do so.