When I was in college, and even high school, all of my computer science classes required me to build algorithms. Often they were simple things, like implement a sort, or reverse a string, or shuffle a deck of cards. Those seemingly silly and trivial exercises, however, build the skills of pattern recognition and implementation in computer science. Sometimes I think we don’t do enough of that for people that are tackling computer careers these days.
I saw a post from someone that had an incrementing column, an identity, that impacted another field. Basically whenever the first column reached “10”, you wanted to add one to the second column.
Easy, right? I think so, and to show someone how they might create an update statement, or even see the pattern, I built a quick tally table.
SELECT Top 205 IDENTITY(INT,1,1) as N INTO Tally FROM master.dbo.syscolumns SC1, master.dbo.syscolumns SC2
From there, I then looked at the pattern. Every 10 items, I need to add one. That’s a pattern, and the way that pattern is easily discerned in math is with a modulo operation. To the rest of the world, that’s a remainder. If you look at the pattern of remainders of an increment divided by 10, it’s this:
n modulo
———– ———–
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 0
11 1
12 2
13 3
14 4
15 5
16 6
17 7
18 8
19 9
20 0
21 1
22 2
from this code:
SELECT Top 205 IDENTITY(INT,1,1) as N INTO Tally FROM master.dbo.syscolumns SC1, master.dbo.syscolumns SC2 SELECT n , n % 10 FROM Tally DROP TABLE tally
That mans that we can see each time there is a zero remainder, we want to perform an increment. So essentially if you detect an update, do a modulo, and get a zero, then you update the next column.
It gets a little more complicated if there can be multiple rows updated or added at once, but here is the overall code that essentially builds a table of numbers that increment for each 10 on the previous value.
SELECT Top 205 IDENTITY(INT,1,1) as N INTO Tally FROM master.dbo.syscolumns SC1, master.dbo.syscolumns SC2 DECLARE @b INT SELECT @b = 1 SELECT n , n % 10 , @b , n , @b + (1 * (n / 10)) 'col b' , CASE WHEN (n % 10) = 0 THEN 'add 1' ELSE '' END FROM Tally DROP TABLE tally
You end up with this:
n n col b
———– ———– ———– ———– ———– —–
1 1 1 1 1
2 2 1 2 1
3 3 1 3 1
4 4 1 4 1
5 5 1 5 1
6 6 1 6 1
7 7 1 7 1
8 8 1 8 1
9 9 1 9 1
10 0 1 10 2 add 1
11 1 1 11 2
12 2 1 12 2
13 3 1 13 2
14 4 1 14 2
15 5 1 15 2
16 6 1 16 2
17 7 1 17 2
18 8 1 18 2
19 9 1 19 2
20 0 1 20 3 add 1
21 1 1 21 3
22 2 1 22 3
23 3 1 23 3
If I had started at zero, you’d see a more traditional increment of 0 for column b to start with.
Filed under: Blog Tagged: syndicated, T-SQL 
