Registros que suman un valor determinado

[LuNaTk]

Hola estoy tratando de realizar un query que me devuelva los registros que suman en uno de sus campos una cantidad determinada.

Tengo una bd que almacena pagos, estos pagos tienen un id, fecha, no. de cuenta, tipo de pago, y el monto del pago, requiero obtener los registros que sumen una cantidad determinada, por ejemplo obtener los registros que sumados por el monto del pago sumen 200, 300 . . . o cualquier valor.

He intentado hacer algo asi ...

Código SQL [-]
select pag_id,sum(pag_total)
from pago having sum(pag_total)=250

pero obviamente me manda un error diciendo que la columna pago no se puede seleccionar porque no esta bajo una clausula group by ... pero si la agrupo por el pag_id que viene siendo la llave de la tabla, entonces me va a devolver solo los registros que sean igual a 250. pero el detalle esta en que no se trata de buscar un registro identico porque muchas veces no lo hay, entonces tengo que recuperar uno de 100, otro de 100 y uno de 50 (por dar un ejemplo), para que me sume la cantidad que busco entonces espero recuperar esas 3 claves primarias ó tantas claves como pagos se sumen para obtener la cantidad deseada...

me pueen echar una mano?

[marcoszorrilla]

O te estás explicando mal, o lo que quieres es imposible, si sumas los elementos de una tabla sin ninguna condición, (Where), siempre te va a sumar todos los elementos y te arrojará el mismo resultado, a no ser que entre una suma y otra haya habido cambios en la tabla.

Y si utilizas group by que sospecho que va por ahí la solución, nunca podrás obtener los Id, como mucho podrás obtener el mayor, menor, la media, etc.

Así que creo que toca explayarse más ampliamente sobre el tema.

Un Saludo.

[LuNaTk]

quise negarme a creer que es imposible...

muy bien voy a intentar explicarlo mejor, la tabla pago contiene el registro de todos los pagos realizados, estoy hablando de un sistema de cobro de agua.

Listare algunas de las columnas principales

pag_id,
pag_fecha,
pag_monto,
pag_status

el interes de este query es, ya que no todos los pagos son facturados en el mes que le corresponde, algunos usuarios piden factura en el siguiente mes, entonces tengo la necesidad de generar esa factura con varios pagos hechos en el mes actual, entonces necesito que la consulta me devuelva los pagos en donde la suma por el campo pag_monto sea igual a una determinada cantidad.

P.Ej. el mes anterior un usuario (por x ó y) olvido facturar un pago de 1000 pesos, para ese mes anterior yo genere una factura global de venta al publico en general con lo que cerre mi periodo, entonces el usuario al siguiente mes me la solicita, yo entro en discusion con el diciendole que ya no se puede generar por x razones fiscales etc. Pero el round lo gana el cliente y me veo obligado a generar en este mes una factura por esos 1000 pesos.

En primer lugar no puedo usar el registro de pago original porque ya fue tomado para generar la factura global, entonces tengo que buscar dentro de los pagos del mes actual y dentro de los pagos que no facturan individualmente, los registros que sumados me den 1000 pesos sin importar si va a encontrar 1, 2, 3 o n registros, lo ideal es que encontrara otro registro de 1000 pesos y listo pero muchas veces hacen pagos donde forzosamente tengo que agrupar ó sumar mas de un pago para obtener la cantidad que busco.

La unica condicion que puedo agregar a la clausula where es la fecha, que sean pagos del mes actual, pero aun asi no se como obtener los Id de los registros

De pronto he pensado que por medio de una simple consulta no es posoble, y quiza tenga que hacerlo con cursores para ir probando con todas las combinaciones posibles ...

[[gatosoft]]

Hola LuNaTk,

En primer lugar, el motor de base de datos te esta exigiendo que utilices la clausula Group By... es obligatoria siempre que estes trabajando con Sum, count, avg, etc... Esto aplica para todos los motroes de base de datos, pues es parte del estandar SQL-92. (aunque parezca redundante es necesario)


