Início > Computadores e Internet, Computers and Internet, SQL Server > Como completar sequência de “n” números pulados?

Como completar sequência de “n” números pulados?

Tenho uma tabela de Cadastro de Vendedor, porém com o CODIGO pulado.

Seq_Number_Pulado_Range_Faixa

Como completar o cadastro com os números pulados, até um “n” limite?

CODIGO NOME
001 João
002 VENDEDOR 002
003 Maria
004 Ricardo
005 Silvia
006 VENDEDOR 006
007 VENDEDOR 007
008 Marcos
009 VENDEDOR 009
010 Antonio
… ATÉ
049 VENDEDOR 049
050 Carlos
… ATÉ
299 VENDEDOR 299
300 Externo – Carlos
301 Externo – Juliana
… ATÉ
308 EXTERNO – 308
309 Externo – Renato
… ATÉ
399 EXTERNO – 399

Sabemos que:

a) O último código que desejamos inserir é 399.

b) Quando o código for menor que 300, o campo NOME deve ser VENDEDOR + CODIGO

c) Quando o código for maior que 300, o campo NOME deve ser EXTERNO + CODIGO

 

Problema:

Como encontrar as faixas/ranges de códigos pulados que são:

[2]; [6,7]; [9]; [11..49]; [51..299]; [302..307]; [310..399]

 

Solução:

Criaremos uma sequência completa de códigos [1..399].

Depois, vamos subtrair os códigos já cadastrados da lista completa.

Sobrando os códigos pulados.

Agora é só inserir os código pulados na tabela de Cadastro de Vendedores.

 

O detalhe está em na criação da tal lista completa de 1 até 399, para isso existem diversas possibilidades.

Fica a pergunta:

E se o último valor for 1.000.000?

Precisamos escrever um comando rápido e dinânico.

Para isso, publiquei Como criar uma tabela contendo uma sequência de "n" números?


Vamos aos comandos:

 

PRINT ‘
Preparando o cenário
======================’
CREATE TABLE #Tab_Vendedor (
Codigo CHAR(3),
Nome VARCHAR(100)
)

INSERT INTO #Tab_Vendedor VALUES(‘001’, ‘João’)
INSERT INTO #Tab_Vendedor VALUES(‘003’, ‘Maria’)
INSERT INTO #Tab_Vendedor VALUES(‘004’, ‘Ricardo’)
INSERT INTO #Tab_Vendedor VALUES(‘005’, ‘Silvia’)
INSERT INTO #Tab_Vendedor VALUES(‘008’, ‘Marcos’)
INSERT INTO #Tab_Vendedor VALUES(‘010’, ‘Antonio’)
INSERT INTO #Tab_Vendedor VALUES(‘050’, ‘Carlos’)
INSERT INTO #Tab_Vendedor VALUES(‘300’, ‘Externo – Carlos’)
INSERT INTO #Tab_Vendedor VALUES(‘301’, ‘Externo – Juliana’)
INSERT INTO #Tab_Vendedor VALUES(‘309’, ‘Externo – Renato’)

PRINT ‘
PASSO 1 – Lista de CODIGO cadastrados
==========’
SELECT * FROM #Tab_Vendedor

Seq_Number_Pulado_Range_Faixa

PRINT ‘
PASSO 2 – Lista com a sequência completa de 1 até 399
==========’
CREATE TABLE #Tab_Sequencia(
  Numero INT
)

DECLARE @Qtde_Numeros int, @Limite_Recursividade int
SET @Qtde_Numeros = 399
SET @Limite_Recursividade = 0

EXEC (
‘WITH n(n) AS
(
    SELECT 1
    UNION ALL
    SELECT n+1 FROM n WHERE n < ‘ + @Qtde_Numeros + ‘
)
INSERT INTO #Tab_Sequencia
SELECT n FROM n ORDER BY n
OPTION (MAXRECURSION ‘ + @Limite_Recursividade + ‘)’
)

SELECT * FROM #Tab_Sequencia

PRINT ‘
PASSO 3 – Comparar as duas listas, subtrair os códigos já cadastrados da lista completa.

==========’

SELECT S.Numero
FROM  #Tab_Sequencia S
WHERE S.Numero NOT IN (SELECT V.Codigo FROM #Tab_Vendedor V WHERE S.Numero = V.Codigo)

PRINT ‘
PASSO 4 – Usar o resultado encontrado para cadastrar na Tabela de Vendedores.
==========’

INSERT INTO #Tab_Vendedor (Codigo, Nome)
SELECT Codigo = RIGHT(‘000’ + CONVERT(VARCHAR(3),S.Numero), 3)
     , Nome = (CASE WHEN S.Numero <= 300 THEN ‘VENDEDOR ‘ ELSE ‘EXTERNO – ‘ END)
              + RIGHT(‘000’ + CONVERT(VARCHAR(3),S.Numero), 3)
FROM  #Tab_Sequencia S
WHERE S.Numero NOT IN (SELECT V.Codigo FROM #Tab_Vendedor V WHERE S.Numero = V.Codigo)

SELECT * FROM #Tab_Vendedor ORDER BY Codigo

Seq_Number_Pulado_Range_Faixa_Completa

DROP TABLE #Tab_Sequencia
DROP TABLE #Tab_Vendedor

  1. Mariana'
    22 de junho de 2015 às 15:09

    Na minha tabelo, o codigo é primary key. Como fazer?

  2. Mariana
    22 de junho de 2015 às 15:12

    Na minha tabela, o codigo é chave primária. Como fazer?

  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: