T-SQL - How to find missed max and min value in a sequence of numbers

For example, I have a sequence of numbers: {1, 2, 5, 7}. I need to find the smallest and the biggest one, which are missed in this sequence (min=3 and max=6 for this example). Values can also be negative. Here is my solution, but it doesn`t pass on extra checking database (Wrong number of records (less by 1)), so I cant say what is exactly wrong. I also tried versions with LEFT OUTER JOIN and EXCEPT predicates - same problem. Please, help me to improve my solution.

WITH AA AS (SELECT MAX(Q_ID) MX              FROM UTQ),            BB AS (SELECT MIN(Q_ID) CODE              FROM UTQ                            UNION ALL                            SELECT CODE + 1              FROM BB              WHERE CODE < (SELECT MX                            FROM AA)              )    SELECT MIN(CODE) MIN_RES, MAX(CODE) MAX_RES  FROM BB  WHERE CODE NOT IN (SELECT Q_ID                     FROM UTQ)  

https://stackoverflow.com/questions/65545962/t-sql-how-to-find-missed-max-and-min-value-in-a-sequence-of-numbers January 03, 2021 at 09:56AM