none
Otra de sumar en escalera hacia arriba. RRS feed

  • Pregunta

  • Hola a todos

    Tengo la siguiente tabla:

    DECLARE @articulos table (articulo varchar(4),nivel int,componentes varchar(4),coste decimal(5,2) )
    INSERT INTO @articulos VALUES
    ('A',10000,'A1',0),('A',20000,'A2',0),('A',20001,'A3',0)
    ,('A1',10000,'A11',0),('A1',10005,'A12',0.6)
    ,('A11',10000,'A111',0.2),('A11',10010,'A112',0.2)
    ,('A3',10000,'B',0.5)
    ,('A2',10000,'C',0.6)

    SELECT * FROM @articulos ORDER BY articulo

    Con esta consulta me sale casi todo bien calculado menos el nivel superior, es decir el coste total del articulo A

    SELECT A.articulo,SUM(ISNULL(B.sumacoste,A.coste))
    FROM @articulos AS A CROSS APPLY  
    ( SELECT SUM(T.coste) AS sumacoste FROM @articulos T WHERE T.articulo = A.componentes) AS B
    GROUP BY A.articulo
    ORDER BY A.articulo

    pues obtengo

    articulo CosteTotal
    A 1.70
    A1 1.00
    A11 0.40
    A2 0.60
    A3 0.50

    cuando en realidad el coste total de A debiera ser 2.10  

    select sum(coste) from @articulos

    ¿ que me falta hacer ?. Muchas gracias de antemano.


    jueves, 2 de agosto de 2018 11:37

Respuestas

  • Raimundo,

    No contamos con una sentencia (al menos en T-SQL) que permita hacer esta operacion a nivel de una jerarquia.  Una forma de lograrlo es expandiendo la relacion de cada padre con todos sus descendientes, lo cual es conocido como Transitive Closure en la teoria de grafos.  En este caso en particular la relacion tambien se expande a un nodo consigo mismo.

    Ejemplo:

    Padre    Descendiente
    A          A
    A          A1
    A          A2
    A          A3
    A          A11
    A          A111
    A          A112
    A          A12
    A          B
    A          C
    A1        A1
    A1        A11
    A1        A12
    A11      11
    A11      A111
    A11      A112
    A111    A111
    A112    A112
    A12      A12
    A2        A2
    A2        C
    A3        A3
    A3        B
    B          B
    C          C

    Ahora puedes usar este resultado para unir con tu tabla mediante la relacion (Descendiente, Componentes) y agrupar por el padre.

    Ejemplo con la raiz (A)

    Padre    Descendiente
    A          A
    A          A1
    A          A2
    A          A3
    A          A11
    A          A111 - 0.2
    A          A112 - 0.2
    A          A12 - 0.6
    A          B - 0.5
    A          C - 0.6
    ==============
                0.6 + 0.5 + 0.6 + 0.2 + 0.2 = 2.10

    Puedes usar una CTE recursiva para crear el Transitive Closure pero preferible que este se materialize para que puedas adicionar indices que soporten la operacion de union.

    Aca te dejo el codigo, el cual es un poco intimidante pero se debe principalmente a que has unido en una sola relacion la jerarquia y el costo de los articulos.

    DECLARE @articulos table (
    articulo varchar(4),
    nivel int,
    componentes varchar(4),
    coste decimal(5,2) 
    );
    
    INSERT INTO @articulos 
    VALUES
    	('A',10000,'A1',0),
    	('A',20000,'A2',0),
    	('A',20001,'A3',0),
    
    	('A1',10000,'A11',0),
    	('A1',10005,'A12',0.6),
    
    	('A11',10000,'A111',0.2),
    	('A11',10010,'A112',0.2),
    
    	('A3',10000,'B',0.5),
    	('A2',10000,'C',0.6);
    
    WITH A AS (
    SELECT
    	articulo AS padre,
    	articulo AS descendiente
    FROM
    	@articulos
    UNION
    SELECT
    	componentes,
    	componentes
    FROM
    	@articulos
    )
    , TC AS (
    SELECT
    	padre,
    	A.descendiente
    FROM
    	A
    
    UNION ALL
    
    SELECT
    	P.padre,
    	C.componentes
    FROM
    	TC AS P
    	INNER JOIN
    	@articulos AS C
    	ON C.articulo = P.descendiente
    )
    , Agg AS (
    SELECT
    	TC.padre,
    	SUM(A.coste) AS sum_coste
    FROM
    	TC
    	INNER JOIN
    	@articulos AS A
    	ON A.componentes = TC.descendiente
    GROUP BY
    	TC.padre
    )
    , H AS (
    SELECT DISTINCT
    	articulo AS descendiente,
    	CAST(NULL AS varchar(4)) AS padre,
    	0 AS lvl,
    	CAST(1 AS varbinary(900)) AS [path]
    FROM
    	@articulos AS A
    WHERE
    	NOT EXISTS (
    	SELECT
    		*
    	FROM
    		@articulos AS B
    	WHERE
    		B.componentes = A.articulo
    	)
    
    UNION ALL
    
    SELECT
    	C.componentes,
    	C.articulo,
    	P.lvl + 1,
    	CAST(P.[path] + CAST(ROW_NUMBER() OVER(PARTITION BY C.articulo ORDER BY C.componentes) AS binary(4)) AS varbinary(900))
    FROM
    	H AS P
    	INNER JOIN
        @articulos AS C
    	ON C.articulo = P.descendiente
    )
    SELECT
    	REPLICATE('||||||||', lvl) + H.descendiente AS articulo,
    	Agg.sum_coste
    FROM
    	H
    	INNER JOIN
        Agg
    	ON Agg.padre = H.descendiente
    ORDER BY
    	[H].[path];
    GO
    
    
    /*
    
    articulo	                sum_coste
    A	                        2.10
    ||||||||A1	                1.00
    ||||||||||||||||A11	        0.40
    ||||||||||||||||||||||||A111	0.20
    ||||||||||||||||||||||||A112	0.20
    ||||||||||||||||A12	        0.60
    ||||||||A2	                0.60
    ||||||||||||||||C	        0.60
    ||||||||A3	                0.50
    ||||||||||||||||B	        0.50
    
    */

    Puedes leer mas al respecto en el libro de Itzik Ben-Gan sobre T-SQL Querying


    AMB

    Some guidelines for posting questions...

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


    jueves, 2 de agosto de 2018 13:14

