none
Encontrar Numeros Faltantes en Secuencia. RRS feed

  • Pregunta

  • Hola a todos y de antemano gracias por quienes se molesten en responder.

    Mi situación tengo una tabla con la siguiente estructura

    Tabla Actas

           [EDO_OFI] int       ,[ANIO] int       ,[MUN_OFI] int       ,[LIBRO] int       ,[ACTA] int

    EDO_OFI

    ANO

    MUN_OFI

    LIBRO

    ACTA

    22

    1848

    3

    1

    1

    22

    1848

    3

    1

    2

    22

    1848

    3

    1

    3

    22

    1848

    3

    1

    7

    22

    1848

    3

    1

    8

    22

    1848

    3

    1

    15

    22

    1848

    3

    1

    16

    22

    1848

    3

    1

    17

    22

    1848

    3

    1

    20

    22

    1848

    3

    1

    22

    22

    1848

    3

    1

    23

    En la secuencia del campo Acta hay ciertos números que faltan, necesito a través de un query determinar esos faltantes.

                                                                       

    martes, 13 de diciembre de 2016 16:14

Respuestas

Todas las respuestas

  • alecerritos,

    Puedes hacer lo siguiente:

    DECLARE @NumeroInicial int;
    DECLARE @NumeroFinal int;
    
    SELECT @NumeroInicial = MIN(ACTA), @NumeroFinal = MAX(ACTA) FROM TablaActas;
    
    WITH Secuencia AS
    (
        SELECT @NumeroInicial AS [Numero]
        UNION ALL
        SELECT Numero + 1 FROM Secuencia WHERE Numero < @NumeroFinal
    )
    SELECT
        s.Numero
    FROM
        Secuencia s
    WHERE
        NOT EXISTS (SELECT 1 FROM TablaActas WHERE (ACTA = s.Numero));


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    martes, 13 de diciembre de 2016 16:36
  • Cual version de SQL Server usas?

    Este problema es conocido como "hayar huecos en una secuencia" (GAPS) y para lo cual contamos con funciones de off-set que hacen la tarea mas facil.

    Desde la version 2012 contamos con funciones de off-set que permiten referenciar una columna o expresion de filas anteriores y/o posteriores dado un orden especifico ( funciones LAG / LEAD).

    DECLARE @T table (
    EDO_OFI int,
    ANO int,
    MUN_OFI int,
    LIBRO int,
    ACTA int);
    
    INSERT INTO @T
    	(EDO_OFI, ANO, MUN_OFI, LIBRO, ACTA)
    VALUES
    	(22, 1848, 3, 1, 1),
    	(22, 1848, 3, 1, 2),
    	(22, 1848, 3, 1, 3),
    	(22, 1848, 3, 1, 7),
    	(22, 1848, 3, 1, 8),
    	(22, 1848, 3, 1, 15),
    	(22, 1848, 3, 1, 16),
    	(22, 1848, 3, 1, 17),
    	(22, 1848, 3, 1, 20),
    	(22, 1848, 3, 1, 22),
    	(22, 1848, 3, 1, 23);
    
    SELECT *
    FROM @T
    ORDER BY EDO_OFI, ANO, MUN_OFI, LIBRO, ACTA;
    
    WITH R AS (
    SELECT
        EDO_OFI,
        ANO,
        MUN_OFI,
        LIBRO,
        ACTA,
        LAG(ACTA, 1, 1) OVER(PARTITION BY EDO_OFI, ANO, MUN_OFI, LIBRO ORDER BY ACTA) AS prv_ACTA
    FROM
        @T
    )
    SELECT
        EDO_OFI,
        ANO,
        MUN_OFI,
        LIBRO,
        prv_ACTA + 1 AS from_acta,
        ACTA - 1 AS to_acta
    FROM
        R
    WHERE
        ACTA - prv_ACTA > 1;
    GO


    Si cuentas con una tabla auxiliar de numeros (muy util por cierto), como la que nos ensenia Itzik Ben-Gan en este articulo, entonces puedes unir este resultado final a dicha tabla usando el operador BETWEEN.

    http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers

    http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions

    DECLARE @T table (
    EDO_OFI int,
    ANO int,
    MUN_OFI int,
    LIBRO int,
    ACTA int);
    
    INSERT INTO @T
    	(EDO_OFI, ANO, MUN_OFI, LIBRO, ACTA)
    VALUES
    	(22, 1848, 3, 1, 1),
    	(22, 1848, 3, 1, 2),
    	(22, 1848, 3, 1, 3),
    	(22, 1848, 3, 1, 7),
    	(22, 1848, 3, 1, 8),
    	(22, 1848, 3, 1, 15),
    	(22, 1848, 3, 1, 16),
    	(22, 1848, 3, 1, 17),
    	(22, 1848, 3, 1, 20),
    	(22, 1848, 3, 1, 22),
    	(22, 1848, 3, 1, 23);
    
    SELECT *
    FROM @T
    ORDER BY EDO_OFI, ANO, MUN_OFI, LIBRO, ACTA;
    
    WITH R AS (
    SELECT
        EDO_OFI,
        ANO,
        MUN_OFI,
        LIBRO,
        ACTA,
        LAG(ACTA, 1, 1) OVER(
        PARTITION BY EDO_OFI, ANO, MUN_OFI, LIBRO 
        ORDER BY ACTA
        ) AS prv_ACTA
    FROM
        @T
    )
    SELECT
        R.EDO_OFI,
        R.ANO,
        R.MUN_OFI,
        R.LIBRO,
        R.prv_ACTA + 1 AS from_acta,
        R.ACTA - 1 AS to_acta,
        S.n
    FROM
        R
        INNER JOIN
        dbo.ufn_GetNums(1, 99999) AS S
        ON S.n > R.prv_ACTA
        AND S.n < R.ACTA
    WHERE
        R.ACTA - R.prv_ACTA > 1
    ORDER BY
        R.EDO_OFI,
        R.ANO,
        R.MUN_OFI,
        R.LIBRO,
        S.n;
    GO

    Nota que he supuesto que deseas obtener los numeros de secuencia por (EDO_OFI, ANO, MUN_OFI, LIBRO) pero puedes cambiar la particion y/o ordenamiento de acuerdo a tus requerimientos.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    • Editado HunchbackMVP martes, 13 de diciembre de 2016 19:55
    martes, 13 de diciembre de 2016 18:29
  • Hola tengo la version 2014 Standar, la respuesta de  Willams Morales  la estaba acondicionando a mi requerimiento y revisare esta propuesta  Hunchback a fin de obtener el mejor resultado.

    Cabe mencionar que tengo otra tabla donde podria obtener el numero Inicial y Final de cada cada libro con los datos EDO_OFI, ANO, MUN_OFI, LIBRO.

    • Editado alecerritos martes, 13 de diciembre de 2016 19:47
    martes, 13 de diciembre de 2016 19:44
  • La version que sugeri suele tener buen desempenio si cuentas con un indice por la particion y el orden (POC), pues suele hacer el calculo en una pasada.

    (EDO_OFI, ANO, MUN_OFI, LIBRO, ACTA)


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    martes, 13 de diciembre de 2016 20:12
  • Buenas tardes Colega, prueba con el siguiente Query, funciona al 100% y es bastante corto.

    declare @Min int, @Max int
    SELECT @Min = min(Acta), @Max = max(Acta) FROM Actas
    DECLARE @Numero AS TABLE([Numbers] INT)
    
    
    WHILE @Min < @Max
    BEGIN;
    	IF NOT EXISTS(SELECT 1 FROM Actas WHERE @Min = Acta)
    	BEGIN
    		INSERT INTO @Numero values (@Min)
    	END
    	SET @Min = @Min + 1
    END
    
    SELECT * FROM @Numero WHERE Numbers not in (SELECT Acta FROM Actas)

    Espero resuelva tu inconveniente, Saludos.

    • Propuesto como respuesta Joyce_ACModerator miércoles, 14 de diciembre de 2016 16:09
    martes, 13 de diciembre de 2016 20:18
  • Este comentario esta dirigido principalmente a Joyce JP.

    En mi humilde opinion, esta sugerencia seria la menos indicada para ser marcada como respuesta. Esta es una solucion basada en recorrido fila a fila no solo del incremento de los numeros a buscar sino que por cada uno de ellos se haria una busqueda en la tabla, asi que imaginate que la numeracion sea de miles y que la tabla a buscar contenga millones de filas pues el desempenio de esta solucion seria pesimo.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    • Editado HunchbackMVP miércoles, 14 de diciembre de 2016 16:24
    miércoles, 14 de diciembre de 2016 16:21
  • Hola Hunchback ,

    De acuerdo, gracias por tu comentario y aclaración, nos mantenemos al tanto.

    Saludos

    Joyce
    __________________________________________________________________________________________
    Por favor, recuerde "Marcar como respuesta" las respuestas que hayan resuelto su problema, hace que sea más fácil para los otros visitantes encontrar la solución  más tarde. Microsoft ofrece este servicio de forma gratuita, con la finalidad de ayudar a los usuarios y la ampliación de la base de datos de conocimientos relacionados con los productos y tecnologías de Microsoft. Este contenido es proporcionado "tal  cual" y no implica ninguna responsabilidad de parte de Microsoft.


    jueves, 15 de diciembre de 2016 17:59
    Moderador