Hacer columna saldo acumulado SQL Firebird 3

[amadis]

Buenas Compañeros disculpen is revivo un viejo Hilo.

Pero venía usando una sentencia como la que mencionaban arriba



Select c1.id, c1.fecha, c1.cliente, c1.DEBE, C1.HABER,Sum(C2.DEBE-C2.HABER)as SALDO
from CTACTE C1
inner join CTACTE C2
on (C1.id >= C2.id)
where (c1.cliente = :cli and c2.cliente = :cli )and c1.fecha >= :f
Group By c1.fecha,c1.id, c1.cliente, c1.DEBE, C1.HABER
Order by c1.id asc


Cuyo resultado es correcto: por ej


ID FECHA CLI DEBE HABER SALDO
80 2019-08-27 11 16037 0 16037
81 2019-08-27 11 16552 0 32589
82 2019-08-27 11 2800 0 35389
131 2019-08-29 11 1610 0 36999
261 2019-09-03 11 2170 0 39169
273 2019-09-14 11 0 169 39000
274 2019-09-01 11 0 150 38850
275 2019-09-12 11 0 100 38750

Pero me surge la necesidad de ordenar los registros por fecha y no por ID.
Perfecto, cambir el Order pero la suma de saldo no se calcula bien ya que si se agregan registros de fechas anteriores, el ID será mayor a los ID de la fechas anteriores y computará mal los saldos.

Aquí resultado de la misma consulta cn ORDER BY FECHA

ID FECHA CLI DEBE HABER SALDO
80 2019-08-27 11 16037 0 16037
81 2019-08-27 11 16552 0 32589
82 2019-08-27 11 2800 0 35389
131 2019-08-29 11 1610 0 36999
274 2019-09-01 11 0 150 38850
261 2019-09-03 11 2170 0 39169
275 2019-09-12 11 0 100 38750
273 2019-09-14 11 0 169 39000


En la clausula ON he probado poniendo on ((c1.fecha <= c2.fecha)and (C1.id >= C2.id))
Pero el resultado es erroneo igual al anterior.

Gracias por su tiempo

[mamcx]

Este tipo de problemas se resuelve mucho mas facilmente con los CTEs:

https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

(SQL ha avanzado desde 1992!)

[amadis]

en el ejemplo Creating a simple common table expression lo que propone es muy similar a la sintaxis que he usado y se ha expuesto aquí arriba.

Y en mi caso estoy usando FIREBIRD 3

[Casimiro Notevi]

No he entendido bien, dices que quieres ordenar por fecha, pues cambias c1.id por c1.fecha:
Select c1.id, c1.fecha, c1.cliente, c1.DEBE, C1.HABER,Sum(C2.DEBE-C2.HABER)as SALDO
from CTACTE C1
inner join CTACTE C2
on (C1.id >= C2.id)
where (c1.cliente = :cli and c2.cliente = :cli )and c1.fecha >= :f
Group By c1.fecha,c1.id, c1.cliente, c1.DEBE, C1.HABER
Order by c1.fecha

[amadis]

Casimiro, lo que dices es correcto, y eso comenté que hice, pero si observas los resultados de los saldos acumulados no coinciden.
Fijate el ejemplo en números que puse en el primer post.

El problema con las fechas se da si hay registros con misma fecha, y con registros que soy agregados hoy, pero registrados con fecha vieja.

[Casimiro Notevi]

Es que los saldos tienen que ser diferentes si cambias el orden, porque van registros antes o después.
Es el "problema" con los bancos, que ves que te va a faltar saldo para pagar algún recibo, haces un ingreso y cuando miras el extracto resulta que te han cobrado el recibo antes que el apunte de tu ingreso, porque lo han ordenado por "fecha valor" en lugar de "fecha real", por poner un ejemplo similar.

[amadis]

No he entendido bien, dices que quieres ordenar por fecha, pues cambias c1.id por c1.fecha:
Select c1.id, c1.fecha, c1.cliente, c1.DEBE, C1.HABER,Sum(C2.DEBE-C2.HABER)as SALDO
from CTACTE C1
inner join CTACTE C2
on (C1.id >= C2.id)
where (c1.cliente = :cli and c2.cliente = :cli )and c1.fecha >= :f
Group By c1.fecha,c1.id, c1.cliente, c1.DEBE, C1.HABER
Order by c1.fecha


