none
Crear lista con registros de dos tablas con valores que pueden estar y no. RRS feed

  • Pregunta

  • Hola

    Primero que todo espero me perdonen el título, no encontraba nada mas adecuado.

    Tengo el siguiente escenario:

    declare @tabla2 table (id int, Descripcion varchar(20),comision decimal(6,2) )
    insert into @tabla2 values(1,'Vendedor 1',15.0),(2,'Vendedor 2',10.5),(3,'Vendedor 3',5.0)

    select * from @tabla2

    declare @tabla table (cliente varchar(8), IdVendedor1 int, comision1 decimal(6,2) ,IdVendedor2 int, comision2 decimal(6,2),IdVendedor3 int, comision3 decimal(6,2))

    insert into @tabla (cliente,idvendedor1,comision1) values('00001',1,15.0)
    insert into @tabla (cliente,idvendedor1,comision1) values('P00001',1,15.0)
    update @tabla set IdVendedor2 = 2, comision2 = 10.50 where cliente = 'P00001'

    select * from @tabla

    todo lo anterior para "armar" el ejemplo

    quisiera lograr generar una lista con Cliente, IdVendedor, DescVendedor, Comision de forma tal que primero sean los vendedores que tenga asociados el cliente para cada vendedor y después, los restantes clientes sin vendedores asociados y la comisión por defecto de cada vendedor, algo así:

    cliente Id,DescVendedor,Comision,Orden

    00001 1 Vendedor 1 15.00 1
    P00001 1 Vendedor 1 15.00 1
    P00001 2 Vendedor 2 10.50 2
    -- hasta aquí llego con el UNION que está mas abajo, pero me faltaría lo que sigue y no se por donde "entrarle"
    00001 2 Vendedor 2 10.50 99
    00001 3 Vendedor 3 5.00 99
    P00001 3 Vendedor 3 5.00 99

    SELECT cliente, Id,Descripcion AS DescVendedor, Comision,1 AS Orden
    FROM @tabla T1 INNER JOIN @tabla2 T2 On T1.IdVendedor1 = T2.id
    UNION SELECT cliente, id,Descripcion AS DescVendedor, Comision,2 AS Orden
    FROM @tabla T1 INNER JOIN @tabla2 T2 On T1.IdVendedor2 = T2.id
    UNION SELECT cliente, id,Descripcion AS DescVendedor, Comision,2 AS Orden
    FROM @tabla T1 INNER JOIN @tabla2 T2 On T1.IdVendedor2 = T2.id

    muchas gracias a todos de antemano, un saludo

    lunes, 26 de febrero de 2018 14:52

Respuestas

  • Entendido. 

    Ejecuta la siguiente consulta:

    WITH T (cliente, idVendedor, comision) AS
    (
        SELECT t.cliente, t.IdVendedor1 AS idVendedor, t.comision1 FROM @tabla t 
        UNION ALL
        SELECT t.cliente, t.IdVendedor2, t.comision2 FROM @tabla t 
        UNION ALL
        SELECT t.cliente, t.IdVendedor3, t.comision3 FROM @tabla t 
    )
    SELECT 
        t.cliente, t.idVendedor, t2.Descripcion, t.comision, 
        ROW_NUMBER() OVER(PARTITION BY t.cliente ORDER BY t.idVendedor) AS Orden
    FROM T t INNER JOIN @tabla2 t2 ON t.idVendedor = t2.id WHERE t.idVendedor IS NOT NULL
    UNION ALL
    SELECT c.cliente, t2.id, t2.Descripcion, t2.comision, 99
    FROM @tabla2 t2 CROSS APPLY (SELECT t.cliente FROM @tabla t GROUP BY t.cliente) c
    WHERE NOT EXISTS (SELECT 1 FROM T t WHERE t2.id = t.idVendedor AND c.cliente = t.cliente);
    GO

    Según los datos de prueba que adjuntas el conjunto resultante es:

    lunes, 26 de febrero de 2018 22:56

