Acumulado por Proveedor y Comprobante
Publicado por Antonio (2 intervenciones) el 11/04/2019 22:01:40
Esta es la misma tabla de un post anterior:
| proveedor | fecha | comp | impsale |
+-----------+------------+--------------+-----------+
| P012 | 2019-03-30 | 000000043946 | 7762.00 |
| P036 | 2019-03-01 | 000000043778 | 436.70 |
| P058 | 2019-03-16 | 000000043841 | 11100.00 |
| P058 | 2019-03-16 | 000000043841 | 4025.50 |
| P128 | 2019-03-13 | 000000043809 | 3500.00 |
| P128 | 2019-03-13 | 000000043809 | 8400.00 |
| P501 | 2019-03-08 | 000000043802 | 22140.80 |
| P501 | 2019-03-08 | 000000043802 | 3074.49 |
| P501 | 2019-03-08 | 000000043802 | 1362.72 |
| P501 | 2019-03-08 | 000000043802 | 447.99 |
| P501 | 2019-03-08 | 000000043802 | 613.22 |
| P501 | 2019-03-08 | 000000043802 | 955.90 |
| P501 | 2019-03-19 | 000000043865 | 1990.50 |
| P501 | 2019-03-19 | 000000043865 | 995.25 |
| P501 | 2019-03-19 | 000000043865 | 627.01 |
| P501 | 2019-03-19 | 000000043865 | 89.57 |
| P501 | 2019-03-19 | 000000043867 | 1990.50 |
| P501 | 2019-03-19 | 000000043867 | 995.25 |
| P501 | 2019-03-19 | 000000043867 | 627.01 |
| P501 | 2019-03-19 | 000000043867 | 89.57 |
El único detalle que cambia es que el nro. de comprobante se puede repetir para el proveedor en cuestión.
Con este query:
SELECT proveedor,fecha,comp,impsale,ROUND(IF(@proveed='' or @proveed=proveedor,@total:=@total+impsale,
@total:=0+impsale),2) AS Acum,@proveed:=proveedor FROM cajamov,(SELECT @proveed:='') AS tmp1,(select @total:=0)
AS tmp2 WHERE fecha BETWEEN '2019-03-01' AND '2019-03-31' AND proveedor <> '' ORDER BY proveedor ;
logré este resultado:
| proveedor | fecha | comp | impsale | Acum | @proveed:=proveedor |
+-----------+------------+--------------+-----------+-----------+---------------------+
| P012 | 2019-03-30 | 000000043946 | 7762.00 | 7762.00 | P012 |
| P036 | 2019-03-01 | 000000043778 | 436.70 | 436.70 | P036 |
| P058 | 2019-03-16 | 000000043841 | 11100.00 | 11100.00 | P058 |
| P058 | 2019-03-16 | 000000043841 | 4025.50 | 15125.50 | P058 |
| P109 | 2019-03-26 | 000000043903 | 4000.00 | 4000.00 | P109 |
| P112 | 2019-03-26 | 000000043899 | 2500.00 | 2500.00 | P112 |
| P128 | 2019-03-13 | 000000043809 | 3500.00 | 3500.00 | P128 |
| P128 | 2019-03-13 | 000000043809 | 8400.00 | 11900.00 | P128 |
| P248 | 2019-03-14 | 000000043820 | 1760.98 | 1760.98 | P248 |
| P501 | 2019-03-08 | 000000043802 | 22140.80 | 22140.80 | P501 |
| P501 | 2019-03-08 | 000000043802 | 3074.49 | 25215.29 | P501 |
| P501 | 2019-03-08 | 000000043802 | 1362.72 | 26578.01 | P501 |
| P501 | 2019-03-08 | 000000043802 | 447.99 | 27026.00 | P501 |
| P501 | 2019-03-08 | 000000043802 | 613.22 | 27639.22 | P501 |
| P501 | 2019-03-08 | 000000043802 | 955.90 | 28595.12 | P501 |
| P501 | 2019-03-19 | 000000043865 | 1990.50 | 30585.62 | P501 |
| P501 | 2019-03-19 | 000000043865 | 995.25 | 31580.87 | P501 |
| P501 | 2019-03-19 | 000000043865 | 627.01 | 32207.88 | P501 |
| P501 | 2019-03-19 | 000000043865 | 89.57 | 32297.45 | P501 |
| P501 | 2019-03-19 | 000000043867 | 1990.50 | 34287.95 | P501 |
| P501 | 2019-03-19 | 000000043867 | 995.25 | 35283.20 | P501 |
| P501 | 2019-03-19 | 000000043867 | 627.01 | 35910.21 | P501 |
| P501 | 2019-03-19 | 000000043867 | 89.57 | 35999.78 | P501 |
En la columna Acum se va sumando el parcial por proveedor y comprobante, pero necesitaba que el comprobante apareciese una sola vez con la suma de sus valores. Utilicé este query:
select proveedor,fecha,comp,impsale,sum(impsale) as SubToT,round(if(@proveed='' or @proveed=proveedor,
@total:=@total+sum(impsale),@total:=0+sum(impsale)),2) as Acum,@proveed:=proveedor from cajamov,
(select @proveed:='') as tmp1,(select @total:=0) as tmp2 where fecha between '2019-03-01' and '2019-03-31'
and proveedor <> '' group by proveedor,comp order by proveedor
y logré este resultado:
| proveedor | fecha | comp | impsale | SubToT | Acum | @proveed:=proveedor |
+-----------+------------+--------------+-----------+-----------+-----------+---------------------+
| P012 | 2019-03-30 | 000000043946 | 7762.00 | 7762.00 | 7762.00 | P012 |
| P036 | 2019-03-01 | 000000043778 | 436.70 | 436.70 | 436.70 | P036 |
| P058 | 2019-03-16 | 000000043841 | 11100.00 | 15125.50 | 15125.50 | P058 |
| P109 | 2019-03-26 | 000000043903 | 4000.00 | 4000.00 | 4000.00 | P109 |
| P112 | 2019-03-26 | 000000043899 | 2500.00 | 2500.00 | 2500.00 | P112 |
| P128 | 2019-03-13 | 000000043809 | 3500.00 | 11900.00 | 11900.00 | P128 |
| P248 | 2019-03-14 | 000000043820 | 1760.98 | 1760.98 | 1760.98 | P248 |
| P501 | 2019-03-08 | 000000043802 | 22140.80 | 28595.12 | 28595.12 | P501 |
| P501 | 2019-03-19 | 000000043865 | 1990.50 | 3702.33 | 3702.33 | P501 |
| P501 | 2019-03-19 | 000000043867 | 1990.50 | 3702.33 | 3702.33 | P501 |
Pero por ej. el proveedor P501 tiene 3 comprobantes: el 000000043802,000000043865 y 000000043867 y si
bien en la columna de Subtotal pone el total que corresponde a los comprobantes, en Acum repite lo mismo
cuando en realidad tendría que ser:
| proveedor| comp | SubToT | Acum
+----------+-------------+----------------------
| P501 | 000000043802| 28595.12 | 28595.12
| P501 | 000000043865| 3702.33 | 32297.45
| P501 | 000000043867| 3702.33 | 35999.78
O sea un Acumulado por comprobante y proveedor. ¿ Que le faltaría al query para lograr ese resultado ?
Antonio
Sistemas
Resipol
| proveedor | fecha | comp | impsale |
+-----------+------------+--------------+-----------+
| P012 | 2019-03-30 | 000000043946 | 7762.00 |
| P036 | 2019-03-01 | 000000043778 | 436.70 |
| P058 | 2019-03-16 | 000000043841 | 11100.00 |
| P058 | 2019-03-16 | 000000043841 | 4025.50 |
| P128 | 2019-03-13 | 000000043809 | 3500.00 |
| P128 | 2019-03-13 | 000000043809 | 8400.00 |
| P501 | 2019-03-08 | 000000043802 | 22140.80 |
| P501 | 2019-03-08 | 000000043802 | 3074.49 |
| P501 | 2019-03-08 | 000000043802 | 1362.72 |
| P501 | 2019-03-08 | 000000043802 | 447.99 |
| P501 | 2019-03-08 | 000000043802 | 613.22 |
| P501 | 2019-03-08 | 000000043802 | 955.90 |
| P501 | 2019-03-19 | 000000043865 | 1990.50 |
| P501 | 2019-03-19 | 000000043865 | 995.25 |
| P501 | 2019-03-19 | 000000043865 | 627.01 |
| P501 | 2019-03-19 | 000000043865 | 89.57 |
| P501 | 2019-03-19 | 000000043867 | 1990.50 |
| P501 | 2019-03-19 | 000000043867 | 995.25 |
| P501 | 2019-03-19 | 000000043867 | 627.01 |
| P501 | 2019-03-19 | 000000043867 | 89.57 |
El único detalle que cambia es que el nro. de comprobante se puede repetir para el proveedor en cuestión.
Con este query:
SELECT proveedor,fecha,comp,impsale,ROUND(IF(@proveed='' or @proveed=proveedor,@total:=@total+impsale,
@total:=0+impsale),2) AS Acum,@proveed:=proveedor FROM cajamov,(SELECT @proveed:='') AS tmp1,(select @total:=0)
AS tmp2 WHERE fecha BETWEEN '2019-03-01' AND '2019-03-31' AND proveedor <> '' ORDER BY proveedor ;
logré este resultado:
| proveedor | fecha | comp | impsale | Acum | @proveed:=proveedor |
+-----------+------------+--------------+-----------+-----------+---------------------+
| P012 | 2019-03-30 | 000000043946 | 7762.00 | 7762.00 | P012 |
| P036 | 2019-03-01 | 000000043778 | 436.70 | 436.70 | P036 |
| P058 | 2019-03-16 | 000000043841 | 11100.00 | 11100.00 | P058 |
| P058 | 2019-03-16 | 000000043841 | 4025.50 | 15125.50 | P058 |
| P109 | 2019-03-26 | 000000043903 | 4000.00 | 4000.00 | P109 |
| P112 | 2019-03-26 | 000000043899 | 2500.00 | 2500.00 | P112 |
| P128 | 2019-03-13 | 000000043809 | 3500.00 | 3500.00 | P128 |
| P128 | 2019-03-13 | 000000043809 | 8400.00 | 11900.00 | P128 |
| P248 | 2019-03-14 | 000000043820 | 1760.98 | 1760.98 | P248 |
| P501 | 2019-03-08 | 000000043802 | 22140.80 | 22140.80 | P501 |
| P501 | 2019-03-08 | 000000043802 | 3074.49 | 25215.29 | P501 |
| P501 | 2019-03-08 | 000000043802 | 1362.72 | 26578.01 | P501 |
| P501 | 2019-03-08 | 000000043802 | 447.99 | 27026.00 | P501 |
| P501 | 2019-03-08 | 000000043802 | 613.22 | 27639.22 | P501 |
| P501 | 2019-03-08 | 000000043802 | 955.90 | 28595.12 | P501 |
| P501 | 2019-03-19 | 000000043865 | 1990.50 | 30585.62 | P501 |
| P501 | 2019-03-19 | 000000043865 | 995.25 | 31580.87 | P501 |
| P501 | 2019-03-19 | 000000043865 | 627.01 | 32207.88 | P501 |
| P501 | 2019-03-19 | 000000043865 | 89.57 | 32297.45 | P501 |
| P501 | 2019-03-19 | 000000043867 | 1990.50 | 34287.95 | P501 |
| P501 | 2019-03-19 | 000000043867 | 995.25 | 35283.20 | P501 |
| P501 | 2019-03-19 | 000000043867 | 627.01 | 35910.21 | P501 |
| P501 | 2019-03-19 | 000000043867 | 89.57 | 35999.78 | P501 |
En la columna Acum se va sumando el parcial por proveedor y comprobante, pero necesitaba que el comprobante apareciese una sola vez con la suma de sus valores. Utilicé este query:
select proveedor,fecha,comp,impsale,sum(impsale) as SubToT,round(if(@proveed='' or @proveed=proveedor,
@total:=@total+sum(impsale),@total:=0+sum(impsale)),2) as Acum,@proveed:=proveedor from cajamov,
(select @proveed:='') as tmp1,(select @total:=0) as tmp2 where fecha between '2019-03-01' and '2019-03-31'
and proveedor <> '' group by proveedor,comp order by proveedor
y logré este resultado:
| proveedor | fecha | comp | impsale | SubToT | Acum | @proveed:=proveedor |
+-----------+------------+--------------+-----------+-----------+-----------+---------------------+
| P012 | 2019-03-30 | 000000043946 | 7762.00 | 7762.00 | 7762.00 | P012 |
| P036 | 2019-03-01 | 000000043778 | 436.70 | 436.70 | 436.70 | P036 |
| P058 | 2019-03-16 | 000000043841 | 11100.00 | 15125.50 | 15125.50 | P058 |
| P109 | 2019-03-26 | 000000043903 | 4000.00 | 4000.00 | 4000.00 | P109 |
| P112 | 2019-03-26 | 000000043899 | 2500.00 | 2500.00 | 2500.00 | P112 |
| P128 | 2019-03-13 | 000000043809 | 3500.00 | 11900.00 | 11900.00 | P128 |
| P248 | 2019-03-14 | 000000043820 | 1760.98 | 1760.98 | 1760.98 | P248 |
| P501 | 2019-03-08 | 000000043802 | 22140.80 | 28595.12 | 28595.12 | P501 |
| P501 | 2019-03-19 | 000000043865 | 1990.50 | 3702.33 | 3702.33 | P501 |
| P501 | 2019-03-19 | 000000043867 | 1990.50 | 3702.33 | 3702.33 | P501 |
Pero por ej. el proveedor P501 tiene 3 comprobantes: el 000000043802,000000043865 y 000000043867 y si
bien en la columna de Subtotal pone el total que corresponde a los comprobantes, en Acum repite lo mismo
cuando en realidad tendría que ser:
| proveedor| comp | SubToT | Acum
+----------+-------------+----------------------
| P501 | 000000043802| 28595.12 | 28595.12
| P501 | 000000043865| 3702.33 | 32297.45
| P501 | 000000043867| 3702.33 | 35999.78
O sea un Acumulado por comprobante y proveedor. ¿ Que le faltaría al query para lograr ese resultado ?
Antonio
Sistemas
Resipol
Valora esta pregunta
1