How best to track a task start time and end time in a single table row?

Question

A person opens pages on a website to perform quality control tasks. On one page, the activity of quality control starts when the person hits a start button. On another page, the activity starts when the page loads.

The activity of quality control can end once the person:

1. completes the task,
2. saves as incomplete, or
3. just closes the browser window.

The EndTime can occur anywhere from 10 seconds to 30 minutes after the start of the quality control task.

The Issue: if you have a table with an ActivityID, a StartTime, and EndTime columns, what would be the best way of going about saving this information to the table, when the act of updating the existing record takes place outside of the stored proc that inserted the record? The StartTime has to be captured, which is straightforward enough, but then, however long after the task is completed, the End Time for the unique Activity has to be captured, and Activity EndTime has to be changed from NULL to the time of the "completion".

Some scenarios to consider:

• person A begins an Activity, then completes it. 10 seconds later, person B starts and finishes.

• person A begins an Activity, and then person B starts 10 seconds later, and person A finishes 5 seconds ahead of person B. (I don believe any kind of scope function would work here... http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ ?)

Would this route be acceptable...? Write a proc to call the Insert when the page loads. As part of the proc, insert the new activity and start time, and also return the value of the ActivityID (one method described here: Get the ID of last inserted records). When the webpage hits an "end" event, then pass the ActivityID returned from the first proc, and call a proc that adds the End Time for the AcitivityID that came from the first proc.

Answer 1

  insert into [Table_2] ([valueStart]) values('start');
  select SCOPE_IDENTITY();

  int sqlIden = SQLcommand.execute();

  // perform task

  update [Table_2] set [valueFinish] = 'finish' where [id] = sqlIden.ToString();