Foros Club Delphi

Foros Club Delphi (https://www.clubdelphi.com/foros/index.php)
-   Conexión con bases de datos (https://www.clubdelphi.com/foros/forumdisplay.php?f=2)
-   -   Calcular saldos con SQL (https://www.clubdelphi.com/foros/showthread.php?t=21372)

mazinger 15-05-2005 13:52:18

Calcular saldos con SQL
 
Tengo una aplicación, que, entre otras cosas lleva las cuentas de bancos. En la cuenta detalle del banco tengo los campos Debe, Haber y Saldo, entre otros como la fecha, etc.
El problema viene cuando quiero calcular el saldo para cada registro. La forma fácil es ir calculando uno a uno los saldos de cada registro. Eso supone una edición y su posterior post sobre cada registro, lo cual es bastante lento en cuanto sube el número de registro. La solución que se me ocurrió es hacerlo por SQL algo del tipo:

Código:

UPDATE Detalle SET Saldo = Haber - Debe
Esto funcionaría perfectamente si no fuera por que tengo que leer el saldo del registro anterior y luego sumarlo al actual.
No sé si eso se puede hacer en SQL

¿Alguna idea?

Gracias por adelantado

mazinger 16-05-2005 17:47:43

Básicamente el problema está en hacer referencia al registro anterior para obtener su saldo y así calcular el saldo del registro siguiente...

axelbb 16-05-2005 19:50:19

Y un trigger...?
 
Si tenés la posibilidad en tu motor, podés probar de hacer un trigger (after update). El trigger se dispara sólo, cuando cambiás el contenido de cada registro automáticamente hace un debe*haber y lo guarda en el saldo. Como es automático por cada modificación, sin hacer nada el motor solito te mantiene el campo actualizado. No sé si entendí bien el problema. Si no, tendrás que preguntarle a alguien que sepa algo del tema :D .

Saludos.

axelbb 16-05-2005 20:21:07

Uhhh, por no leer bien...
 
Sí, entendí mal. No es que en cada registro tenés un resumen de cada banco, sino que acumula un saldo de un mismo banco...
En primer lugar... ¿Te resulta indispensable hacer eso? En general el saldo debería surgir de un cálculo en el momento de la consulta. Si el temor es que sea muy lento cuando tengas muchos registros, de todos modos podés hacer un trigger que tome el saldo del último registro y sume tus valores de debe y haber.
¿Cómo sería? dejame ver que recuerdo haber hecho algo parecido alguna vez. Busco y te lo paso, si es que ahora sí entendí.:D

Epachsoft 16-05-2005 21:19:01

Los triggers son increiblemente comodos, pero altamente peligrosos, no son faciles de seguir en caso de "debug", y pierdes control absoluto de la accion, asi que cualquier update que se realice sobre la tabla, sin importar la ubicacion o el proposito se vera reflejado.

Yo no lo consideraria algo apropiado para este tipo de operaciones. Si estas usando MSSQL 2000, te recomendaria una funcion, que simplemente retorne el monto del mes anterior, para el registro actual.

Si tas usando otra db puedes usar puro sql, puedes crear una vista y agregarla en el From del update, esta vista mostraria todos los registros menos el ultimo, etc.

De cualquier manera PLSQL es mas completo que TSQL por lo que debe de haber un equivalente de funcion.

Muchas opciones, suerte con eso.

GO DELPHI!

mazinger 16-05-2005 22:22:16

Supongo que calcular el saldo al mostrar los registros es igual de complicado que hacerlo e irlo almacenando en un campo. En cualquier caso gracias.

Seria algo como esto:
Código:

        Debe        Haber        Saldo        Saldo anterior                Haber - Debe + Saldo anterior
                30        30          0                        30 - 0 + 0 = 30
          20                  10          30                        0 - 20 + 30 = 10
                25        35          10                        25 - 0 + 10 = 35
 
......

Traducido a SQL

UPDATE Cuenta SET Saldo = Haber - Debe + [SaldoAnt]

Donde Saldo anterior hace referencia al saldo del registro anterior, pero no sé como se hace eso.

roman 16-05-2005 23:35:49

Cita:

Empezado por mazinger
Supongo que calcular el saldo al mostrar los registros es igual de complicado que hacerlo e irlo almacenando en un campo. En cualquier caso gracias.

No veo por qué ha de ser complicado:

Código Delphi [-]
Query.SQL.Text := 'select haber, debe from cuenta';
Query.Open;

Saldo := 0;
while not Query.EoF do
begin
  {
    Aquí muestras o usas Query['haber'], Query['debe'], Saldo
  }
  
  // Y calculas el siguiente Saldo
  Saldo := Query['haber'] - Query['debe'] + Saldo;
end;

Y esto suponeindo que realmente quieres mostrar los saldos parciales porque si lo que quieres es obtener el saldo final entonces se obtiene de un sólo golpe:

Código SQL [-]
select sum(haber-debe) from cuenta

// Saludos

axelbb 17-05-2005 17:47:23

Si, está claro lo que necesitas hacer.

Respecto al comentario sobre triggers de Epachsoft, hay bastante de verdad si esa tabla se maneja de manera poco ortodoxa, ordenada y documentada. Aún así se definen los triggers para caso de updates o de inserts, o de borrados, bien pensados para cada caso, y se ejecutarán siempre, sin posibilidad de olvidarse de hacer algo como actualizar el saldo. Eso garantiza la integridad de los datos, porque la regla se ejecuta aunque te olvides que existía, o aunque quieras guardar un saldo incorrecto. Y calcular el saldo nuevo ante cada operación... creo que es una regla de integridad fundamental en tu caso (ni pensemos si quedara mal en algún sitio y siguieras calculando desde allí). Aclaremos además que un trigger puede desactivarse si se desea que en una operación particular no se ejecute. Las dificultades que plantea Epachsoft pueden ser un precio adecuado a cambio de otras ventajas, que tendrás que sopesar. Personalmente, cuando una regla para una tabla es irrevocable, como que el saldo debe ser congruente con los debes y haberes... ni lo pienso, esa regla es parte de la tabla y el trigger (bien diseñado) me la garantiza. Pero es mi criterio personal.:)

Respecto al código de Roman:
Query.SQL.Text := 'select haber, debe from cuenta';
está correcto, para trabajar en tu programa los datos, sólo que traes al cliente todos los registros de la tabla, y si es grande... pobre red (a menos que hablemos de tablas "de escritorio" y el BDE, en cuyo caso ni hablemos de triggers). Además, me parece que calcula un saldo mucho más rápidamente un motor en un servidor que un programa Delphi en un cliente recorriendo secuencialmente los registros de un Query, habría que probar.

Insisto que es mejor solución no guardar el saldo, a la 2da. sentencia de cálculo de Roman le agregaría un "where fecha<TuFecha1" (supongo que hay un campo para la fecha del registro, obvio), para que el servidor te retorne el cálculo de SALDO HASTA el momento que necesites el detalle, y luego sí el
'select haber, debe from cuenta where fecha>=TuFecha1 and fecha <=TuFecha2'
para mostrar, listar, operar con los datos en detalle, pero solamente los datos que te hagan falta estrictamente. Por ejemplo, movimientos de banco entre fecha1 y fecha2. Calculas el saldo hasta fecha1 (sin ese día) y traes los registros de movimientos para ese intervalo de fechas, y ahí sí, recalculas el saldo en tu programa con el método que te pasó Roman y lo listas.

Si optas por la solución de Epachsoft y seguir con el campo saldo en tu tabla (a veces no dan ganas de romper lo que ya se hizo, no? :D ), es buena su idea también del almacenado. Allí, pueden serte útiles las sentencias tipo "select last 1 saldo group by saldo" (a mí no me funciona en FireBird :confused: ) o "select first 1 saldo group by saldo desc" (esa sí anda :p ) que obtienen ambas el último saldo, o las que se te ocurran dentro del SQL soportado por tu servidor. Sólo que ante cada cambio (insert, update o delete) deberás tener un almacenado específico (calcular para el registro agregado, o recalcular para luego del borrado o modificado, si se cambió debe o haber) que no deberás olvidarte nunca de ejecutar (a menos, claro, que para hacer cada una de esas operaciones acudas a ese almacenado, directamente, pasándole parámetros con los datos (fecha, debe, haber...), para que los guarde y de paso recalcule el saldo como corresponde. Eso sí, si la base de datos se reparte en aplicaciones que hagan otros programadores, que lleguen a hacerte solamente el Insert y se olviden del almacenado...:eek: No pensemos en eso.

Soy medio nuevo en esto, pero es como lo plantearía (yo, sin campo saldo);)

Suerte y saludos.

roman 17-05-2005 18:11:02

Vamos a ver, no sé si voy a rebatir o a apoyar lo que dice axelbb. :confused:

Desde luego que la sentencia "select haber, debe from cuenta" muy posiblemente va complementada por una condición WHERE pero el punto es:

Si necesita mostrar los saldos parciales (en un rango de fecha u otra condición) entonces de cualquier forma tendrá que bajar todos esos registros al cliente por lo que el tiempo excedente del cálculo del saldo parcial es imperceptible (no estamos hablando de una operación compleja sino de una simple suma acumulativa).

Y si sólo requiere mostrar el saldo total, entonces no hay que molestarse siquiera en calcular los saldos parciales ya que estos se cancelan y sólo queda:

saldo total = haber total - debe total + saldo inicial

por lo que una sólo consulta basta.

No veo ninguna necesidad de triggers, bien o mal planteados. La congruencia entre el saldo y los haberes y deberes se dará al momento de acceder a los datos puesto que no veo ninguna necesidad de almacenar los saldos en la base.

// Saludos

axelbb 17-05-2005 19:57:56

Mazinger salió corriendo...
 
Parece que escribí muy confuso :D , me distraje en el debate.

A) De acuerdo con Roman en que no hace falta campo saldo. Pero NO es necesario bajar todos los registros al cliente:

select sum(debe-haber) as saldo from cuenta where fecha < Fecha1

se ejecuta EN EL SERVIDOR y retorna solamente un campito llamado saldo con una sola fila con el saldo a esa fecha. Como un saldo se arrastra día a día, debe calcularse (debe-haber) desde el primer registro. NO HAY un rango para esto, no hay saldos parciales, hay un límite inferior (fecha hasta). No veo que sea necesario bajar todo al cliente, ¿estoy en lo correcto o sigo sin entender tu enfoque?.

Luego:

select debe,haber,fecha,nota_explicativa... from cuenta where fecha >= Fecha1 and fecha <= Fecha2 (que puede ser Now, fin de año...)

que me trae el DETALLE de movimientos en el RANGO necesario (ahora sí).

Resultado, Banco desde el 15/5 hasta hoy, por ejemplo:

Fecha Detalle movimiento Débitos Créditos Saldo
==========================================
Saldo anterior 1000.00 <-Primer select
15/5 Depósito valores 20.00 1020.00
15/5 Cheque 11122 60.00 960.00
16/5 Cheque 11123 100.00 860.00
17/5 Nuevo Saldo 860.00
==========================================

Los datos en verde surgen del segundo select y los saldos de la operación que los haga el programa calculando, partiendo del saldo inicial.

B) A veces, hemos planteado mal el problema en un principio, hemos desarrollado mucho alrededor de ese error y preferimos mantenerlo así, o hay alguna razón más que fundamenta el uso del campo SALDO. Ahí, como alternativa, sostengo el uso de triggers para asegurar la integridad. El pide una sentencia para obtener saldo anterior, y le pasé una posibilidad (sinceramente, soy nuevo pero quiero ayudar como ustedes lo hicieron antes conmigo) con

