Saldos Bancários (Running Total?)

[Gustavo_RJ 0]

Prezados, boa dia sou iniciante em Oracle e peguei recentemente um relatório que está me tirando o sono, já pesquisei bastante mas não encontro a solução correta, procurando encontrei este fórum, espero que possam me ajudar, eis a situação:

possuo duas tabelas uma com saldos bancários e outras com movimentações a receber, e preciso fazer um relatório projetado baseado nas datas das movimentações a receber, supondo que elas ocorram somando ao ultimo saldo na tabela de saldos bancários, algo como:

TAB_SALDOS

COD_BANCO DT_SALDO VL_SALDO

341 10/05/2014 1000.00

341 09/05/2014 1500.00

...

TAB_MOV

ID_TITULO DT_VENC VALOR

0001 10/05/2014 300.00

0182 13/05/2014 650.00

7625 13/05/2014 100.00

6254 14/05/2014 750.00

6500 15/05/2014 100.00

5422 15/05/2014 200.00

3322 15/05/2014 300.00

...

a situação seria, tomando como base a data de hoje (13/05/2014), o ultimo saldo bancário é 10/05/2014, 1000.00

então o resultado seria algo como:

TAB_TEMP_RESULTADO

DT_SALDO SALDO

13/05/2014 1750.00

14/05/2014 2500.00

15/05/2014 3100.00

...

13/05/2014 - 1750.00 --- saldo bancário de 1000.00 + 750.00 do dia

14/05/2014 - 2500.00 --- saldo anterior de 1750.00 + 750.00 do dia

15/05/2014 - 3100.00 --- saldo anterior de 2500.00 + 600.00 do dia

notem que os valores estão agrupados por data, e esqueci de dizer que se a data do vencimento for uma sexta-feira, eu preciso apresenta-la no relatório como a próxima segunda-feira, uma vez que o banco só disponibiliza o valor em dias uteis (essa parte eu consegui resolver)

Montando o quebra-cabeças, eu busco o ultimo saldo através de:

SELECT VL_SALDO 


   FROM Tab_Saldos 

   WHERE DT_SALDO =  ( SELECT MAX(DT_SALDO) as DATA_SALDO FROM Tab_Saldos 

                                  WHERE BANCO  = '104' ) and BANCO = '104'

Buscando os valores por vencimento e caso seja sexta apresentando como segunda:

SELECT "NOVO VENCIMENTO", SUM(SALDO) 


       FROM  ( 

       SELECT CASE TO_CHAR(TO_DATE(VENCREAL,'yyyymmdd'), 'D','nls_date_language=portuguese')

       WHEN  '6' THEN TO_DATE(VENCREAL,'YYYYMMDD') + 3

       WHEN  '7' THEN TO_DATE(VENCREAL,'YYYYMMDD') + 2

       WHEN  '1' THEN TO_DATE(VENCREAL,'YYYYMMDD') + 1


       ELSE  TO_DATE(VENCREAL,'YYYYMMDD')   END AS "NOVO VENCIMENTO",

       SALDO      



       FROM Tab_Mov



       WHERE VENCREAL BETWEEN '20140513' AND '20140515' SALDO > 0  )


       GROUP BY "NOVO VENCIMENTO"


       ORDER BY "NOVO VENCIMENTO"

Essa consulta me retorna os valores agrupados corretamente:

14/05/14 603558,99

15/05/14 873579,71

falta agora o saldo bancário como saldo inicial e os somatórios e estou parado nisso, pesquisei muito sobre Running Total mas não consigo implementa-lo na minha situação, principalmente por no caso o saldo inicial da soma estar em uma tabela diferente de onde o resto do calculo prossegue...

Alguém tem uma luz?

Desde já agradeço!

[Motta 645]

Veja se isto ajuda

 

Veja o Example 21-7 Cumulative Aggregate Function

[Gustavo_RJ 0]

Motta, obrigado por ajudar antes de mais nada.

 

bem, não sei se entendi errado mas no exemplo, é possível relacionar de maneira simples as tabelas envolvidas

 

 

SELECT c.cust_id, t.calendar_quarter_desc, TO_CHAR (SUM(amount_sold),

'9,999,999,999.99') AS Q_SALES, TO_CHAR(SUM(SUM(amount_sold))

OVER (PARTITION BY c.cust_id ORDER BY c.cust_id, t.calendar_quarter_desc

ROWS UNBOUNDED

PRECEDING), '9,999,999,999.99') AS CUM_SALES

