chrismat (9/10/2012)
I've always heard anything can be done in set-based operation without using a cursor, but I can't think of how for this scenario.A SELECT query returns four columns from tables, and the fifth column (NewSibling) needs calculated. Here is an example of the output desired:
OldSponsor, OldSibling, NewSponsor, NewSibling
177632,0,176896,5
177632,1,176896,6
177632,2,176896,7
171988,0,176896,8
171988,1,176896,9
179067,0,176897,55
179067,1,176897,56
179067,2,176897,57
179068,0,176897,58
179068,1,176897,59
The NewSponsor currently has some MAX(Sibling); 176896 is currently 4, 176897 is currently 54.
Rows in same NewSponsor group must start current MAX(Sibling)+1 and increment to account for the others being selected.
Thanks for any advice.
Based on the data above, what are the expected results.