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 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 you answer in the comments so we all can learn from one another.

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 display 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 though this puzzle was going to be hard to answers.  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 realize I could use window functions.

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

All I needed to do to find a gap in a sequence was 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

Its 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 green.

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

To learn more about Common Table Expressions (CTEs), check out our Ultimate Guide to Learning CTEs.

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