Just some suggestions / remarks.

1. You can get the time someone spent in a certain state just from inserting the 'starttime' for each state by using a query like the following.

SELECT s1.Agent, s1.State, s1.Start,

       min(s2.Start) As End

FROM State s1

  INNER JOIN State s2

  ON s1.Agent = s2.Agent

  AND s1.Start < s2.Start

(Did not test this, nor did I look at performance).

2. Seems odd that you get deadlocks when you use a trigger. Are you sure there is no recordset left open at the time of the insert that should have been closed?

Be careful with the trigger solution. If you ever do batch inserts of more than one record, the trigger will only be fired once. So you have to make sure you cover this situation in the trigger.

If you don't mind the slower performance / higher load of the first solution, there is no need to go back and update the timeendstamp.

3. Advantage of a stored procedure over a trigger? No real advantage, except for the general advantages a stored procedure has over immediate table access : security (and performance due to cached execution plan).

4. Your 'state' tables look a lot like building a cube. Did you consider using the OLAP possibilities of SQL Server (or any other tool like Business Objects or Cognos) do get your stuff done?

Edited by - NPeeters on 05/22/2003 06:19:56 AM