To obtain the balance of an account on a certain day, I use the following query:
SELECT (@total := @total+IFNULL(SUM(tcc.consumo),0.00)-IFNULL(SUM(tcc.pago),0.00)) saldo FROM transaccion_cta_cli tcc JOIN (SELECT @total := 0) acumulados WHERE tcc.no_cta=1000 AND tcc.fecha<='2018-08-25' ORDER BY tcc.fecha
Later I made a function (SF) that when passed as parameters: the date and not_cta, throws me the same balance as the previous query, that is, the query was incorporated into the function.
DELIMITER / CREATE FUNCTION saldos_2(fecha DATE,cuenta INT ) RETURNS DECIMAL(13,2) DETERMINISTIC BEGIN DECLARE saldo DECIMAL(13,2); SET saldo = ( SELECT (@total := @total+IFNULL(SUM(tcc.consumo),0.00)-IFNULL(SUM(tcc.pago),0.00)) saldo FROM transaccion_cta_cli tcc JOIN (SELECT @total := 0) acumulados WHERE tcc.no_cta=cuenta AND tcc.fecha<=fecha ORDER BY tcc.fecha ); RETURN saldo; END / DELIMITER ;
To know this balance of all my accounts registered in the previous table, my idea was to create a procedure (SP) that runs the accounts (not_cta) registered in the table ctabanc and saves them in a SP variable called: account , which I retrieve with the
FETCH cursor_ctabanc INTO cuenta; and the value of the account (no_cta) and the date passed as parameter to the procedure, later pass them as a parameter to the function mentioned above
SET sa = (SELECT saldos_2(fecha,cuenta)); and save the balance of the accounts and their no_cta in a temporary table,
CREATE TEMPORARY TABLE saldos_clientes(no_cta INT,saldo DECIMAL(13,2)); and at the end, return the sum of the balances. This procedure is the following:
DELIMITER / CREATE PROCEDURE saldos(IN fecha DATE, OUT saldos_client DECIMAL(13,2) ) BEGIN DECLARE cuenta INT; DECLARE sa DECIMAL(13,2); DECLARE fin INT DEFAULT 0; DECLARE cursor_ctabanc CURSOR FOR SELECT no_cta FROM ctabanc; DECLARE CONTINUE HANDLER FOR SQLSTATE '20000' SET fin = 1; CREATE TEMPORARY TABLE saldos_clientes(no_cta INT,saldo DECIMAL(13,2)); OPEN cursor_ctabanc; REPEAT FETCH cursor_ctabanc INTO cuenta; SET sa = (SELECT saldos_2(fecha,cuenta)); INSERT INTO saldos_clientes(no_cta,saldo) VALUES(cuenta,sa); UNTIL fin=1 END REPEAT; CLOSE cursor_ctabanc; SELECT SUM(saldo) sal INTO saldos_client FROM saldos_clientes; END / DELIMITER ;
Only by sending the previous procedure, I get the following error:
I hope someone can help me, I would appreciate it, because in a report they ask me, I need to calculate the sum of all the balances, of all the accounts at a certain date and my idea was to execute the procedure with another function, which when a date passes I will return the balance that is supposed to throw me the procedure.