Sometimes you come across these little code snippets that just makes you go: “Oh! I wish I was that smart”. I hope this has happened to you as well.
Here is a very smart SQL snippet that solves the following dilemma: How do I detect gaps in a long series of numbers, that are supposed to be sequential?
To put it another way: Let’s say you have a list of transactions. The list is supposed to start at 1000 and you know it ends at 1200. How do you know that 1193 is not missing?
If I would solve that in SQL I would probably use a cursor to iterate and compare the actual transaction number with the supposed value, i.e. not smart.
IsNull(MAX(TransNumber) - MIN(TransNumber) + 1 - COUNT(1), 0)
If the number is zero, then there are no gaps.
Well at least now I can look this up and look smart.