Check for missing values

[sleezy]

l have a hierachyKey that ranges from 1- 1000
If l want to check that the number runs in sequence and does not have missing numbers in between .. how do l do that

[cvandemaele]

Forgive my ignorance, but what exactly do you mean by a HierachyKey ?

CVM.

[jkoopmann]

you can easily check to see if you have any missing numbers by summing of all your keys. the total should match the mathamatical formula below:
1. get your highest key
2. if your key is an even number, multiply 1/2 of your highest key number by the next number.
-or-
if you key is odd, multiply your odd key number by 1/2 of the next number

Example:
1. If your highest key is 1000
2. The next number is 1001
3. 1001 X (1000/2) = 1001 X 500 = 500500
4. So your sum of all keys should equal 500500. to get the sum of all your keys, just issue a sql command such as select sum(hierachykey) from yourtable;

NOW, if the sum of your keys don't match, then you will have to sequentially look through your table with an application to find where the mising keys are.

if you wanted to get fancy, you could put some of this into a fancy sql statement that would allow you to do the calculation on key ranges. if you need help on this, i would suggest you let us know what database platform you are on as that would determine the method of the sql statement.

[cvandemaele]

Mathematical formula ?

If you just compare the number of occurrences with the maximum value of your "key", then I guess you know whether you have misses.

Assuming your key is an integer...

Anyway, JKoopmann is right :

1 + 2 + 3 + ... + N = N x (N + 1) / 2

CVM.

[sleezy]

The database platform l'm using is Sql Server 2000