Mikael Sand - Trying to be different

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.

SELECT
    CONVERT( INT, 
        IsNull(MAX(TransNumber) - MIN(TransNumber) + 1 - COUNT(1), 0)
    )
FROM tbl_Transactions

If the number is zero, then there are no gaps.

Well at least now I can look this up and look smart.