select last 1 saldo from cuenta order by id_movimiento o
select first 1 saldo from cuenta order by id_movimiento desc

He visto motores donde hay funciones que devuelven un campo del último registro, estilo Last_row_value('SALDO') (el nombre lo acabo de inventar), pero no sé si todos tienen algo así.

Eso responde una fila, un campo, con el último saldo, siempre que identifique cada operación con su id, cosa que me parece muy conveniente (equivoqué algo en el anterior mensaje :rolleyes: con el group by).

Pero sí, 1000% de acuerdo con vos, Roman, que la A) es mejor alternativa, no hay posible incongruencia :cool: .

Saludos, y disculpen mi confusa redacción. Creo que al final no ayudé nada, una pregunta simple se hizo una larguísima discusión. Mazinger salió corriendo y encontró la solución anoche en su libro de SQL...:p

roman 17-05-2005 20:11:11

Cita:

Empezado por axelbb
A) De acuerdo con Roman en que no hace falta campo saldo. Pero NO es necesario bajar todos los registros al cliente:

select sum(debe-haber) as saldo from cuenta where fecha < Fecha1

se ejecuta EN EL SERVIDOR y retorna solamente un campito llamado saldo con una sola fila con el saldo a esa fecha. Como un saldo se arrastra día a día, debe calcularse (debe-haber) desde el primer registro. NO HAY un rango para esto, no hay saldos parciales, hay un límite inferior (fecha hasta). No veo que sea necesario bajar todo al cliente, ¿estoy en lo correcto o sigo sin entender tu enfoque?.

