/*
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
No comments:
Post a Comment