Rather user UPDATE than cursor 1

[LampknLn45]

If I have the following table:

tblTest
Name ITime Seq Minutes StartTime StopTime
A 10/1/2003 8:30 AM 0 15 NULL NULL
A 10/1/2003 8:30 AM 1 20 NULL NULL
A 10/1/2003 8:30 AM 2 25 NULL NULL
A 10/1/2003 8:30 AM 3 30 NULL NULL
B 10/1/2003 9:00 AM 0 25 NULL NULL
B 10/1/2003 9:00 AM 1 10 NULL NULL
B 10/1/2003 9:00 AM 2 15 NULL NULL

I need to loop through the table and populate StartTime and StopTime using the ITime and Minutes field. For example:

Starting with Name A and Seq 0, StartTime = ITime. I add Minutes to StartTime to get StopTime. I then move to the next Seq (under the same name (A)). StartTime at Seq 1 will equal the StopTime of Seq 0. StopTime at Seq 1 will equal StartTime at Seq 1 plus Minutes. When all done, StartTime and StopTime will look like the following:

tblTest
Name Seq StartTime StopTime
A 0 10/1/2003 8:30 AM 10/1/2003 8:45 AM
A 1 10/1/2003 8:45 AM 10/1/2003 9:05 AM
A 2 10/1/2003 9:05 AM 10/1/2003 9:30 AM
A 3 10/1/2003 9:30 AM 10/1/2003 10:00 AM
B 0 10/1/2003 9:00 AM 10/1/2003 9:25 AM
B 1 10/1/2003 9:25 AM 10/1/2003 9:35 AM
B 2 10/1/2003 9:35 AM 10/1/2003 9:50 AM

I obviously left some columns out to save space here but hopefully, you get the idea.

I would like to avoid the use of a cursor and here is what I have so far.

UPDATE tblTest SET StartTime = ITime WHERE Seq = 0
-------------------------------------------------------
UPDATE tblTest SET StopTime = DateAdd(n, Minutes, StartTime) WHERE Seq = 0
-------------------------------------------------------
UPDATE A SET StartTime = (SELECT B.StopTime FROM tblTemp B WHERE
B.Name = A.Name AND B.Seq = (A.Seq - 1) AND A.Seq <> 0),
StopTime = DateAdd(n, A.Minutes, (SELECT B.StopTime FROM tblTemp B WHERE
B.Name = A.Name AND B.Seq = (A.Seq - 1) AND A.Seq <> 0))
FROM tblTemp A
WHERE A.Seq <> 0
-------------------------------------------------------

This isn't quite working. I get the following results:

tblTest
Name Seq StartTime StopTime
A 0 10/1/2003 8:30 AM 10/1/2003 8:45 AM
A 1 10/1/2003 8:45 AM 10/1/2003 9:05 AM
A 2 NULL NULL
A 3 NULL NULL
B 0 10/1/2003 9:00 AM 10/1/2003 9:25 AM
B 1 10/1/2003 9:25 AM 10/1/2003 9:35 AM
B 2 NULL NULL


Have any suggestions? Is what I'm trying to do even possible with just an UPDATE statement?

Thanks,





LampknLn45

 

[swampBoogie]

UPDATE t
  SET StopTime = 
   (select dateadd(n,sum(minutes),ITIME)
      from tblTest
    where name = t.name
      and seq <= t.seq),
      startime = 
   (select dateAdd(n,coalesce(sum(minutes),0),ITIME)
      from tblTest
    where name = t.name
      and seq < t.seq)
 from tblTest as t

The code is untested.

 

[swampBoogie]

The above query will not handle the case when seq = 0 properly

UPDATE t
  SET StopTime =
   (select dateadd(n,sum(minutes),ITIME)
      from tblTest
    where name = t.name
      and seq <= t.seq),
      startime = case when seq = 0 then ITIME else
   (select dateAdd(n,sum(minutes),ITIME)
      from tblTest
    where name = t.name
      and seq < t.seq) end
 from tblTest as t

still untested

 

[LampknLn45]

swampBoogie,
Thanks for the example. It worked great once I put the GROUP BY clause in there.

UPDATE t
SET StopTime =
(select dateadd(n,sum(minutes),ITIME)
from tblTest
where name = t.name
and seq <= t.seq GROUP BY ITIME),
startime = case when seq = 0 then ITIME else
(select dateAdd(n,sum(minutes),ITIME)
from tblTest
where name = t.name
and seq < t.seq GROUP BY ITIME) end
from tblTest as t







LampknLn45