1
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
In the article Optimization of window functions you find example of current total; it is named "accumulated sum". -> https://portosql.wordpress.com/2020/07/23/optimizacao-functions-window/
– José Diz
Thanks a lot @Josédiz It was very useful the link, I learned but things still. Thanks.
– Kevin Kafala