Author: way0utwest

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.