Posted by js on 04/06/06 19:35

Thank you. It works with minor modification. Now I would like to use
a trigger so that upon insert the elapsed time will be posted in Table
A column (int) "TimeLapse". However, it would not accept the value.
Can you help?

ALTER TRIGGER [updateA]
ON [dbo].[B]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

Update A
set A.TimeLapse = (SELECT SUM(elapsed)
FROM (SELECT B1.ID, B1.[TimeStamp] -
B2.[TimeStamp] AS elapsed, B2.ChgCode, B2.description
FROM B B1 INNER JOIN
B B2 ON B1.ID = B2.ID AND
B2.[TimeStamp] =
(SELECT
MAX(B3.Timestamp)
FROM B B3
WHERE B3.ID = B1.ID
AND B3.TimeStamp < B1.TimeStamp)
WHERE not (B1.ChgCode = 'S' and
(b1.description
like '%resume%' or b1.description like '%don''t care%')) OR
(b1.description IS
NULL)) B GROUP BY ID)
END

[Back to original message]