none
Agrupar Múltiples Registros RRS feed

  • Pregunta

  • Buenos días!

    Tengo la siguiente duda...

    Estoy tratando de armar un listado de clientes que tienen deudas en mi empresa en la cual a partir de este listado voy a armar un macro que enviará a cada cliente un mail con todos los comprobantes adeudados...

    El Query que yo armé es el siguiente...

    Select ClienteNombre,
    Concat('\\192.168.0.5\d$\eFactura$\',NroComprobante,'.pdf')as Adjunto,
    Convert(Nvarchar(12),FechaComprobante,103) as 'FechaComprobante',
    (ImporteTotal-ImportePagado) as 'ImporteAdeudado',
    'Contacto Expreso Brio SRL - Saldo Pendiente' as 'Asunto' 
    From 
    ClientesCtaCte 
    Inner Join Clientes On Clientes.ClienteID = ClientesCtaCte.ClienteID 
    Where 
    ImportePagado < ImporteTotal and (SUBSTRING(NroComprobante,2,4) = '0009' or SUBSTRING(NroComprobante,1,1) = 'X') 
    and TipoOperacion IN ('FAC','CPO') 
    and CuentaCorriente = 1

    Este Query me trae los datos pero mi problema está en que lo que yo necesitaría es que cuando el cliente se repite me agregue en una misma linea (columna adjunto) todos los comprobantes que le corresponden a ese cliente en donde se deben de sumar todos los importes pendientes de cada uno de los comprobantes y mostrarlos.

    Yo se que tengo que usar la consulta WITH para realizar eso creo pero no se aplicarla (es un talón de aquiles muy importante mio) podrían explicarme de manera sencilla como utilizar esta consulta? por que las explicaciones que dan por microsoft la verdad que no las entiendo...

    Fuera de eso buscando por internet conseguí que había chance también de agruparlos utilizando la función XML pero al realizar la operación me traía todos los clientes repetidos y me agrupaba un montón de comprobantes que estaban facturados a diferencia de los que no... Les adjunto esa query tmb a ver si me pueden decir donde tengo el error...

    Select ClienteNombre,
    STUFF(
    	(Select ',' + ClientesCtaCte.NroComprobante
    		From ClientesCtaCte
    		Where ClientesCtaCte.ClienteID = Clientes.ClienteID and 
    			ImportePagado < ImporteTotal and 
    			(SUBSTRING(NroComprobante,2,4) = '0009' or SUBSTRING(NroComprobante,1,1) = 'X') and 
    			TipoOperacion IN ('FAC','CPO') and CuentaCorriente = 1
    		For XML PATH('')),
    		1,2,'') as Comprobantes
    From ClientesCtaCte
    Where ImportePagado < ImporteTotal and (SUBSTRING(NroComprobante,2,4) = '0009' or SUBSTRING(NroComprobante,1,1) = 'X') 
    and TipoOperacion IN ('FAC','CPO') and CuentaCorriente = 1

    Espero me puedan ayudar.

    Muchas gracias.

    viernes, 13 de diciembre de 2019 14:51

Respuestas

  • Usamos SQL 2012

    Mariano, ¿la tabla ClientsCtaCte tiene una columna que identifica cada fila de forma única?

    ---

    Por favor, pruebe el siguiente código SQL:

    -- código #2 v2
    SELECT C.ClienteNombre, C.ClienteID,
           stuff ((SELECT '; ' + T.NroComprobante + '.pdf'
                     from ClientesCtaCte as T
                     where T.ClienteID = C.ClienteID
                           and T.ImportePagado < T.ImporteTotal
                           and (substring (T.NroComprobante, 2, 4) = '0009' or substring (T.NroComprobante, 1, 1) = 'X')
                           and T.TipoOperacion in ('FAC', 'CPO')
                           and T.CuentaCorriente = 1
                     for XML path('')),
                  1,2,'') as Adjunto,
           sum (CC.ImporteTotal - CC.ImportePagado) as ImporteAdeudado,
           'Contacto Expreso Brio SRL - Saldo Pendiente' as Asunto

      from ClientesCtaCte as CC
           inner join Clientes as C on C.ClienteID = CC.ClienteID

      where CC.ImportePagado < CC.ImporteTotal
            and (substring (CC.NroComprobante, 2, 4) = '0009' or substring (CC.NroComprobante, 1, 1) = 'X')
            and CC.TipoOperacion in ('FAC','CPO')
            and CC.CuentaCorriente = 1

      group by C.ClienteNombre, C.ClienteID;


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita

    • Editado José Diz viernes, 13 de diciembre de 2019 17:05
    • Marcado como respuesta Mariano D viernes, 13 de diciembre de 2019 17:34
    viernes, 13 de diciembre de 2019 16:40

Todas las respuestas

  • Si la versión de SQL Server es 2017, o más reciente, puede utilizar la función STRING_AGG() para recoger comprobantes del mismo cliente.

    -- código #1 v2
    SELECT ClienteNombre, Clientes.ClienteID,
           string_agg (concat ('\\192.168.0.5\d$\eFactura$\', NroComprobante, '.pdf'), '; ') as Adjunto,
           sum (ImporteTotal - ImportePagado) as ImporteAdeudado,
           'Contacto Expreso Brio SRL - Saldo Pendiente' as Asunto
    
      from ClientesCtaCte 
           inner join Clientes on Clientes.ClienteID = ClientesCtaCte.ClienteID 
    
      where ImportePagado < ImporteTotal
    and (substring (NroComprobante, 2, 4) = '0009' or substring (NroComprobante, 1, 1) = 'X') and TipoOperacion in ('FAC','CPO') and CuentaCorriente = 1 group by ClienteNombre, Clientes.ClienteID;

    No hice la prueba; puede contener error(es).


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    viernes, 13 de diciembre de 2019 15:06
  • Usamos SQL 2012 lamentablemente...

    Por lo que podrias hacerlo para esa version?

    Muchas Gracias por tu respuesta!

    viernes, 13 de diciembre de 2019 16:15
  • Usamos SQL 2012

    Mariano, ¿la tabla ClientsCtaCte tiene una columna que identifica cada fila de forma única?

    ---

    Por favor, pruebe el siguiente código SQL:

    -- código #2 v2
    SELECT C.ClienteNombre, C.ClienteID,
           stuff ((SELECT '; ' + T.NroComprobante + '.pdf'
                     from ClientesCtaCte as T
                     where T.ClienteID = C.ClienteID
                           and T.ImportePagado < T.ImporteTotal
                           and (substring (T.NroComprobante, 2, 4) = '0009' or substring (T.NroComprobante, 1, 1) = 'X')
                           and T.TipoOperacion in ('FAC', 'CPO')
                           and T.CuentaCorriente = 1
                     for XML path('')),
                  1,2,'') as Adjunto,
           sum (CC.ImporteTotal - CC.ImportePagado) as ImporteAdeudado,
           'Contacto Expreso Brio SRL - Saldo Pendiente' as Asunto

      from ClientesCtaCte as CC
           inner join Clientes as C on C.ClienteID = CC.ClienteID

      where CC.ImportePagado < CC.ImporteTotal
            and (substring (CC.NroComprobante, 2, 4) = '0009' or substring (CC.NroComprobante, 1, 1) = 'X')
            and CC.TipoOperacion in ('FAC','CPO')
            and CC.CuentaCorriente = 1

      group by C.ClienteNombre, C.ClienteID;


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita

    • Editado José Diz viernes, 13 de diciembre de 2019 17:05
    • Marcado como respuesta Mariano D viernes, 13 de diciembre de 2019 17:34
    viernes, 13 de diciembre de 2019 16:40
  • Jose, si había una columna que identificaba cada fila como unica que es la columna NroOperación pero yo no utilizaba esa columna debido a que la intersección entre tablas se daba con las columnas ClienteID entre ClientesCtaCte y Clientes.

    He podido llegar al resultado con tu query aunque le realicé un par más de retoques ya que vos no tenías el modelo del mismo pero hiciste lo que quería lograr!.

    Aqui te dejo el query final muchisimas gracias ojala algún dia haga querys como las tuyas!.

    SELECT C.ClienteNombre, C.ClienteID,
           stuff ((SELECT '; ' + T.NroComprobante + '.pdf'
                     from ClientesCtaCte as T
                     where T.ClienteID = C.ClienteID 
                           and T.ImportePagado <> T.ImporteTotal
                           and (substring (T.NroComprobante, 2, 4) = '0009' or substring (T.NroComprobante, 1, 1) = 'X') 
                           and T.TipoOperacion in ('FAC', 'CPO')
                     for XML path('')),
                  1,2,'') as Adjunto,
           sum (CC.ImporteTotal - CC.ImportePagado) as ImporteAdeudado,
           'Contacto Expreso Brio SRL - Saldo Pendiente' as Asunto
    
      from ClientesCtaCte as CC
           inner join Clientes as C on C.ClienteID = CC.ClienteID 
    
      where CC.ImportePagado <> CC.ImporteTotal
            and (substring (CC.NroComprobante, 2, 4) = '0009' or substring (CC.NroComprobante, 1, 1) = 'X') 
            and CC.TipoOperacion in ('FAC','CPO') 
            and C.CuentaCorriente = 1
    
      group by C.ClienteNombre, C.ClienteID
      Having SUM(CC.ImporteTotal - CC.ImportePagado) >= 50 
      Order by C.ClienteID;


    viernes, 13 de diciembre de 2019 17:34