Posted by Erland Sommarskog on 04/06/06 00:58

js (androidsun@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]