Aqui pego un ejemplo con numeros mas chicos, de lo que da esta sentencia. EL SALDO FINAL DEBE SER 480


ID FECHA CLI DEBE HABER SALDO
80 2019-08-27 11 120 0 120
81 2019-08-27 11 50 0 170
82 2019-08-27 11 250 0 420
131 2019-08-29 11 300 0 720
274 2019-09-01 11 0 150 580
261 2019-09-03 11 30 0 750
275 2019-09-12 11 0 100 480
273 2019-09-14 11 0 20 730


En cambio los saldos son correctos si se ordena por ID, pero se agregaron fechas anteriores con ID posteriores no queda ordenado bien por fecha y complica la visión

Así queda orden por ID, los saldos quedan bien ordenados pero no las fechas y los movimientos


ID FECHA CLI DEBE HABER SALDO

80 2019-08-27 11 120 0 120
81 2019-08-27 11 50 0 170
82 2019-08-27 11 250 0 420
131 2019-08-29 11 300 0 720
261 2019-09-03 11 30 0 750
273 2019-09-14 11 0 20 730
274 2019-09-01 11 0 150 580
275 2019-09-12 11 0 100 480

[mamcx]

Y en mi caso estoy usando FIREBIRD 3

Firebird lo usa hace rato (nota la fecha del post):
https://firebird21.wordpress.com/2013/11/17/usando-cte-common-table-expression/

SQL ha avanzado desde 1992!

P.D: Y reitero, con CTE esto se resuelve muy facil y cubre mas de lo que pides. Pegale una estudiada... CTE es lo maximo para hacer reportes y estadisticas!

[amadis]

Que tal queridos compañeros.

Abro este post buscando ayuda con un tema que se ha mencionado en un par de ocasiones, que es como crear una columna que acumule saldos de cuenta.

Con la siguiente sentencia creo un extracto de cuenta, donde se visualizan, las fechas del movimiento, y el importe que acredita o debita.
Y la idea es que se vaya calculando el saldo de la cuenta parcial en cada registro.

Select c1.id, c1.fecha, c1.cliente, c1.DEBE, C1.HABER,Sum(C2.DEBE-C2.HABER)as SALDO
from CTACTE C1
inner join CTACTE C2
on (C1.id >= C2.id)
where (c1.cliente = :cli and c2.cliente = :cli )and c1.fecha >= :f
Group By c1.fecha,c1.id, c1.cliente, c1.DEBE, C1.HABER
Order by c1.fecha

La sentencia anterior lo consigue perfectamente, pero aparece el de que si ORDENO POR FECHA, y si se cargan fechas anteriores luego de la fecha de hoy por ej. Los registros se ordenan por ID y el saldo se calcula por el ID mientras que los registros de muestran por fecha y no conciden.


Aqui pego un ejemplo con numeros de lo que da esta sentencia. EL SALDO FINAL DEBE SER 480


ID FECHA CLI DEBE HABER SALDO
80 2019-08-27 11 120 0 120
81 2019-08-27 11 50 0 170
82 2019-08-27 11 250 0 420
131 2019-08-29 11 300 0 720
274 2019-09-01 11 0 150 580
261 2019-09-03 11 30 0 750
275 2019-09-12 11 0 100 480
273 2019-09-14 11 0 20 730


En cambio los saldos son correctos si se ordena por ID, pero se agregaron fechas anteriores con ID posteriores no queda ordenado bien por fecha y complica la visión

Así queda orden por ID, los saldos quedan bien ordenados pero no las fechas y los movimientos



ID FECHA CLI DEBE HABER SALDO

80 2019-08-27 11 120 0 120
81 2019-08-27 11 50 0 170
82 2019-08-27 11 250 0 420
131 2019-08-29 11 300 0 720
261 2019-09-03 11 30 0 750
273 2019-09-14 11 0 20 730
274 2019-09-01 11 0 150 580
275 2019-09-12 11 0 100 480