FROM sales s, times t, customers c

WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id AND t.calendar_year=2000

AND c.cust_id IN (2595, 9646, 11111)

GROUP BY c.cust_id, t.calendar_quarter_desc

ORDER BY c.cust_id, t.calendar_quarter_desc;

 

No meu eu não tenho como relacionar os títulos com o saldo do banco porque como o relatório é futuro eu ainda não sei em que banco foi baixado aí não posso relaciona-lo a tabela de bancos e somar ao ultimo saldo que lá existe.

No caso eu terei que inferir a soma dos títulos nas datas e simular com o ultimo saldo de todos os bancos, assumindo que foram pagos em todos os bancos, para daí os gestores fazerem suas projeções (admitindo que o titulo foi recebido por este ou aquele banco)

Eu que estou viajando, entendendo errado para achar essa solução?

[Motta 645]

A sua dúvida não tem relação com o uso das analytic functions e sim de como montar a sua query.

 

Primeiro tente montar uma query analítica, tipo listar tudo o que se quer, segundo uma agregação simples do tipo SUM e em terceiro uma com linhas acumuladas.

[Gustavo_RJ 0]

Resolvi, de fato ao resolver o problema de montar a query analítica, consegui desenvolver o running total ..

 

Obrigado!

[Motta 645]

Comendo o mingau pelas beiradas fica fácil ... :)

[Gustavo_RJ 0]

Comendo o mingau pelas beiradas fica fácil ... :)

 

:joia:

[Gustavo_RJ 0]

Motta, colocarei aqui no mesmo tópico porque o assunto tem a ver... e se quisermos a DIFERENÇA entre as linhas:

 

Data Valor Diferença

20/05/2014 1000.00 1000.00

21/05/2014 50.00 950.00

22/05/2014 150.00 800.00

23/05/2014 250.00 550.00

 

Eu tentei com LAG e LEAD mas sinceramente não consegui:

 

SELECT Data, Valor AS "SALDO INICIAL", Valor - LAG (Valor) OVER (ORDER BY "Data") "Diferença"

 

tentei também

 

SELECT Data, Valor AS "SALDO INICIAL", LAG (Valor) OVER (ORDER BY "Data") - Valor "Diferença"

[Motta 645]

Putz, sei não , tentou dar uma volta somando (valor * -1) ?

[Gustavo_RJ 0]

Tentei sim Motta ...

[Motta 645]

http://docs.oracle.com/cd/A60725_05/html/comnls/us/ap/supp01.htm

 

https://community.oracle.com/thread/2232597?tstart=0

 

Veja estas URLs , nem li , só repassei ..

[Gustavo_RJ 0]

Pelo que li Motta, o link https://community.oracle.com/thread/2232597?tstart=0 tem a ver com o que preciso, mas dessa vez tá complicado "comer pelas beiradas" :D

 

a diferença pelo que entendi é que no exemplo deles eles trabalham com números negativos e eu quero subtrair independente do valor

 

qual termo você está usando para pesquisar? estou procurando algo como "Oracle running total subtraction"

[Motta 645]

Tente também balance (saldo) , negative procure no AskTom também

 

Não tivesse eu muito enrolada ia pensar uma solução pois um dia posso vir a precisar de algo assim.

 

Se achar posta aqui.por favor.

 

=======

Pense também numa function, pode ficar lento mas em tese resolveria

[Gustavo_RJ 0]

Vou procurar

 

inicialmente estou fugindo de functions etc.. só usarei se não achar mesmo.

 

Se achar a solução com certeza coloco aqui :yes:

 

 

Motta,

encontrei a seguinte consulta que realiza a soma tal qual funções analíticas porém sem o uso delas:

select e1.emp_no, e1.lastname, e1.salary,

(select sum(salary)

from employee e2

where e2.emp_no <= e1.emp_no )

AS cume_salary2

from employee e1 order by emp_no;

 

será que substituindo o sum(salary) pela diferença do valor na e2 e na e1 consigo?

pensei em utilizar o rownum ao invés da emp_no, algo como e1.rownum < = e1.rownum, estou tentando aqui mas estou confundindo alguma coisa.

Estou adaptando a Query que utilizei para fazer a soma e que está funcionando, tentando gerar a diferença:

 

 

