Good afternoon, I need to set up a query that calculates for me in a column the current balance by deducting the amount to be paid and adding the amount receivable, thus: balance_residual = (receive - pay + bank balance) I have three source tables, SE8010 table is bank balances, E8_SALATUA field is current balance, E8_DTSALAT field is date SE1010 table is receive accounts, field E1_VENCREA is receive due date, E1_SALDO is current balance SE2010 table is pay bills, E2_SALDO field is the pay day amount, E2_VENCREA pay date field pay.
I'm using the following query.
DECLARE @filial CHAR (2);
SET @filial = ('01');
SELECT E8_FILIAL = @filial, SUM(ISNULL(E8_SALATUA,0)) AS VALOR, E8_DTSALAT, SUM(E2_SALDO) AS PAGAR, SUM(E1_SALDO) AS RECEBER, (SUM(ISNULL(E8_SALATUA,0)) - SUM(ISNULL(E2_SALDO,0)) + SUM(ISNULL(E1_VALOR,0))) AS SALDO_ATUAL
FROM SE8010
LEFT JOIN SE1010 ON
E1_VENCREA = E8_DTSALAT AND
(CAST(E1_FILIAL AS CHAR (2))) = E8_FILIAL
LEFT JOIN SE2010 ON
E2_VENCREA = E8_DTSALAT AND
E2_VENCREA = E1_VENCREA AND (CAST(E2_FILIAL AS CHAR (2))) = E8_FILIAL
WHERE SE8010.D_E_L_E_T_ = ''
GROUP BY E8_FILIAL, E8_BANCO, E8_SALATUA, E8_DTSALAT, E8_CONTA, E8_AGENCIA
However the result is not effective for the dates provided for 7 days. So that I can create a column with a variable to save the balance (residual from the previous day - pay next day - get next day) how could I adjust this query so that it will provision me values for date greater than today? Can someone help me?