Find missing integers in a list of Values

Question

Currently, I have 12 rows with column Named 'Value'. The sample like this (just sample data, real data will be more):

Value
1
2
3
4
6
7
8
9
10
11
12
14

What I want is select them to get result like this:

Result          Result_Miss
1-4, 6-12, 14   5, 13

I want to avoid using a cursor to work row-by-row.

Answer 1

Dynamic, set-based approach using CTEs to hunt down the missing values, and write out the ranges available based on those missing values.

--(I can't seem to get SqlFiddle to work with CTE's or I'd post one up here)--

Reworked to be more dynamic for number of records:

This works provided you always have '1' in your set of value

CREATE TABLE #OneTen
(
   Value INT NOT NULL
);

INSERT INTO #OneTen
VALUES (1), (2), (3), (4), (6), (8), (9), (10), (11), (12), (14);


WITH ExpectedActual AS
(
   SELECT ot.Value AS Actual, ROW_NUMBER() OVER (ORDER BY Value) AS Expected
   FROM #OneTen AS ot
)
, DegreesOff AS
(
   SELECT ea.Expected, ea.Actual, (ea.Actual - ea.Expected) AS Change
   FROM ExpectedActual AS ea
)
, Missing AS
(
   SELECT CASE
             WHEN MIN(do.Expected) = 1 THEN 0
             ELSE MIN(do.Expected) + do.Change - 1
          END AS Missing
      , ROW_NUMBER() OVER (ORDER BY MIN(do.Expected)) AS RowNumber
   FROM DegreesOff AS do
   GROUP BY do.Change
   UNION ALL
   SELECT MAX(do.Actual + 1), MAX(do.Change + 2) --Adding Last Value 1 higher than Actual so the code below that takes mNext.Missing - 1 brings it down to the proper value:
     --Change + 2 to account for 0 plus being 1 higher
   FROM DegreesOff AS do
)
SELECT STUFF((
   SELECT ', ' + CASE
                    WHEN m.Missing + 1 = mNext.Missing - 1 THEN CAST(m.Missing + 1 AS NVARCHAR(4))
                    ELSE CAST(m.Missing + 1 AS NVARCHAR(4)) + '-' + CAST(mNext.Missing - 1 AS NVARCHAR(4))
                 END
   FROM Missing AS m
   LEFT JOIN Missing AS mNext ON m.RowNumber = mNext.RowNumber - 1
   FOR XML PATH('')), 1, 2, '') AS Result
   , STUFF((
      SELECT ', ' + CAST(MIN(do.Expected + do.Change - 1) AS NVARCHAR(4))
   FROM DegreesOff AS do
   WHERE do.Change > 0
   GROUP BY do.Change
   FOR XML PATH('')), 1, 2, '') AS Result_Miss

Answer 2

Try the following script:

DDL

CREATE TABLE Numbers
(
   Value INT NOT NULL
);

INSERT INTO Numbers
VALUES (1), (2), (3), (4), (6), (7), (8), (9), (10), (12),(13);

Script

DECLARE @MinValue INT
DECLARE @MaxValue INT
DECLARE @Temp TABLE(MissingValues INT)
DECLARE @MissingValues VARCHAR(50)


SELECT @MinValue = MIN(Value),
       @MaxValue = MAX(Value)
FROM Numbers


;WITH CTE AS
(
  SELECT @MinValue Value

  UNION ALL

  SELECT Value + 1
  FROM CTE
  WHERE Value + 1 <= @MaxValue
)
INSERT INTO @Temp
SELECT CTE.Value
FROM CTE 
LEFT JOIN Numbers N
ON CTE.Value = N.Value
WHERE N.Value IS NULL
OPTION (MAXRECURSION 1000)

SELECT @MissingValues = 
            STUFF((  SELECT ',' + CAST(MissingValues AS VARCHAR)
                     FROM @Temp 
                     FOR XML PATH('')),1,1,'')



INSERT INTO @Temp
SELECT @MinValue - 1

UNION ALL

SELECT @MaxValue + 1

;WITH CTE AS
(
  SELECT MissingValues,
         ROW_NUMBER() OVER(ORDER  BY MissingValues ASC) RN
  FROM @Temp
)
,Ranges AS
(
  SELECT CAST(T1.MissingValues + 1 AS VARCHAR) + '-' +
         CAST(T2.MissingValues - 1 AS VARCHAR) Ranges
  FROM CTE AS T1
  INNER JOIN CTE AS T2
  ON T1.RN = T2.RN - 1
)
SELECT STUFF((  SELECT ',' + R.Ranges
                FROM Ranges R
                FOR XML PATH('')),1,1,'') Result,
       @MissingValues AS Result_Miss