Showing posts with label find gaps in sql. Show all posts
Showing posts with label find gaps in sql. Show all posts

Monday, March 8, 2010

Write the query to find values where Gaps are starting

/*
There is a table like below

#Numbers (SeqNumber INT)

The data in the column is like this. 1, 2, 4, 5, 10, 11, 15, 1 billion

Write the query to find values where Gaps are starting. Like 2, 5, 11…
*/

IF OBJECT_ID('tempdb..#Numbers') IS NOT NULL
DROP TABLE #Numbers

CREATE TABLE #Numbers (SeqNumber INTEGER NOT NULL PRIMARY KEY);

INSERT INTO #Numbers VALUES (0); --sentinel
INSERT INTO #Numbers VALUES (2);
INSERT INTO #Numbers VALUES (3);
INSERT INTO #Numbers VALUES (5);
INSERT INTO #Numbers VALUES (7);
INSERT INTO #Numbers VALUES (8);
INSERT INTO #Numbers VALUES (14);
INSERT INTO #Numbers VALUES (20);


SELECT s1.SeqNumber+1
FROM #Numbers s1
LEFT JOIN #Numbers s2
ON s1.SeqNumber = s2.SeqNumber -1
WHERE s2.SeqNumber IS NULL

output:

1
4
6
9
16
21

SELECT s1.SeqNumber+1 Start,MIN(s3.SeqNumber)-1 Finish
FROM #Numbers s1
LEFT JOIN #Numbers s2 ON s1.SeqNumber = s2.SeqNumber -1
LEFT JOIN #Numbers s3 ON s1.SeqNumber < s3.SeqNumber
WHERE s2.SeqNumber IS NULL
GROUP BY s1.SeqNumber

Start Finish
1 1
4 4
6 6
9 13
15 19
21 NULL