Find missing table number

Asked

Viewed 98 times

2

Have a table with the following structure:

------------------------
Codigo | Num_doc | Serie 
------------------------
     1 |    1    |   1
     2 |    1    |   2
     3 |    3    |   2
     4 |    2    |   1
     5 |    3    |   1
     6 |    5    |   1
     7 |    7    |   1
     8 |    5    |   2
------------------------

I need to perform a select to get the Num_doc that "jumped" to each Serie, I mean, the consultation needs to return me: 

---------------
Num_doc | Serie 
---------------
   4    |   1
   6    |   1
   2    |   2
   4    |   2
---------------
sql-server

  • can be a process? and the document number (Num_doc) is the basis for the calculation?

  • Possible duplicate of Picking Gaps in an SQL sequence

  • select Codigo from tbExemplo where Codigo not in (select Num_doc from tbExemplo) Would that be?

  • 1

    Murilo, what you want is clear, at least for me. Your topic has been blocked here and there is no way to answer it. If you like, post your question in the forum "SQL Server - General Development" -> https://social.msdn.microsoft.com/Forums/sqlserver/pt-BR/home?forum=520

  • @Josédiz although he agrees with the fact that the topic is clear, as I quoted in a previous comment, this question already has an answer in the question Picking Gaps in an SQL sequence.

1 answer

2

To find the ranges there are a number of suggestions on the web classified as "gaps and Islands". Here is one of the suggestions, adapted from the article "The SQL of Gaps and Islands in Sequences":

-- código #1
with C as (
SELECT Serie, Num_doc, 
       row_number() over (partition by Serie order by Num_doc) as rownum
  from tabela
)
SELECT Cur.Serie as [Série], (Cur.Num_doc + 1) as [Início faixa], (Nxt.Num_doc - 1) as [Final faixa]
  from C as Cur
       inner join C as Nxt on Cur.Serie = Nxt.Serie 
                               and Nxt.rownum = Cur.rownum + 1
  where Nxt.Num_doc - Cur.Num_doc > 1;

Substitute table by table name.

I suggest reading the article "Generation of numerical sequences"if you need to generate sequential values and no jumps.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.