Picking Gaps in an SQL sequence

Question

Hello, today I own a select that returns me the following result:

I do another select that returns a value, 5 for example. I would need to know which numbers are not between number 1 and number 5. In my case I would need the following result 2, 4, 5.

You’d need help developing that logic.

Answer 1

You can use the clause WITH to generate the sequence of number and compare it with the table in question:

WITH sequencias
  AS (SELECT 1 AS sequencia
      UNION ALL
      SELECT s.sequencia + 1 AS sequencia
        FROM sequencias s
       WHERE s.sequencia <= 5)
SELECT s.*
  FROM sequencias s
 WHERE NOT EXISTS(SELECT 1
                    FROM tabela t
                   WHERE t.nr_volume = s.sequencia)
 ORDER BY s.sequencia
OPTION(MAXRECURSION 0);

---

We have a great command explanation WITH in answer to the question Using WITH AS command in Sql Server.