Por otro lado, veo que estamos un poco confundidos, pues la clausula Having es un where a nivel de agrupación, asi que no hay problema ... ¿ya intentaste con la siguiente sentencia?

Código SQL [-]
Select pag_id,sum(pag_total)
from pago 
Group by pag_id
having sum(pag_total)=250

debe darte el resultado que esperas...

La siguiente sentencia (Dependiedo de si el motor que usas soporta subquerys) deberia darte el mismo resultado...

Código SQL [-]
Select *
from (
Select pag_id,sum(pag_total) as total
from pago 
Group by pag_id
) as SubSelect1
where total = 250

un saludo,

[LuNaTk]

Hola [gatosoft]

Asi es el having hace el where para el operador Sum.

El query que me sugieres es valido, pero, como el pag_id es la llave primaria y es identity, entonces es lo mismo que poner

Código SQL [-]
select pag_id,pag_total
from pago
where pag_total=250

Porque al agruparlo por la clave primaria, no sumas mas que el total de ese mismo registro.

Con ese query estoy buscando los pagos con el total igual a 250 y como primer opcion es aceptable, es decir encontrar un pago con el monto igual al deseado.

Pero suponiendo que no tengo ningun otro pago por 250, pero si tengo muchos de 100 y muchos de 50, el query tendria que devolverme ya sea dos de 100 y uno de 50, ó bien 1 de a 100 y 3 de 50, ó bien 5 de a 50...

Existe forma de lograr esto con un query?

[[gatosoft]]

Ok LuNaTk,

El confundido era yo, no leí bien tu descripción e interpreté diferente las cosas... espero esta vez si haberlo entendido como es...

Yo probé la siguiente consulta y me funcionó OK en PosgreSQL

Código SQL [-]
select * 
from (
select *, (select sum(BB.pag_monto) from pago BB where BB.pag_id <= AA.pag_id and BB.pag_status='PENDIENTE') as totalx
from pago AA
  where AA.pag_status='PENDIENTE'
) as subquery
where totalx <= 250

Lo que hace recuperar los movimientos mas antiguos que sumen los 250 que buscas o que se aproximen (por debajo a este monto)...

entonces teiendo estos datos:

pag_id, pag_monto, pag_status
1;10;"PENDIENTE"
2;8;"PENDIENTE"
3;3;"PENDIENTE"
4;12;"OK"
5;60;"PENDIENTE"
6;20;"PENDIENTE"
7;13;"PENDIENTE"
8;8;"OK"
9;9;"PENDIENTE"
10;15;"PENDIENTE"
11;5;"OK"
12;50;"PENDIENTE"

y enviando la consulta para un valor de 81 me retornaría:

pag_id, pag_monto, pag_status
1;10;"PENDIENTE"
2;8;"PENDIENTE"
3;3;"PENDIENTE"
5;60;"PENDIENTE"

Ahora si quisieras encontrar el número exacto, escogiendo y descartando filas interedias... ahí si te tocaría hacer un procedimiento almacenado o funcion que te ayude....


me refiero a un ejemplo como este:

Tengo los siguientes numeros y busco aquellos que me sumen 94...

60
50
20 OK
15 OK
13 OK
12 OK
10 OK
9
8 OK
8 OK
5 OK
3 OK

Para llegar a este resultado, tuve que ordenarlos (de menor a mayor o al contrario de igual) y luego aplicar un algoritmo que toma y descarta numeros... bueno eso creo, no se si este equivocado

[LuNaTk]

asi es gatosoft

ya nos entendimos, pues si, estoy entonces haciendo el stored procedure para realizar esa búsqueda de pagos, si tengo exito compartire el codigo, saludos!

tenias razon marcoszorrilla, aprece que es imposible en un query

[Casimiro Notevi]

Creo que, en principio, no se puede hacer.
Debería ser inteligente para descartar una cantidad y sumar otras, y eso actualmente no existe, habría que inventarlo

