SQL Puzzle | The Missing Gaps Puzzle [Multiple Solutions – Best One]

This puzzle is really simple. Here we are in need to find out the row number’s gap in a table to analyze the deleted/update/missing records range after an incident. There are different ways to find out the rows gap !

Please check out the sample input and the expected output below-

Sample Input

Value
2
3
10
11
13
14
19
22
24
25
29

Expected Output

GapStart GapEnd
4 9
12 12
15 18
20 21
23 23
26 28

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.

Script | use below script to create table & insert some sample data

 ``` -- CREATE TABLE SeqVal (VALUE INT not null CONSTRAINT PK_Val PRIMARY KEY); go INSERT INTO SeqVal VALUES(2); INSERT INTO SeqVal VALUES(3); INSERT INTO SeqVal VALUES(10); INSERT INTO SeqVal VALUES(11); INSERT INTO SeqVal VALUES(13); INSERT INTO SeqVal VALUES(14); INSERT INTO SeqVal VALUES(19); INSERT INTO SeqVal VALUES(22); INSERT INTO SeqVal VALUES(24); INSERT INTO SeqVal VALUES(25); INSERT INTO SeqVal VALUES(29); GO -- ```

Solution 1

 ``` -- SELECT v1 GapStart, v2 GapEnd FROM ( SELECT Value+1 v1 ,LEAD(Value) OVER(ORDER BY Value)-1 v2 FROM SeqVal )t WHERE v2>=v1 -- ```

Solution 2

 ``` -- SELECT A.VALUE + 1 AS GapStart , (SELECT MIN(B.VALUE) FROM SeqVal B WHERE B.VALUE > A.VALUE) -1 AS GapEnd FROM SeqVal A WHERE not exists ( SELECT * FROM SeqVal C WHERE C.VALUE = A.VALUE +1 ) and (A.VALUE < (SELECT MAX(VALUE) FROM SeqVal)) -- ```

Solution 3

 ``` -- ;WITH CTE AS ( SELECT VALUE ,VALUE - ROW_NUMBER() OVER (ORDER BY VALUE) rnk FROM SeqVal ) ,CTE1 AS ( SELECT MIN(VALUE) - 1 GStart, MAX(VALUE) + 1 Gend, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnks FROM CTE Group By Rnk ) SELECT CASE WHEN a.GStart >= b.Gend THEN b.Gend ELSE a.GStart END GapStart, CASE WHEN a.GStart >= b.Gend THEN a.GStart ELSE b.Gend END GapEnd FROM CTE1 a INNER JOIN CTE1 b ON a.rnks-1 = b.rnks -- ```

Solution 4

 ``` -- SELECT cur+1 AS Start_V, nxt-1 AS End_V FROM (SELECT A.VALUE AS cur, (SELECT MIN(B.VALUE) FROM SeqVal B WHERE B.VALUE > A.VALUE ) AS nxt FROM SeqVal A) AS D WHERE nxt - cur > 1 -- ```

Performance Analysis – Execution Plans Comparison

Clearly the first method is the best one.

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com