Calculate time lapse

[js]

Can anyone help with the following Transact SQL question? Thanks. I
need a store procedure to return the the result recordset which will be
execute from a web page. The database has tables, A and B. For each A
record, there are many related B records. In the B table there is a
timestamp field which tracks the change of A record. For example, A1
has B like the followings:

ID TimeStamp Chg Code Descption
== ========= ======= ========
A1 1138375875 E null //end of the event
A1 1138025002 S resume
A1 1137092615 S don't care
A1 1137092570 S stop
A1 1137092256 I null //start of the
event

I need to generate all records in table A and total elapse time for
each record, but B with Chg Code 'S' that has "don't cacre" to be
deducted from the total time, so that the result will be like this:

ID Name TotalTime
(seconds)
== ==== =======
A1 xyz 351187

[das]

try this, I haven't tested it:

select A.ID, A.Name, Sum(B.TimeStamp)
from A inner join B on A.ID = B.ID
group by A.ID, A.Name
having (B.ChgCode <> 'S' and B.Description <> 'don''t care')

adi

[Erland Sommarskog]

js (an********@yahoo.com) writes:

Can anyone help with the following Transact SQL question? Thanks. I
need a store procedure to return the the result recordset which will be
execute from a web page. The database has tables, A and B. For each A
record, there are many related B records. In the B table there is a
timestamp field which tracks the change of A record. For example, A1
has B like the followings:

ID TimeStamp Chg Code Descption
== ========= ======= ========
A1 1138375875 E null //end of the event
A1 1138025002 S resume
A1 1137092615 S don't care
A1 1137092570 S stop
A1 1137092256 I null //start of the
event

I need to generate all records in table A and total elapse time for
each record, but B with Chg Code 'S' that has "don't cacre" to be
deducted from the total time, so that the result will be like this:

ID Name TotalTime
(seconds)
== ==== =======
A1 xyz 351187

It is not clear to how that "don't care" row is to be deducted, since
that is just a point in time. Had you posted CREATE TABLE statements
for the tables, and INSERT statements with the data, it would have been
easy to play around. The below is just a guess, and is untested:

SELECT B.ID, SUM(elapsed)
FROM (SELECT B1.ID, elapsed = B1.TimeStamp - B2.Timestamp,
B2.ChgCode, B2.Description
FROM B B1
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)
) AS B
WHERE NOT (B.ChgCode = 'S' AND B2.ChgCode = 'don''t care')
GROUP BY B.ID

Here, I'm making the assumption that it is the time from the don't-
care event until the next event that is to be ignored.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[js]

Thanks for the reply. The TimeStamp column is the actual time the
event occurs not the length of each event, so summing the column will
not give me the actual length of the time from the start to end. The
calculation should be
(time event starts - time envent ends) - (time resume starts - time
resume stops)
thus the result is
(1138375875 - 1137092256) - (1138025002 - 1137092570) = 351187

Any idea? I think I have to use cursor to loop through each result
block and determine if the Description field contains 'stop' or
'resume'. Thanks.

[js]

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

[Erland Sommarskog]

js (an********@yahoo.com) writes:

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?

You must correlate the computation of elapsed with a row in A. The
easiest way is to use the proprietary FROM/JOIN syntax supported by
MS SQL Server:

Update A
set TimeLapse = Btot.elapsed
FROM A
JOIN (SELECT B.ID, elapsed = SUM(elapsed)
FROM (SELECT B1.ID, B1.[TimeStamp] - B2.[TimeStamp] AS elapsed,
B2.ChgCode, B2.description
FROM B B1
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 B.ID) AS Btot = A.ID = B.ID

However, neither this is entierly satisfactory, as you are reading the
entire B table a couple of times on each insert, and this could be
expensive. SQL Server offers the the virtual tables "inserted" and
"deleted" which holds after-image and before-images of the rows
affected by the statement. (For an INSERT, there are only rows in
"inserted" obviously.)

Rewriting the trigger to look at inserted is not trivial, least of all
if rows can be inserted out of order. (What if a "don't care" row is
inserted in the middle of it all?)

Not knowing the exact scenario where this appears I prefer to not suggest
a solution.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx