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.