-
Change cursor to While loop Statement
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|