How do I make a Total Current?

Question

I have a Query made that takes all the invoices of a specific client and it appears all the payment information and current balance, as shown in the excel image: Img1

The idea is to add the missing values in the column balance Ex:

Missing Value is 0+518820=518820 Balance; 518820+2777750 = 796570 Balance And so on and so forth...

Here’s my scripts I created for the exercise. THE TABLES BEING WORKED ARE THOSE OF INVOICING(ft) And CORRENT ACCOUNT(cc)

SELECT ft.nmdoc, ft.fno, ft.fdata,  ISNULL(cc.deb,cc.cred) "Valor Doc",  ISNULL(cc.debf,cc.credf)Valor_Pago,
ISNULL((cc.deb-cc.debf),(cc.cred-cc.credf)) Valor_em_Falta, 
ISNULL(++(cc.deb-cc.debf),(cc.cred-cc.credf)) Saldo,
--ISNULL(SUM(@saldo ),0),
ft.pdata Dt_Vencimento 

FROM cc RIGHT JOIN  ft ON cc.ftstamp=ft.ftstamp 

where ft.nome ='JOAQUIM TESTE & FILHO,LDA'

GROUP BY ft.nmdoc, ft.fno, ft.fdata,ft.pdata ,cc.deb,cc.cred,cc.debf,cc.credf

order by ft.fdata 

Upshot:

Answer 1

Create a running total in SQL Server is relatively simple. Not having the whole context, I believe that the query below solves the problem:

SELECT      ft.nmdoc
        ,   ft.fno
        ,   ft.fdata
        ,   Valor_Doc       = ISNULL(cc.deb,cc.cred) 
        ,   Valor_Pago      = ISNULL(cc.debf,cc.credf)
        ,   Valor_em_Falta  = ISNULL((cc.deb-cc.debf),(cc.cred-cc.credf)) 
        ,   Saldo           = SUM(ISNULL((cc.deb-cc.debf),(cc.cred-cc.credf))) OVER (ORDER BY ft.fdata)
        ,   Dt_Vencimento   = ft.pdata 
FROM        cc 
RIGHT JOIN  ft ON cc.ftstamp = ft.ftstamp 
WHERE       ft.nome ='JOAQUIM TESTE & FILHO,LDA'
GROUP BY    ft.nmdoc
        ,   ft.fno
        ,   ft.fdata
        ,   ft.pdata 
        ,   cc.deb
        ,   cc.cred
        ,   cc.debf
        ,   cc.credf
ORDER BY    ft.fdata