I would like to change a cursor to a sql loop to get the following result:

Assn_Num Time EventCodeRevNo
12345-1 5:00 pm 1
12345-1 5:25 pm 2
12345-1 6:30 pm 3

The temp table will have the values of eventtime, assnnum and I want to assign a sequential EventCodeRevNo in ascending time order. Some events may have up to 10 or more. This will reset when the Assn_Num changes.

This is a piece of the cursor currently being used:

declare temp_cur_in_pb cursor for
select EventTime,Assn_Num from #temp_res where #temp_res.EventCode="PB"
and #temp_res.Assn_Num = @Assn_Num order by EventTime asc

declare @count int
select @count =1
open temp_cur
fetch temp_cur into @Assn_Num
while (@@sqlstatus = 0 )
begin
select @count =1
open temp_cur_in_pb
fetch temp_cur_in_pb into @EventTime, @Assn_Num
while (@@sqlstatus = 0 )
begin
update #all_exams
set EventCodeRevNo = @count
where EventTime = @EventTime
and Assn_Num = @Assn_Num
select @count = @Count + 1
fetch temp_cur_in_pb into @EventTime,@Assn_Num
end
close temp_cur_in_pb
select @count =1