I have some raw data like this:
| Team | Ranking |
|---|---|
| 1 | 1 |
| 2 | 8 |
| 3 | 2 |
| 4 | 6 |
| 5 | 3 |
| 6 | 5 |
| 7 | 4 |
| 8 | 7 |
Once a month, I copy it in to my database, adding a "Last Updated" column to indicate which monthly update run that row was last updated on. However, due to the way that ranks work, if one rank changes, then some other(s) will change. My issue is that I want to only update the "Last Updated" column when the rank of the row in question has been specifically changed. For example, if Team 5 moves up two ranks, then I only want to mark Team 5's row as updated. I do not want to change anything in the "Last Updated" column of the rows of the two teams that they have just overtaken. How can I implement this?
A key factor is that intentional changes can only happen one at a time, but since I'm only checking monthly, I'm almost always going to see multiple changes. The difficulty is in detecting which team's ranks have been intentionally moved rather than merely moved as a consequence of the other data.
For example, consider if the following two changes happened in the raw data between my monthly checks (intentional changes in bold):
| Team | Original Ranking | Ranking After First Change | Ranking After Second Change |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 8 | 8 | 8 |
| 3 | 2 | 5 | 6 |
| 4 | 6 | 6 | 7 |
| 5 | 3 | 2 | 2 |
| 6 | 5 | 4 | 4 |
| 7 | 4 | 3 | 3 |
| 8 | 7 | 7 | 5 |
If that happens, then I want every changed row in my processed data to have its rank updated, but I only want the "Last Updated" column updated for Team 3 and Team 8.
For example, if it's now month 36, I'd want my processed data to go from
| Team | Ranking | Last Updated |
|---|---|---|
| 1 | 1 | 31 |
| 2 | 8 | 31 |
| 3 | 2 | 32 |
| 4 | 6 | 33 |
| 5 | 3 | 34 |
| 6 | 5 | 35 |
| 7 | 4 | 31 |
| 8 | 7 | 31 |
to
| Team | Ranking | Last Updated |
|---|---|---|
| 1 | 1 | 31 |
| 2 | 8 | 31 |
| 3 | 6 | 36 |
| 4 | 7 | 33 |
| 5 | 2 | 34 |
| 6 | 4 | 35 |
| 7 | 3 | 31 |
| 8 | 5 | 36 |
Is this possible to implement in T-SQL? At this point, I don't care even if I have to resort to using cursors.
CodePudding user response：
Determining multiple steps is impossible on algorithmic level, regardless of language.
Example:
Initial team ranking: A,B,C
Final team ranking: B,C,A
You cannot know if this was a one step A moving from 1st rank to 3rd, or a two step of A going from 1st to 2nd and then from 2nd to 3rd.