Click here to Skip to main content
15,667,422 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Transaction Date Transaction Description Debit Credit balance
--------------- ---------------------- ----- ----- --------
01/04/2012 Savings 0.00 2,000.00 2,000.00

01/04/2012 withdrawal 750.00 0.00 1,250.00

16/04/2012 Savings 0.00 8,400.00 9,650.00

20/04/2012 Savings 0.00 400.00 1,00.50

What I have tried:

SELECT cust_id, acc_num, acc_date, acc_details, (SUM(debit)*-1) + SUM(credit) AS RunningBalance
FROM cust_account_t
GROUP BY cust_id, acc_num, acc_date, acc_details
Updated 3-Oct-16 9:26am
[no name] 3-Oct-16 13:48pm    
It would seem that you have forgotten to ask a question.
Mathiudi 3-Oct-16 13:55pm    
I'm vert sorry. I'm under pressure here. Thanks for your reminders.

Can any body help me solve this?.
[no name] 3-Oct-16 13:58pm    
Probably a lot of people can solve whatever it is that you are asking. But, we need a question or at least a description of some sort of a problem first. We are not mind readers over here.
Mathiudi 3-Oct-16 14:05pm    
OK. What I want is, Let say on 1/4/2014 a customer made a deposit $100,000, If we are to print a statement for that customer, the statement should read below and so forth:

Date Details Debit Credit Balance
1/4/2014 Deposit 0.00 100,000.00 100,000.00
[no name] 3-Oct-16 14:25pm    
Okay.... seems to be a language barrier thing. The table definition and your query do not match, none of your comments are questions, there is exactly zero questions in your posting anywhere, no description of a problem, no examples of input data, no example of the output that you get when you run your query. I do not know what it is the you expect from people based on the conflicting and wrong information that you have given us to work with.

The problem is almost certainly that your GROUP BY clause contains too many columns - and that is normally because you don't understand what GROUP by does, and tried to get round the "Column 'name' is invalid in the select list because..." error by just adding columns to the group by in order to select them. Without a lot more info on exactly what you are trying to achieve we can't be at all specific, but this may help you to work it out on your own: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]
Share this answer
If I understand the question correctly, one simple way is to use running calculations introduced in SQL Server 2012. Consider the following example
CREATE TABLE #Transactions (
   TransactionDate        date,
   TransactionDescription varchar(100),
   Debit                  decimal,
   Credit                 decimal

('04/01/2012', 'Savings',    0.00,   2000.00),
('04/01/2012', 'withdrawal', 750.00, 0.00),
('04/16/2012', 'Savings',    0.00,   8400.00),
('04/20/2012', 'Savings',    0.00,   400.00);

SELECT t.TransactionDate, t.TransactionDescription, SUM(t.Credit-t.Debit) OVER (ORDER BY t.TransactionDate, t.TransactionDescription) AS Balance
FROM #Transactions t
ORDER BY t.TransactionDate, TransactionDescription;

The query gives the following result
TransactionDate TransactionDescription Balance
--------------- ---------------------- -------
2012-04-01      Savings                2000
2012-04-01      withdrawal             1250
2012-04-16      Savings                9650
2012-04-20      Savings                10050

You can find some alternative ways in Calculating simple running totals in SQL Server[^]
Share this answer
Mathiudi 4-Oct-16 7:12am    
Thank you for your concern and for your support and time. I tried your code it did not work. May be due to the sql 2008 express I;m using. Below is the code i came up with and it is working for me. Thank you all.

SELECT K.cust_id,K.acc_date,K.acc_details,K.debit,

,Sum( - B.debit) As Balance

from cust_account_t K
Inner Join
cust_account_t B
On K.acc_date >= B.acc_date
And B.cust_id <= K.cust_id
Group BY K.cust_id,K.acc_date,K.acc_details,K.debit,

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900