question

Ayman avatar image
Ayman asked

SQL query getting running total issue

The query is used to get balance of certain client SELECT t.Statement_ID, t.custid, t.[InvoiceID], t.S_Type as Type, t.Date, t.Debit, t.Credit, b.Balance FROM Statement as t CROSS apply (SELECT Balance = SUM(Debit) - SUM(Credit) FROM Statement as x WHERE x.CustID = t.CustID AND x.InvoiceID = t.InvoiceID and x.Statement_ID <= t.Statement_ID ) b WHERE t.CustID ='2' and date between '2015-01-01' and '2016-01-12' order by InvoiceID, Type desc, Date out put InvoiceID Type Date Debit Credit Balance 4 Sales Invoice 2015-06-09 520.00 0.00 520.00 4 Receipt Voucher 2016-01-04 0.00 520.00 0.00 6 Sales Invoice 2015-06-09 160.00 0.00 160.00 6 Receipt Voucher 2016-01-04 0.00 160.00 0.00 9 Sales Invoice 2015-06-09 850.00 0.00 850.00 9 Receipt Voucher 2016-01-04 0.00 850.00 0.00 13 Sales Invoice 2015-06-09 200.00 0.00 200.00 20 Sales Invoice 2015-07-11 1225.00 0.00 1225.00 176 Sales Invoice 2015-12-14 900.00 0.00 900.00 the issue is with with sales invoices # 13 20 176 its supposed to sum the debit of invoice and show in balance so right out put should be like this InvoiceID Type Date Debit Credit Balance 4 Sales Invoice 2015-06-09 520.00 0.00 520.00 4 Receipt Voucher 2016-01-04 0.00 520.00 0.00 6 Sales Invoice 2015-06-09 160.00 0.00 160.00 6 Receipt Voucher 2016-01-04 0.00 160.00 0.00 9 Sales Invoice 2015-06-09 850.00 0.00 850.00 9 Receipt Voucher 2016-01-04 0.00 850.00 0.00 13 Sales Invoice 2015-06-09 200.00 0.00 200.00 20 Sales Invoice 2015-07-11 1225.00 0.00 1425.00 176 Sales Invoice 2015-12-14 900.00 0.00 2325.00
sql-server-2008sqlt-sqlsql-server-2008-r2running-total
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
0 Likes 0 ·
Ayman avatar image Ayman commented ·
different case
0 Likes 0 ·
Gazz avatar image Gazz commented ·
Why did you use a different username?
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.