Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

The Missing Gaps [Multiple Methods - Best One]

The Missing Gaps [Multiple Methods – Best One]

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