[LuNaTk]

Despues de un buen rato de depuracion, por fin tengo lo que necesito, comparto el código, muy seguramente se puede optimizar, a mi no me gusta usar cursores asi que los implemento en ciclos while.

Código SQL [-]
create procedure sp_c_pago_reemplazo @Total numeric(19,2),@Fecha datetime
as
set nocount ON
declare @Pago table (n int identity(1,1),pag_id int,pag_total numeric(19,2))
declare @Resultado table (n int,pag_id int,pag_total numeric(19,2))

-- Recupero los Pagos del Mes
insert @pago
select 
pag_id,pag_total
from pago
where pag_total<=@Total
and pag_status='A'
and pag_id not in (select pag_id from cfdi_pago)
and year(pag_fecha)=year(@Fecha)
and month(pag_fecha)=month(@Fecha)
order by pag_total desc,pag_id desc

declare
@pag_id int,
@pag_total_idx numeric(19,2),
@pag_total numeric(19,2),
@n int,
@l int,
@n_ int,
@l_ int,
@c int,
@r int,
@Suma numeric(19,2)

select @n=MIN(n),@l=MAX(n),@Suma=0 from @Pago
while (@n<=@l)
 begin
   -- Limpio tabla de resultados
   delete from @Resultado
   set @Suma=0
   -- Tomo el primer pago
   select @pag_total_idx=pag_total,@pag_id=pag_id from @pago where n=@n
   if @pag_total_idx<=@Total
     insert @Resultado select @n,@pag_id,@pag_total_idx
   -- Verifico si se encontro el total
   if @Total=@pag_total_idx
     Goto Final
   else
     begin
        select 
        @c=COUNT(n),
        @r=0 
        from @Pago 
        where pag_total<=@Total-@pag_total_idx
        print 'Repeticiones '+cast(@c as varchar)
        while @r<@c
          begin  -- Repito la busqueda para todas las combinaciones
            delete from @Resultado where n>@n
            set @Suma=0
            select 
            @n_=MIN(n)+@r,
            @l_=@l
            from @Pago 
            where pag_total<=@Total-@pag_total_idx         
            while @n_<=@l_
              begin
                select @pag_total=pag_total,@pag_id=pag_id from @pago where n=@n_
                --print 'Nivel '+cast(@pag_total as varchar)            
                if (@pag_total_idx+@Suma+@pag_total)<=(@Total)
                  begin
                    insert @Resultado select @n_,@pag_id,@pag_total 
                    set @Suma=@Suma+@pag_total
                  end
                if (@Suma+@pag_total_idx)=@Total  -- Verifico si se encontro el total
                  Goto Final 
                set @n_=(select MIN(n) from @Pago where n>@n_ and pag_total<@pag_total) 
              end
            set @r=@r+1
          end
     end
   set @n=(select MIN(n) from @pago where n>@n and pag_total<@pag_total_idx)
 end
 Final:
 select pag_id,pag_total from @Resultado

De manera breve explico lo que hace.

Recupero y guardo en memoria los pagos que no se hayan facturado todavia y que el monto sea menor ó igual al importe deseado, guardo en memoria para poder trabajar con esos datos sin tener que acceder a las tablas fisicas debido a que el sistema esta siendo usado y tiene un alto indice de concurrencia. Guardo los valores minimo y maximo de Ids de los pagos para recorrerlos en un ciclo while primario el cual se encargara de recorrer uno a uno los pagos de manera descendente en el monto.

Si encuentro un pago igual a el monto que estoy buscando, finalizo el ciclo y muestro los resultados.
Si no encuentro un pago igual al monto, entonces tengo uno con un monto menor ya que asi esta establecida la consulta y procedo a realizar una sub-busqueda en la tabla de pagos que cargue en memoria pero iniciando desde los pagos que potencialmente sumados den como resultado la cantidad deseada, es decir no recorro todos los registros, para eso agrego la sentencia where pag_total<=@Total-@pag_total_idx.

