CODEWITHSUNDEEP
oracle-databasecumulative-sum
kelsen
kelsen

Reputation: 29

Oracle sql cumulative result

D.VL_SALDOTOTAL is equal to 452,00. I Would like that SALDOTOTAL were cumulative as image below: 66,80 + 482,00 = 548,80 -> 107,00 + 548,80 = 655,80 but its summing SALDODIA + 482,00 every row. Is this possible with simple SQL or do I need a stored procedure?

select B.NR_CTAPES, A.DS_TITULAR, B.TP_MANUTENCAO as DS_MANUTENCAO, B.DT_MOVIM, B.TP_DOCUMENTO as DS_DOCUMENTO, B.TP_OPERACAO as DS_OPERACAO, B.NR_NF, B.VL_LANCTO, B.CD_HISTORICO as DS_HISTORICO, (SUM (DECODE( B.TP_OPERACAO,'C', DECODE( 
B.TP_DOCUMENTO, '3', B.VL_LANCTO, 0),0)) - SUM (DECODE( B.TP_OPERACAO,'D', DECODE( 
B.TP_DOCUMENTO, '3', B.VL_LANCTO, 0),0)) - SUM (DECODE( B.TP_OPERACAO,'D', DECODE( B.TP_DOCUMENTO, '9', B.VL_LANCTO, 0),0))) as VL_SALDODIA,  COALESCE( D.VL_SALDOTOTAL + (SUM (DECODE( B.TP_OPERACAO,'C', DECODE( B.TP_DOCUMENTO, '3', B.VL_LANCTO, 
0),0)) - SUM (DECODE( B.TP_OPERACAO,'D', DECODE( 
B.TP_DOCUMENTO, '3', B.VL_LANCTO, 0),0)) - SUM (DECODE( B.TP_OPERACAO,'D', DECODE( B.TP_DOCUMENTO, '9', B.VL_LANCTO, 0),0))),0) as VL_SALDOTOTAL
FROM VR_FCC_CTAPES A
LEFT OUTER JOIN VR_FCC_MOVLIQ B ON
B.NR_CTAPES = A.NR_CTAPES 
AND B.TP_MANUTENCAO = A.TP_MANUTENCAO 
AND B.CD_EMPRESA = A.CD_EMPRESA
LEFT OUTER JOIN (
    select (SUM (DECODE( C.TP_OPERACAO,'C', DECODE( C.TP_DOCUMENTO, '3', C.VL_LANCTO, 0),0)) - SUM (DECODE( C.TP_OPERACAO,'D', DECODE( C.TP_DOCUMENTO, '3', C.VL_LANCTO, 0),0)) - SUM (DECODE( C.TP_OPERACAO,'D', DECODE( C.TP_DOCUMENTO, '9', 
C.VL_LANCTO, 0),0))) as 
VL_SALDOTOTAL, C.NR_CTAPES, C.TP_DOCUMENTO, C.TP_MANUTENCAO, C.CD_EMPRESA, C.DT_MOVIM, C.CD_HISTORICO 
FROM VR_FCC_MOVLIQ C 
WHERE C.IN_ESTORNO ='F'
GROUP BY C.NR_CTAPES, C.TP_DOCUMENTO, C.TP_MANUTENCAO, C.CD_EMPRESA, C.DT_MOVIM, C.CD_HISTORICO
) D ON
D.NR_CTAPES = B.NR_CTAPES
AND D.TP_DOCUMENTO = B.TP_DOCUMENTO
AND D.TP_MANUTENCAO = B.TP_MANUTENCAO
AND D.CD_EMPRESA = B.CD_EMPRESA
AND D.DT_MOVIM = TO_DATE(@DT_INICIAL) - 1
AND D.CD_HISTORICO = B.CD_HISTORICO 
WHERE
B.TP_MANUTENCAO IN ('4','6') 
AND B.TP_DOCUMENTO IN ('3','9') 
AND B.IN_ESTORNO ='F' 
AND B.CD_HISTORICO NOT IN ('77','835','836','840','75','857','837','830','855','856','833','832','838','1074','1073') 
AND B.CD_EMPRESA IN ('1','2')
AND B.DT_MOVIM between @DT_INICIAL and @DT_FINAL 
GROUP BY B.NR_CTAPES, A.DS_TITULAR, B.TP_MANUTENCAO, B.DT_MOVIM, B.TP_DOCUMENTO, B.TP_OPERACAO, B.NR_NF, B.VL_LANCTO, B.CD_HISTORICO, D.VL_SALDOTOTAL 
ORDER BY B.DT_MOVIM, B.TP_OPERACAO

result

Upvotes: 0

Views: 214

Answers (1)

Wistful Thinker
Wistful Thinker

Reputation: 26

This is a bit of a thick read, but I think what you want is the 

sum(sum_column) over ([PARTITION BY group_column(s)] [order by order_column(s)]).

I think you should stop trying to cumulative total inside your existing query, and then enclose it in parentheses as a subquery. Select the columns you need from the gigantic subquery and add the below column next to your column that you want o keep a running total of:

sum(COLUMN_OF_VALUES_THAT_NEED_TO_BE_CUMULATIVELY_TOTALED) over
  (order by DT_MOVIM, TP_OPERACAO) as VL_SALDOTOTAL

I think that, even if this particular expression doesn't work, this is the function you are looking for. Research Oracle analytical queries for more help. Best of luck to you. -Tom

Upvotes: 1

Related Questions