tsql - T-SQL accessing next/previous row of a table -

update: figured out after posting ... used row_number created sub-partitions of taskassignments based on taskid order by taskassigmentid`. guys.

suppose build system keep track of simple task assignments following schema:

tasks: taskid           currentlyassignedto taskaddedtime              taskendtime                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                     foo         2013-05-03 23:28:32.060 2                     bar         2013-05-04 20:12:32.060 3                     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 

table tasks contains current information of tasks. particular task (a or b) assigned multiple workers (one @ time). every time assignment happens (including creation time), entry added taskassignments table specifying whom task assigned , @ time. when task reassigned, currentlyassignedto field in tasks updated current worker. when task completed, taskendtime field updated in tasks table. taskassignmentid incremental index.

with that, want build query/view output this:

vwtaskbreakdown: taskid    assignedto    starttime                 endtime         foo           2013-05-03 23:28:32.060   2013-05-04 20:12:32.060         bar           2013-05-04 20:12:32.060   2013-05-05 10:12:32.060         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 

where, particular task, endtime of assignment starttime of next assignment. if there no next assignment, endtime comes taskendtime of table tasks.

any hint how can done nicely?

disclaimer: don't have prior experience sql

thanks, noobie

you can try this...

select taskid,assignedto,starttime,coalesce(   (select min(starttime) tablename a.taskid=t.taskid , a.starttime>t.starttime),   (select taskendtime t1 b b.taskid=t.taskid , b.currentlyassignedto=t.assignedto) )as endtime tablename t 

sql fiddle demo