El compañero había mencionado que esto se puede resolver con CTE (https://firebird21.wordpress.com/201...le-expression/

Pero no he logrado que funcione.


Gracias a todos por su tiempo

[mamcx]

Podrias dar los datos inciales?

[Casimiro Notevi]

O no se entiende lo que preguntas, o simplemente no es posible hacer lo que quieres.
Un saldo acumulado se hace en un orden determinado, ese orden puede ser la fecha/hora de apunte, o un número de factura, o algún otro dato dependiendo del caso.

Vamos a suponer que es una entrada de ingresos y gastos, que se realiza por fecha y hora del apunte, o sea, compras algo y lo registras, ahora vendes y lo registras, haces un pago y lo registras, etc.

Fecha y hora Concepto Importe Saldo
2019.10.17 09:01:22 Me tocó la lotería 100 100
2019.10.17 09:05:35 Pago de la luz 50 50
2019.10.17 09:15:27 Cobro de fra.33 20 70
2019.10.17 09:23:52 Pago cafe 2 68

Bien, el saldo acumulado debe ir por orden de fecha hora.
No puedes pretender ordenar, por ejemplo, por el concepto, y que el saldo sea correcto. Poder, puedes, pero no es un saldo correcto.
En tu software debe existir un dato que es el que se utilice para esos saldos, debes usar ese.

Fecha y hora Concepto Importe Saldo ¡¡¡ Mal, obviamente !!!
2019.10.17 09:15:27 Cobro de fra.33 20 70
2019.10.17 09:01:22 Me tocó la lotería 100 100
2019.10.17 09:23:52 Pago cafe 2 98
2019.10.17 09:05:35 Pago de la luz 50 48

Si quieres que se recalcule según el orden elegido, no guardarás los saldos, sino que los calcularás cada vez que los presente ordenados por el campo que quieras.

Fecha y hora Concepto Importe Saldo
2019.10.17 09:15:27 Cobro de fra.33 20 20
2019.10.17 09:01:22 Me tocó la lotería 100 120
2019.10.17 09:23:52 Pago cafe 2 118
2019.10.17 09:05:35 Pago de la luz 50 68

[amadis]

Buenos Días Casimiro.

Trataré de contarles mejor.

Se trata de una tabla con movimientos de cuenta de clientes, donde hay un ID unico, y luego IDCLIENTE, FECHA, DEBITO Y CREDITO. El saldo lo calculaba desde el Query con Sum(C2.DEBE-C2.HABER)as SALDO.

Esto estaba funcionando perfecto ordenado por ID que es el campo único incremental que tengo. Pero ahora resulta que necesito añadir movimientos a la tabla con fechas anteriores a las ya existentes, y entonces el ID ultimo agregado irá al final. Por lo que necesito ordenar por Fecha (solo fecha no tengo horas y si las tuviera no me servirían) Porque por ej Hoy, se puede registrar un movimiento de hace 2 días.

Intenté cambie el order by a FECHA, pero ordena los registros por fecha, mientras que el saldo lo calcula por ID.

Necesito ver como calcular los saldos por el orden de fecha.

MAMCX me pedía datos iniciales. Simplemente pretendo ordenar los siguientes datos (esto


ID FECHA IDCLI DEBE HABER SALDO

80 2019-08-27 11 120 0 120
81 2019-08-27 11 50 0 170
82 2019-08-27 11 250 0 420
131 2019-08-29 11 300 0 720
261 2019-09-03 11 30 0 750
273 2019-09-14 11 0 20 730
274 2019-09-01 11 0 150 580
275 2019-09-12 11 0 100 480


Esos datos son el resultado de la sentencia
Select c1.id, c1.fecha, c1.cliente, c1.DEBE, C1.HABER,Sum(C2.DEBE-C2.HABER)as SALDO
from CTACTE C1
inner join CTACTE C2
on (C1.id >= C2.id)
where (c1.cliente = :cli and c2.cliente = :cli )and c1.fecha >= :f
Group By c1.fecha,c1.id, c1.cliente, c1.DEBE, C1.HABER
Order by c1.id

Si cambio el on (C1.id >= C2.id) por FECHA en lugar de ID, en los 3 registros que tiene la misma fecha me pone como saldo en cada registro la suma de los 3 registros.

[Casimiro Notevi]

No puedes agrupar por fecha y cliente porque en una misma fecha habrá registros de distintos clientes.

[amadis]

Lo hago así porque en la tabla tengo todos los clientes, pero este extracto es individual por cliente, entonces en el parametro :cli le paso por ej "11" para obtener los movimientos de cuenta del cliente ese

[amadis]

desde mi ignorancia, creo que la solución está en la primera parte donde calculo el saldo. Ver la posibilidad de indicar que se calcule por fecha

[Casimiro Notevi]

Entonces suma el debe y el haber, si quieres agrupar por ellos, en caso contrario saldrá un registro por cada cifra distinta.

[amadis]

Entonces suma el debe y el haber, si quieres agrupar por ellos, en caso contrario saldrá un registro por cada cifra distinta.

Creo que eso es lo que estoy haciendo con esto

Select c1.id, c1.fecha, c1.cliente, c1.DEBE, C1.HABER,Sum(C2.DEBE-C2.HABER)as SALDO
from CTACTE C1

[fjcg02]

Hola,

utiliza una store procedure. Creo que es la solución a tu problema.

No recuerdo bien la sintaxis, pero hay abundante información.
Saludos

CREATE OR ALTER MI_PROCEDURE ( variables de entrada...)
returns ( tu lista de campos incluyendo el saldo)
variables para acumular y controlar el flujo

FOR select bla bla bla
into :v1, :v2, :v3
ordenado por lo que te de la gana
do
si se cumple la condicion sumo, si no reseteo acumulado
hago el sumatorio de lo que quiero en base a la condicion que quiero...


suspend
end

[mamcx]

Si entiendo bien, esta consulta lo resuelve:


SELECT
*,
SUM(debe - haber) OVER (
PARTITION BY cli --quitar si no debe calcular saldo por cliente, sino global
ORDER BY id
) AS saldo_calculado
FROM mytable
ORDER BY id, fecha


Una explicacion de las funciones windows/cte (en postgresql que normalmente es lo que tiene mejor documentacion):

http://www.postgresqltutorial.com/postgresql-window-function/


id | fecha | cli | debe | haber | saldo | saldo_calculado
-----+------------+-----+------+-------+-------+-----------------
80 | 2019-08-27 | 11 | 120 | 0 | 120 | 120
81 | 2019-08-27 | 11 | 50 | 0 | 170 | 170
82 | 2019-08-27 | 11 | 250 | 0 | 420 | 420
131 | 2019-08-29 | 11 | 300 | 0 | 720 | 720
261 | 2019-09-03 | 11 | 30 | 0 | 750 | 750
273 | 2019-09-14 | 11 | 0 | 20 | 730 | 730
274 | 2019-09-01 | 11 | 0 | 150 | 580 | 580
275 | 2019-09-12 | 11 | 0 | 100 | 480 | 480

[amadis]

Ahora estoy probando modificar eso.

tuve que poner mytable.* en lugar de * solo al inicio y ahí larga algo, ahora veré que puedo hacer con eso

[amadis]

Hola colegas, disculpen que tuve que ausentarme unos días.

El aporte del colega Mamcx responde lo que preguntaba y necesitaba a la perfección.

Ahora unas consultas más para comprender, como funciona ésto.

Lo que veo es una gran simplificación de la consulta original que había planteado sin resultados

¿esto es CTE?

¿que es lo que hacen Over y Partition?

Gracias a todos y espero que esto ayude a muchos más

[mamcx]

Hay buen material de esto. Mi primera exposición a lo nuevo en SQL fue:

https://modern-sql.com/video

CTE es un poco mas dificil de entender, pero las funciones windows son mas simples.

SQL funciona maso asi:


for current in query.next() do
//tengo solo una fila. No se que datos habian antes o despues
print current


Con windows ( un "window" aqui se entienden como una "vista a la ejecucion"), es como si pasara esto:

old = null;
next = null
for current, next in query.next(); query.next() do
print old, current, next //la fila anterior, actual y siguiente
//Ahora puedo usar acumulados en base a lo anterior!
old = current

[amadis]

Muchas gracias por el material y el link del video. Comenzaré a estudiar modern-sql
Porque una vez comprendido ahora tiempo, dolores de cabeza y seguramente mejora la performance.