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.

Help with Performance on a Conditional Trigger

I have a pretty massive conditional trigger. If there is another way of going about this, please let me know. But I'm populating a temp table with records and based on many conditions, I am transforming this data to another table in a corrected format. These conditions I am using reference the final table in many ways, and this seems to become slower and slower as the final table grows larger.

Take a look and see if you can help me please.There are some major issues with your trigger. The length and repetetiveness is just symptomatic.

When you evaluate statements like:
IF (SELECT RESV_TOTAL FROM inserted) = 0
...you need to keep in mind that the trigger executes once for every insert statement, not once for every inserted record. If you load 100 records into this table the trigger is still going to fire only once, and thus SELECT RESV_TOTAL FROM inserted will return more than one record and can't be compared to the scalar value "0".

And regarding:
IF (SELECT COUNT(*) FROM RESERVATION_MASTER RM, inserted
WHERE RM.BOOK_NO = inserted.BOOK_NO) >= @.@.ROWCOUNT
...I can't even be sure which last successful transaction @.@.ROWCOUNT refers to, and I'm not convinced you can either.

I suggest you back off, think about your application process a bit more, and try to write up what you are trying to accomplish as succinctly as possible. Having a clear picture of Point A and Point B often helps in devising the best route from Point A to Point B.|||it certainly does appear that you're not quite consistent in the method of identifying your conditions. but i'd say that can be fixed once you follow blindman's advice. what cannot be fixed is the problem that you addressed us with in the first place, - "it seems to become slower and slower as the final table grows larger."

if you can revamp your insert process by referencing that final table through a lefty outer join (left that is.) i'd even go further, i'd create a view using that join, and bcp records out. then, i'd drop the trigger all together and just use bulk insert. after all, at that point i only have records that i need.