Todas las respuestas

  • Raimundo,

    No contamos con una sentencia (al menos en T-SQL) que permita hacer esta operacion a nivel de una jerarquia.  Una forma de lograrlo es expandiendo la relacion de cada padre con todos sus descendientes, lo cual es conocido como Transitive Closure en la teoria de grafos.  En este caso en particular la relacion tambien se expande a un nodo consigo mismo.

    Ejemplo:

    Padre    Descendiente
    A          A
    A          A1
    A          A2
    A          A3
    A          A11
    A          A111
    A          A112
    A          A12
    A          B
    A          C
    A1        A1
    A1        A11
    A1        A12
    A11      11
    A11      A111
    A11      A112
    A111    A111
    A112    A112
    A12      A12
    A2        A2
    A2        C
    A3        A3
    A3        B
    B          B
    C          C

    Ahora puedes usar este resultado para unir con tu tabla mediante la relacion (Descendiente, Componentes) y agrupar por el padre.

    Ejemplo con la raiz (A)

    Padre    Descendiente
    A          A
    A          A1
    A          A2
    A          A3
    A          A11
    A          A111 - 0.2
    A          A112 - 0.2
    A          A12 - 0.6
    A          B - 0.5
    A          C - 0.6
    ==============
                0.6 + 0.5 + 0.6 + 0.2 + 0.2 = 2.10

    Puedes usar una CTE recursiva para crear el Transitive Closure pero preferible que este se materialize para que puedas adicionar indices que soporten la operacion de union.

    Aca te dejo el codigo, el cual es un poco intimidante pero se debe principalmente a que has unido en una sola relacion la jerarquia y el costo de los articulos.

    DECLARE @articulos table (
    articulo varchar(4),
    nivel int,
    componentes varchar(4),
    coste decimal(5,2) 
    );
    
    INSERT INTO @articulos 
    VALUES
    	('A',10000,'A1',0),
    	('A',20000,'A2',0),
    	('A',20001,'A3',0),
    
    	('A1',10000,'A11',0),
    	('A1',10005,'A12',0.6),
    
    	('A11',10000,'A111',0.2),
    	('A11',10010,'A112',0.2),
    
    	('A3',10000,'B',0.5),
    	('A2',10000,'C',0.6);
    
    WITH A AS (
    SELECT
    	articulo AS padre,
    	articulo AS descendiente
    FROM
    	@articulos
    UNION
    SELECT
    	componentes,
    	componentes
    FROM
    	@articulos
    )
    , TC AS (
    SELECT
    	padre,
    	A.descendiente
    FROM
    	A
    
    UNION ALL
    
    SELECT
    	P.padre,
    	C.componentes
    FROM
    	TC AS P
    	INNER JOIN
    	@articulos AS C
    	ON C.articulo = P.descendiente
    )
    , Agg AS (
    SELECT
    	TC.padre,
    	SUM(A.coste) AS sum_coste
    FROM
    	TC
    	INNER JOIN
    	@articulos AS A
    	ON A.componentes = TC.descendiente
    GROUP BY
    	TC.padre
    )
    , H AS (
    SELECT DISTINCT
    	articulo AS descendiente,
    	CAST(NULL AS varchar(4)) AS padre,
    	0 AS lvl,
    	CAST(1 AS varbinary(900)) AS [path]
    FROM
    	@articulos AS A
    WHERE
    	NOT EXISTS (
    	SELECT
    		*
    	FROM
    		@articulos AS B
    	WHERE
    		B.componentes = A.articulo
    	)
    
    UNION ALL
    
    SELECT
    	C.componentes,
    	C.articulo,
    	P.lvl + 1,
    	CAST(P.[path] + CAST(ROW_NUMBER() OVER(PARTITION BY C.articulo ORDER BY C.componentes) AS binary(4)) AS varbinary(900))
    FROM
    	H AS P
    	INNER JOIN
        @articulos AS C
    	ON C.articulo = P.descendiente
    )
    SELECT
    	REPLICATE('||||||||', lvl) + H.descendiente AS articulo,
    	Agg.sum_coste
    FROM
    	H
    	INNER JOIN
        Agg
    	ON Agg.padre = H.descendiente
    ORDER BY
    	[H].[path];
    GO
    
    
    /*
    
    articulo	                sum_coste
    A	                        2.10
    ||||||||A1	                1.00
    ||||||||||||||||A11	        0.40
    ||||||||||||||||||||||||A111	0.20
    ||||||||||||||||||||||||A112	0.20
    ||||||||||||||||A12	        0.60
    ||||||||A2	                0.60
    ||||||||||||||||C	        0.60
    ||||||||A3	                0.50
    ||||||||||||||||B	        0.50
    
    */

    Puedes leer mas al respecto en el libro de Itzik Ben-Gan sobre T-SQL Querying


    AMB

    Some guidelines for posting questions...

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


    jueves, 2 de agosto de 2018 13:14
  • Muchas gracias Hunchback por tu respuesta, si uso tu solución con la tabla de ejemplo ejecuta perfectamente pero he sustituido mi tabla real y los campos reales y me da el error:

    "Mens. 240, Nivel 16, Estado 1, Línea 14
    Los tipos de la parte de delimitador y de la parte recursiva de la columna "padre" de la consulta recursiva "H" no coinciden."

    solo he sustituido "componentes" por ArticuloMP y el nombre de la tabla @articulos por FAB_RUTA_MPRIMA, mi tabla real es:

    CREATE TABLE [dbo].[FAB_RUTA_MPRIMA](
    [Articulo] [nvarchar](20) NOT NULL,
    [Seccion] [int] NOT NULL,
    [Fase] [int] NOT NULL,
    [Linea] [int] NOT NULL,
    [ArticuloMP] [nvarchar](20) NULL,
    [CodDescripcion] [int] NULL,
    [Almacen] [int] NULL,
    [Cantidad] [float] NULL,
    [PorcentajeResto] [float] NULL,
    [Resto] [float] NULL,
    [Coste] [float] NULL,
    [Observacion] [nvarchar](255) NULL,
    [Acceso] [float] NULL,
    [ts] [datetime] NULL )

    gracias una vez mas

    jueves, 2 de agosto de 2018 13:52
  • Muchas gracias Hunchback por tu respuesta, si uso tu solución con la tabla de ejemplo ejecuta perfectamente pero he sustituido mi tabla real y los campos reales y me da el error:

    "Mens. 240, Nivel 16, Estado 1, Línea 14
    Los tipos de la parte de delimitador y de la parte recursiva de la columna "padre" de la consulta recursiva "H" no coinciden."

    solo he sustituido "componentes" por ArticuloMP y el nombre de la tabla @articulos por FAB_RUTA_MPRIMA, mi tabla real es:

    CREATE TABLE [dbo].[FAB_RUTA_MPRIMA](
    [Articulo] [nvarchar](20) NOT NULL,
    [Seccion] [int] NOT NULL,
    [Fase] [int] NOT NULL,
    [Linea] [int] NOT NULL,
    [ArticuloMP] [nvarchar](20) NULL,
    [CodDescripcion] [int] NULL,
    [Almacen] [int] NULL,
    [Cantidad] [float] NULL,
    [PorcentajeResto] [float] NULL,
    [Resto] [float] NULL,
    [Coste] [float] NULL,
    [Observacion] [nvarchar](255) NULL,
    [Acceso] [float] NULL,
    [ts] [datetime] NULL )

    gracias una vez mas

    Ya va bien, era problema del tamaño de los campos. Saludos.
    jueves, 2 de agosto de 2018 14:11
  • Raimundo,

    Esta es una pregunta diferente a la original.  Por que no habres un hilo nuevo?


    AMB

    Some guidelines for posting questions...

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

    viernes, 3 de agosto de 2018 15:10
  • Raimundo,

    Esta es una pregunta diferente a la original.  Por que no habres un hilo nuevo?


    AMB

    Some guidelines for posting questions...

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


    Tienes razón, lo haré, saludos.
    sábado, 4 de agosto de 2018 23:46