Todas las respuestas

  • No es correcto la estructura de columnas que especificas en la tabla '@tabla', es erróneo agregar columnas según la cantidad de filas de otra tabla. La tabla '@tabla' debe almacenar, como filas, cada relación entre el cliente y el vendedor, porque, ¿qué sucede si agregas un nuevo vendedor a la tabla '@tabla2'? ¿agregarás nuevas columnas a la tabla '@tabla' y actualizarás todos los objetos dependientes?, en definitiva no es una buena idea.

    Sin embargo, si no tienes opción a modificar la estructura de las tablas y la cantidad de vendedores es fija y se tratan de los mismos -la verdad lo dudo-, puedes crear un acercamiento por coincidencia, pero será muy sensible a los cambios, por ejemplo:

    WITH T (cliente, idVendedor, comision) AS
    (
        SELECT t.cliente, COALESCE(t.IdVendedor1, v.id), t.comision1
        FROM @tabla t CROSS APPLY (SELECT id FROM @tabla2 WHERE Descripcion = 'Vendedor 1') v
        UNION ALL
        SELECT t.cliente, COALESCE(t.IdVendedor2, v.id), t.comision2 
        FROM @tabla t CROSS APPLY (SELECT id FROM @tabla2 WHERE Descripcion = 'Vendedor 2') v
        UNION ALL
        SELECT t.cliente, COALESCE(t.IdVendedor3, v.id), t.comision3 
        FROM @tabla t CROSS APPLY (SELECT id FROM @tabla2 WHERE Descripcion = 'Vendedor 3') v
    )
    SELECT t.cliente, t.idVendedor, COALESCE(t.comision, t2.comision) AS Comision
    FROM T t INNER JOIN @tabla2 t2 ON t.idVendedor = t2.id
    ORDER BY IIF(t.comision IS NOT NULL, 0, 1), t.cliente, t.idVendedor;
    GO

    * Considera que la consulta no retornará los resultados esperados en caso algún valor de la columna 'Descripción' cambie y lógicamente se tendrá que actualizar en caso la cantidad de vendedores varíe, reitero que no es lo correcto.

    lunes, 26 de febrero de 2018 16:17
  • No es correcto la estructura de columnas que especificas en la tabla '@tabla', es erróneo agregar columnas según la cantidad de filas de otra tabla. La tabla '@tabla' debe almacenar, como filas, cada relación entre el cliente y el vendedor, porque, ¿qué sucede si agregas un nuevo vendedor a la tabla '@tabla2'? ¿agregarás nuevas columnas a la tabla '@tabla' y actualizarás todos los objetos dependientes?, en definitiva no es una buena idea.

    Sin embargo, si no tienes opción a modificar la estructura de las tablas y la cantidad de vendedores es fija y se tratan de los mismos -la verdad lo dudo-, puedes crear un acercamiento por coincidencia, pero será muy sensible a los cambios, por ejemplo:

    WITH T (cliente, idVendedor, comision) AS
    (
        SELECT t.cliente, COALESCE(t.IdVendedor1, v.id), t.comision1
        FROM @tabla t CROSS APPLY (SELECT id FROM @tabla2 WHERE Descripcion = 'Vendedor 1') v
        UNION ALL
        SELECT t.cliente, COALESCE(t.IdVendedor2, v.id), t.comision2 
        FROM @tabla t CROSS APPLY (SELECT id FROM @tabla2 WHERE Descripcion = 'Vendedor 2') v
        UNION ALL
        SELECT t.cliente, COALESCE(t.IdVendedor3, v.id), t.comision3 
        FROM @tabla t CROSS APPLY (SELECT id FROM @tabla2 WHERE Descripcion = 'Vendedor 3') v
    )
    SELECT t.cliente, t.idVendedor, COALESCE(t.comision, t2.comision) AS Comision
    FROM T t INNER JOIN @tabla2 t2 ON t.idVendedor = t2.id
    ORDER BY IIF(t.comision IS NOT NULL, 0, 1), t.cliente, t.idVendedor;
    GO

    * Considera que la consulta no retornará los resultados esperados en caso algún valor de la columna 'Descripción' cambie y lógicamente se tendrá que actualizar en caso la cantidad de vendedores varíe, reitero que no es lo correcto.

    Gracias por tu respuesta Williams. Con respecto a tu observación aunque haya mas vendedores en @tabla2, que de hecho los hay, cada registro en @tabla solo puede tener como máximo tres asociaciones. Por favor me podrías decir ¿ como puedo hacer esto mismo pero de forma general ?. Una vez más muchas gracias.
    lunes, 26 de febrero de 2018 20:31
  • Antes de continuar posteando código necesito respondas a dos preguntas:

    - ¿Continuarás con la practica de mantener 6 columnas para registrar información de los tres vendedores por cliente?

    - Sea el caso que hayas elegido, ¿la lista adicional que deseas agregar se basa en todos los vendedores que no se encuentren registrados por cliente?, es decir, si tienes ocho vendedores y sólo un vendedor registrado para el cliente A, ¿requieres -además del vendedor registrado- mostrar los siete vendedores restantes?

    lunes, 26 de febrero de 2018 21:33
  • Hola Williams, no me queda otra pues así están diseñadas las tablas, como te explicaba un cliente puede tener hasta 3 vendedores y cada uno con una comisión distinta. En cuanto a la lista es exactamente lo que me preguntas, los que ya están registrados, con numero de orden 1,2 o 3 y sus comisiones y el resto que aún no están asignados con numero de orden 99 para distinguirlos y sus comisiones. Esta lista la utilizaré en 3 combobox uno para cada Vendedor1, vendedor2 o vendedor3 Gracias.




    lunes, 26 de febrero de 2018 22:07
  • Entendido. 

    Ejecuta la siguiente consulta:

    WITH T (cliente, idVendedor, comision) AS
    (
        SELECT t.cliente, t.IdVendedor1 AS idVendedor, t.comision1 FROM @tabla t 
        UNION ALL
        SELECT t.cliente, t.IdVendedor2, t.comision2 FROM @tabla t 
        UNION ALL
        SELECT t.cliente, t.IdVendedor3, t.comision3 FROM @tabla t 
    )
    SELECT 
        t.cliente, t.idVendedor, t2.Descripcion, t.comision, 
        ROW_NUMBER() OVER(PARTITION BY t.cliente ORDER BY t.idVendedor) AS Orden
    FROM T t INNER JOIN @tabla2 t2 ON t.idVendedor = t2.id WHERE t.idVendedor IS NOT NULL
    UNION ALL
    SELECT c.cliente, t2.id, t2.Descripcion, t2.comision, 99
    FROM @tabla2 t2 CROSS APPLY (SELECT t.cliente FROM @tabla t GROUP BY t.cliente) c
    WHERE NOT EXISTS (SELECT 1 FROM T t WHERE t2.id = t.idVendedor AND c.cliente = t.cliente);
    GO

    Según los datos de prueba que adjuntas el conjunto resultante es:

    lunes, 26 de febrero de 2018 22:56
  • Un millón de gracias Williams, ha funcionado perfectamente con mis datos reales, saludos.
    martes, 27 de febrero de 2018 10:23