En el peor de los casos tengo que repetir esta sub-busqueda tantas veces como registros en la sub-busqueda se encuentren, sumando todos los registros encontrados entre si hasta hayar la combinacion correcta.

Bueno soy malo para explicar, pero me funciona de maravilla.

de una tabla de pagos de el mes de enero 2012, tengo 1766 registros, despues de ejecutar el sp con los parametros 35000,'01/01/2012' obtengo los siguientes resultados

pag_id    pag_total
198232    18000.00
198191    2000.00
197846    1956.50
197296    1937.00
196831    1823.50
196806    1812.00
197823    1718.50
198264    1653.50
197260    1557.00
197546    1500.00
198277    1031.00
197692    11.00

la suma es correcta, estoy buscando los pagos que me sumen 35,000.00 pesos para elaborar una factura por esta cantidad (por ejemplo) ya que el mes pasado falto elaborarle una factura a un cliente por este monto, como no hay un pago en este mes por esa cantidad, busco los pagos que tengo libres para poder generar esa factura.

Gracias por sus consejos!

[LuNaTk]

Un ultimo ajuste ya que en el caso de que no haya registros que coincidan con la busqueda no se borraba la tabla de resultados, arrojando registros que no cumplian.

Código SQL [-]
alter procedure sp_c_pago_reemplazo @Total numeric(19,2),@Fecha datetime
as
set nocount ON
declare @Pago table (n int identity(1,1),pag_id int,pag_total numeric(19,2))
declare @Resultado table (n int,pag_id int,pag_total numeric(19,2))
insert @pago
select 
pag_id,pag_total
from pago
where pag_total<=@Total
and year(pag_fecha)=year(@Fecha)
and month(pag_fecha)=month(@Fecha)
order by pag_total desc,pag_id desc
declare
@pag_id int,
@pag_total_idx numeric(19,2),
@pag_total numeric(19,2),
@pag_total_rep numeric(19,2),
@n int,
@l int,
@n_ int,
@l_ int,
@c int,
@r int,
@Suma numeric(19,2)
select @n=MIN(n),@l=MAX(n),@Suma=0 from @Pago
while (@n<=@l)
 begin
   -- Limpio tabla de resultados
   delete from @Resultado
   set @Suma=0
   -- Tomo el primer pago
   select @pag_total_idx=pag_total,@pag_id=pag_id from @pago where n=@n
   if @pag_total_idx<=@Total
     insert @Resultado select @n,@pag_id,@pag_total_idx
   -- Verifico si se encontro el total
   if @Total=@pag_total_idx
     Goto Final
   else
     begin
        select 
        @c=MAX(n),
        @r=0
        from @Pago 
        where pag_total<=@Total-@pag_total_idx
        --print 'Repeticiones '+cast(@c as varchar)
        while @r<=@c
          begin  -- Repito la busqueda para todas las combinaciones
            delete from @Resultado where n<>@n
            set @Suma=0
      select 
      @n_=MIN(n)+@r,
      @l_=@l
      from @Pago 
      where pag_total<=@Total-@pag_total_idx and n>@n         
      while @n_<=@l_
        begin
        select @pag_total=pag_total,@pag_id=pag_id from @pago where n=@n_
              --print 'Nivel '+cast(@pag_total as varchar)      
        if (@pag_total_idx+@Suma+@pag_total)<=(@Total)
          begin
          insert @Resultado select @n_,@pag_id,@pag_total 
          set @Suma=@Suma+@pag_total
          end
        if (@Suma+@pag_total_idx)=@Total  -- Verifico si se encontro el total
          Goto Final 
        set @n_=(select MIN(n) from @Pago where n>@n and n>@n_) 
        end
      set @r=@r+1
      end
     end
   set @n=(select MIN(n) from @pago where n>@n and pag_total<@pag_total_idx)
 end
 delete from @Resultado
 Final:
 select pag_id,pag_total from @Resultado order by n