title: T-SQL accessing next/previous row of a table
body: <p><code>UPDATE:</code> I figured this out soon after posting this ... Essentially I used <code>ROW_NUMBER</code> to created sub-partitions of TaskAssignments based on TaskId <code>ORDER BY</code> TaskAssigmentId`. Thanks guys.</p>
  
  <p>Suppose I build a system to keep track of simple task assignments with the following schema:</p>
  
  <pre><code>Tasks:
  TaskId           CurrentlyAssignedTo TaskAddedTime              TaskEndTime
  A                FooBar              2013-05-03 23:28:32.060    2013-05-07 10:12:32.060
  B                Bob                 2013-05-03 20:12:32.060    NULL
  
  TaskAssignments:
  TaskAssignmentId TaskId AssignedTo  StartTime
  1                A      Foo         2013-05-03 23:28:32.060
  2                A      Bar         2013-05-04 20:12:32.060
  3                A      FooBar      2013-05-05 10:12:32.060
  4                B      Alice       2013-05-03 20:12:32.060
  5                B      Bob         2013-05-06 10:12:32.060
  </code></pre>
  
  <p>Table <code>Tasks</code> contains the current information of tasks. A particular task (A or B) could be assigned to multiple workers (one at a time). Every time an assignment happens (including creation time), an entry is added to <code>TaskAssignments</code> table specifying to whom the task was assigned and at what time. When a task is reassigned, <code>CurrentlyAssignedTo</code> field in <code>Tasks</code> is updated with the current worker. When a task is fully completed, <code>TaskEndTime</code> field is updated in <code>Tasks</code> table. <code>TaskAssignmentId</code> is an incremental index.</p>
  
  <p>With that, I want to build a query/view to output something like this:</p>
  
  <pre><code>VwTaskBreakDown:
  TaskId    AssignedTo    StartTime                 EndTime
  A         Foo           2013-05-03 23:28:32.060   2013-05-04 20:12:32.060
  A         Bar           2013-05-04 20:12:32.060   2013-05-05 10:12:32.060
  A         FooBar        2013-05-05 10:12:32.060   2013-05-07 10:12:32.060
  B         Alice         2013-05-03 20:12:32.060   2013-05-06 10:12:32.060
  B         Bob           2013-05-06 10:12:32.060   NULL
  </code></pre>
  
  <p>Where, for a particular task, the <code>EndTime</code> of an assignment is the <code>StartTime</code> of the next assignment. If there is no next assignment, the <code>EndTime</code> comes from <code>TaskEndTime</code> of table <code>Tasks</code>.</p>
  
  <p>Any hint into how this can be done nicely?</p>
  
  <p><code>Disclaimer</code>: I don't have any prior experience with SQL</p>
  
  <p>Thanks,
  Noobie</p>