Help with using Fast Forward Cursor

Transact-SQL
1

Hello everyone,

I am new to the concept of cursors in SQL, so the question I have might be very basic. I have a data set with columns A (datetime: start time), B (datetime: end time), C (int: ID) and D (int: UniqueID). Rows are ordered based on C and then A.

For the first row, value for D equals 1. The purpose is to use the same value for D in the next row, only if C (row X+1) = C (row X) and A (row X+1) < B+35 minutes (row X). Else, the value for D should be increased with 1.

I put the value of C(X+1) in a new column E to the previous row X, as well as the value of A(X+1) in a new column F(X) (using lead), to be able to compare everything on the same row. Then I used a cursor to loop through all rows.

  declare @A, @B, @C, @D, @E, @F
 begin
 declare cTrip cursor fast_forward for
    select A, B, C, D, E, F from Tablename order by C asc, A asc
 
  open cTrip
  fetch next from cTrip into @A, @B, @C, @D, @E, @F;
  while @@FETCH_STATUS = 0
  begin
    if @C = @E and DATEDIFF(SECOND, @B, @F) <= (35 * 60)
    begin
      set @D = @D
    end
    else
    begin
      set @D = @D + 1
    end
 
    update Tablename
       set D = @D
 
    fetch next from cTrip into @A, @B, @C, @D, @E, @F;
  end 
  close cTrip
  deallocate cTrip
end

However, this seems not to work: the value of D remains the same for all rows, even if some rows clearly do not satisfy the if-condition. I would be very happy with your help what's wrong in this coding.

Thanks!

2

I haven't looked at it very closely, but can you not do this in a single UPDATE statement? SET -BASED is way WAY better in SQL than row-by-row (not uncommon to be 100x faster when using set-based)

1 Like