Exactamente, no es necesario bajar todos los registros a menos que desee mostrar al usuario los saldos parciales.

A lo mejor digo una tontería pero no sé si mazinger tiene claro que para mostrar el saldo final no es necesario ir calculando los saldos parciales.

// Saludos

axelbb 17-05-2005 20:27:00

Ah, ahora entendí. "La información está buscando desesperadamente mi cerebro"

mazinger 17-05-2005 22:14:01

Pues no, no salí corriendo :p

Está interesante la discusión, pero tengo que apuntar que sí que necesito calcular el saldo parcial para cada registro, al estilo de un extracto bancario, ya que lo utilizo para comparar en cada apunte si me coincide el saldo que yo tengo con el saldo que calcula el banco. Además el orden de introducción de los apuntes no es necesariamente ordenado, ya que a veces puedo insertar un registro antes que otros que ya tenían calculado su correspondiente saldo, con lo cual me tocar recalcular los posteriores.
Sigo dándole vueltas al tema, pero aún no he encontrado una solución satisfactoria.

Gracias por vuestro interes

axelbb 18-05-2005 15:05:54

Era lo que decía en apartado B), puede haber una situación que justifique ese campo.
Bien, si tenés un campo fecha, como supongo, al insertar el registro queda al último del grupo de registros de su misma fecha, ¿no?. Probaste:

