How to Find a Gap in a Sequence using SQL SERVER

In this puzzle, we're going to learn how to find a gap in a sequence using SQL Server. I was recently reviewing my bank statement and noticed that they flagged the start of a missing check number with ***s.

I figured with would be a great example for a SQL puzzle as there are many ways you could solve it.

Solving puzzles is a great way to learn SQL. Nothing beats practicing what you've learned. Once you have figured out the puzzle, post your answer in the comments so we can all learn from one another. We also discuss the puzzle and more in Essential SQL Learning Group on Facebook. Be sure to find us there!

SQL Puzzle Question

You've been asked to analyze some checking account registers and indicate where there is a gap in a Sequence of check numbers.

Here is the table structure:

DECLARE @CheckRegister TABLE
(
   AccountNumber Varchar(10),
   CheckNumber Varchar(10)
)

If the checks are numbered 101, 102, 104, 105 then you'll want to indicate a gap starts at 3. In fact, you'll want to indicate this place appending *** to the check number. Below is an example:

Can you come up with a query that displays the check register and the gaps?

You can use this script to define the table and sample data.

Good luck!

How to Find a Gap in a Sequence Answer

I thought this puzzle was going to be hard to answer. I thought about using joins and comparing the joined values to see if there were gaps and I even thought I might try using a recursive CTE to find gaps, but once I thought about it for a bit, I realized I could use window functions.

There is this really cool function called LEAD. It allows you to look at a value from the next row in a query result.

All I needed to do to find a gap in a sequence was to define a partition and then use LEAD to see whether the next number was more than one greater than the current check number. If so, we have a gap.

Here is the SQL I used to find the current and next CheckNumber.

SELECT AccountNumber,
       CheckNumber,
       Lead(CheckNumber, 1) OVER (PARTITION BY AccountNumber ORDER BY CheckNumber) NextCheckNumber
FROM   @CheckRegister

It's crazy simple, that is once you understand how partitions work. :)

Partitions can be used to segment the data. In our example, we use it to separate the check numbers by account numbers.

To make it easy to separate the logic of obtaining the next value and then comparing it to the current, I used a common table expression.

The CTE is used to get the next value. This result set is then used in the final query alongside a case statement to detect the gap.

If a gap is detected, then *** is appended to the CheckNumber value.

Below is the final query:

WITH CTE_CheckRegister (AccountNumber, CheckNumber, NextCheckNumber)
AS
(
   SELECT AccountNumber,
          CheckNumber,
          Lead(CheckNumber, 1) OVER (PARTITION BY AccountNumber ORDER BY CheckNumber) NextCheckNumber
   FROM @CheckRegister
)
SELECTÂ Â  AccountNumber,
         CASE
            WHEN NextCheckNumber is NULL THEN CheckNumber
            WHEN (CheckNumber = NextCheckNumber - 1) THEN CheckNumber
            ELSE CheckNumber + '***'
         END as CheckNumber
FROMÂ Â Â Â  CTE_CheckRegister
ORDER BY AccountNumber, CheckNumber

The CTE is in bold.

As you can see, it is straight forward. I think it is easy to read.

What type of solution did you come up with? Please post your answer in our comments section.

The post How to Find a Gap in a Sequence using SQL SERVER appeared first on Essential SQL.