0
Good afternoon, I need to assemble a query that calculates for me in a column the part of the current balance by deducting the amount to pay and adding the amount to receive, thus: residual balance = (receive - pay + balance) I have three source tables, table SE8010 is bank balances, field E8_SALATUA is current balance, field E8_DTSALAT is date table SE1010 is receivable accounts, field E1_VENCREA is due date receivable, E1_SALDO is current balance SE2010 table is pay bills, field E2_SALDO is the day paid amount, field E2_VENCREA due date 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 dates provided for 7 days . So that I can create a column with a variable to store the balance (residual of the previous day - pay next day - receive next day ) how could I adjust this wish so that it gives me values for date higher than today? Someone can help me?
They seem to me to be tables of Totvs Protheus , publish the structure of the tables , sgbd in question , has seen if Erp does not have a standard rel for this ?
– Motta
has a standard report yes, but we have to create one within Analytics - Gooddata.
– Allan Rafner
When I did a GD event, Totvs sold the fish that had these extractors... Think then of an extractor with a temporary table and a Procedure to populate , I think it will be easier using the E8 as a base , a job could populate the table and the extractor goes into it.
– Motta