--- QUERY CALCULO SALDOS BANCÁRIOS ( Retiradas)

--- INICIA RUNNING TOTAL

SELECT "NOVO VENCIMENTO", SALDO AS "SALDO INICIAL",

(SELECT E88.SALDO - E8.SALDO FROM SE8010 E88 WHERE E88.ROWNUM <= E8.ROWNUM) AS DIFERENCA

FROM

 

(

 

--- BUSCANDO SALDO BANCÁRIO (SOMA DE SALDO DE TODOS OS BANCOS)

SELECT TO_DATE(MAX(E8_DTSALAT),'YYYYMMDD') AS "NOVO VENCIMENTO",

SUM(E8_SALATUA) AS SALDO

FROM SE8010 E8 ,

(SELECT E8_BANCO, E8_AGENCIA, E8_CONTA, MAX(E8_DTSALAT) AS DIA

FROM SE8010 E8 WHERE E8.D_E_L_E_T_ <> '*'

AND E8_DTSALAT < TO_CHAR( (SELECT SYSDATE FROM DUAL), 'YYYYMMDD')

GROUP BY E8_BANCO, E8_AGENCIA, E8_CONTA) BANCOS_IN

WHERE E8.D_E_L_E_T_ <> '*'

AND E8.E8_DTSALAT = BANCOS_IN.DIA

AND E8.E8_BANCO = BANCOS_IN.E8_BANCO

AND E8.E8_AGENCIA = BANCOS_IN.E8_AGENCIA

AND E8.E8_CONTA = BANCOS_IN.E8_CONTA

--- Término BUSCANDO SALDO BANCÁRIO (SOMA DE SALDO DE TODOS OS BANCOS)

 

UNION

-----INICIO TÍTULOS A PAGAR POR DATA

SELECT "NOVO VENCIMENTO", SALDO FROM

(

SELECT "NOVO VENCIMENTO", SUM(E2_SALDO) AS SALDO

FROM (

SELECT CASE TO_CHAR(TO_DATE(E2_VENCREA,'yyyymmdd'), 'D','nls_date_language=portuguese')

WHEN '6' THEN TO_DATE(E2_VENCREA,'YYYYMMDD') + 0 ----Hoje não altero a data, mas a estrutura permite se quiser postergar titulo

WHEN '7' THEN TO_DATE(E2_VENCREA,'YYYYMMDD') + 0 ----Hoje não altero a data, mas a estrutura permite se quiser postergar titulo

WHEN '1' THEN TO_DATE(E2_VENCREA,'YYYYMMDD') + 0 ----Hoje não altero a data, mas a estrutura permite se quiser postergar titulo

 

ELSE TO_DATE(E2_VENCREA,'YYYYMMDD') END AS "NOVO VENCIMENTO",

E2_SALDO

 

FROM SE2010 SE2

 

WHERE E2_VENCREA BETWEEN TO_CHAR( (SELECT SYSDATE FROM DUAL), 'YYYYMMDD') AND '20140530'

AND SE2.d_e_l_e_t_ = ' '

AND E2_SALDO > 0

AND E2_TIPO <> 'PA'

)

 

GROUP BY "NOVO VENCIMENTO"

 

ORDER BY "NOVO VENCIMENTO"

)

------------ TERMINO BUSCA TÍTULOS A PAGAR NAS DATAS

 

) --- TERMINO DO RUNNING TOTAL

 

 

 

A query que busca o último saldo bancário com Union + Query dos Títulos nas Datas resulta em algo como

 

 

 

Novo Vencimento Saldo

27/05/14 18376,52

28/05/14 738,91

29/05/14 7018,07

30/05/14 817,55

[Gustavo_RJ 0]

Consegui :D

 

SELECT "NOVO VENCIMENTO", "SALDO INICIAL", SINAL, sum(SINAL) over (order by "NOVO VENCIMENTO") DIFERENÇA

FROM

(

SELECT "NOVO VENCIMENTO", SALDO AS "SALDO INICIAL",

SALDO * CASE when ROWNUM >1 then -1 else 1 end SINAL

FROM

(

--- BUSCANDO SALDO BANCARIO (SOMA DE SALDO DE TODOS OS BANCOS)

SELECT TO_DATE(MAX(E8_DTSALAT),'YYYYMMDD') AS "NOVO VENCIMENTO",

SUM(E8_SALATUA) AS SALDO

.....

Continua igual o resto