January 3, 2012

Find all missing number in a sequence

We have following sequence number in our table.

image

Now we have requirement to find all missing sequence number from above list.
It means our output should be

image

T-SQL

DECLARE
@MSN TABLE
(VAL INT)

INSERT INTO @MSN
SELECT 1 UNION ALL
SELECT 4 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 10

;WITH CTE(SN)
AS
(
SELECT 1 SN
UNION ALL
SELECT SN+1 FROM CTE WHERE SN < 10
)
SELECT CTE.* FROM CTE
LEFT JOIN @MSN T1 ON T1.VAL=CTE.SN
WHERE
T1.VAL IS NULL


That’s It.
Enjoy Learning.

No comments:

Post a Comment