1) Calcular saldo HASTA ese día con
select last 1 saldo where fecha < TuFecha order by fecha desc

(hacé un índice desc para facilitarle las cosas al motor)

Ahí tenés rápidamente el saldo anterior. Luego

2) Recalcular saldos posteriores:

UPDATE Cuenta SET Saldo = Haber - Debe + [SaldoAnt] where fecha >= TuFecha
Que actualiza todos los registros subsiguientes al cálculo del saldo. [SaldoAnt] es UNA SUMA FIJA que le agregas a la instrucción que surge del primer select. Lo probé y funciona.
Luego de cada insert o update o delete, ejecutás estas dos, y debería funcionar (al menos sí me anduvo en FireBird).

Suerte

axelbb 18-05-2005 15:08:29

Fé de erratas:

el primer select es

select FIRST 1 saldo where fecha < TuFecha order by fecha desc

o

select last 1 saldo where fecha < TuFecha order by fecha

axelbb 18-05-2005 15:15:29

Ya sé, tampoco anda. Por qué no le preguntás a Kinobi?

roman 18-05-2005 17:47:00

Insisto en lo innecesario de añadir un campo saldo.

Como ya ha aclarado mazinger, requiere mostrar los saldos parciales de manera que de cualquier forma los registros correspondientes deben bajarse, y de ahí a calcular el saldo es muy fácil y rápido. El mismo ha mencionado el hecho de que al no tener insertados los registros en orden (cronológico supongo) tiene que recalcular el saldo así que ¿para qué estarse liando con una actualización innecesaria de un campo calculado, es decir, justamente un campo cuyo valor se obtiene del cálculo de otros campos?

// Saludos

xorc 12-06-2012 23:48:50

Una sola consulta T-SQL
 
Hola compañeros, hace poco busqué la respuesta a la pregunta planteada en este tema pero no encontré respuestas muy satisfactorias para mi gusto, así que les planteo una solución en T-SQL que calcula el saldo en una sola consulta. Se que este tema se incluyó hace varios años, pero siempre habemos programadores nuevos que intentamos resolver viejos problemas cierto?.. espero que le sirva a alguien.
Comensaré diciendo que tengo dos tabla llamadas TComprobante y detalle_comprobante, que creo que se explican por si mismas, cada una tiene, obviamente, un campo identidad que, en el caso del detalle_comprobante, se aprovecha para calcular el saldo hasta el registro indicado.

Código SQL [-]
SELECT C.NROCOMP,D.FECHA,DT.DESCRIPCION, INGRESO,EGRESO, SALDO=( 
    SELECT sum(INGRESO-EGRESO)as saldo 
    FROM Detalle_Comprobante AS d1 
    INNER JOIN Tcomprobante as c1 ON d1.idcomp = c1.IdComp 
    WHERE d1.iddetcomp<=d.iddetcomp) 
FROM TCOMPROBANTE AS C 
INNER JOIN DETALLE_COMPROBANTE AS D ON D.IDCOMP=C.IDCOMP 
ORDER BY D.FECHA,d.iddetcomp
Despliega el siguiente resultado (dependiendo de lo que tenga en las tablas claro).

NROCOMP FECHA DESCRIPCION INGRESO EGRESO SALDO
099 02/01/2012 Aporte Regular.........100.00 00.00 100.00
109 03/01/2012 Retiro de Ap.Regular ...0.00 11.00 89.00
109 03/01/2012 Ptmo.Corriente .........21.00 00.00 110.00

Debe tomarse en cuenta de NROCOMP no es la llave de la tabla TComprobante ni de Detalle_comprobante, es solo un otro dato.
Resalto la capacidad de Transact de SQL Server 2000 de poder anidad una consulta dentro e otra permitiendo utilizar los valores de los registros para de alguna manera parametrizar la consulta anidada.
Si no se utiliza Order By d.iddetcomp el saldo se calcula bien de igual manera.
Se puede incluir parametrización entre fechas, cuentas específicas, etc, todo depende de lo que se necesite visualizar.
Un Saludo.


La franja horaria es GMT +2. Ahora son las 21:03:09.

Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Traducción al castellano por el equipo de moderadores del Club Delphi