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
*/
