## Building an algorithm

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