Accounting Ledger balance

Question

1.00/5 (1 vote)

See more:

Hi
I have a transaction table that contains

LedgerName	DebitOrCredit	DebitAmount	CreditAmount	VoucherNo
Cash A/c	    Dr	          3000.00       	0.00	   1
Cash A/c	    Dr	          3000.00               0.00	   2
Axis Bank A/c       Cr             0.00         	3000.00	   2 
Cash A/c	    Dr	          700.00	        0.00	   6
 Orders A/c	    Cr	           0.00	               700.00	   6
Cash A/c	    Dr	          700.00	        0.00	  18
 Orders A/c	    Cr	           0.00	               700.00	  18
Cash A/c	    Dr	          700.00	         0.00	  54
Orders A/c	    Cr	           0.00	               700.00	  54
Cash A/c	    Dr            700.00	        0.00	  7
Orders A/c	    Cr	           0.00	               700.00	  7

If I query for Cash A/c, my result would be

Axis Bank A/c       Cr              3000.00
 Orders A/c	    Cr               700.00
 Orders A/c	    Cr               700.00
 Orders A/c	    Cr               700.00
 Orders A/c	    Cr               700.00

Please provide me the sql query.

Posted 7-Dec-12 19:43pm

Rajaram24101984

Updated 7-Dec-12 19:50pm

Mehdi Gholam

v2

Add a Solution

Comments

[no name] 8-Dec-12 1:52am

have you tried anything so far ???

Rajaram24101984 8-Dec-12 2:45am

yes i have tried... but can not get the exact result

Rajaram24101984 8-Dec-12 3:34am

Krunal did you tried on your side

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

__TR__ · Answer 1 · 2012-12-07T20:45:00

Solution 2

Try

SQL

SELECT LedgerName, DebitOrCredit, CreditAmount FROM [YourTable]
WHERE VoucherNo IN
(
	SELECT VoucherNo FROM [YourTable]
	WHERE LedgerName = 'Cash A/c'
)
AND DebitOrCredit = 'Cr'

Posted 7-Dec-12 20:45pm

__TR__

Updated 7-Dec-12 21:13pm

v2

Comments

Rajaram24101984 8-Dec-12 2:59am

No, if i query for cash A/c, query will fetch the data that contains Cash A/c in each voucher that may either credit or debit of transaction. Vice versa, If I Query for Axis Bank, It will show only Cash A/c 3000.00

__TR__ 8-Dec-12 3:13am

Check the updated solution.

Rajaram24101984 8-Dec-12 3:25am

Hmm... It shows only credit types. Simply If i query for Axis bank A/c which is Credit type, result will be debit type items will show with Axis bank amount corresponding to voucher no. And If I query for Cash A/c Debit type, result will be credit type items will show with Cash A/c corresponding to Voucher no.

__TR__ 8-Dec-12 3:32am

I would suggest you to update your question with what you have tried so far and what do you exactly need. May be someone else will be able to provide a better solution, if you provide more details.

Rajaram24101984 8-Dec-12 3:34am

SELECT LedgerCode, DebitOrCredit,DebitAmount, CreditAmount FROM accountingtransaction
WHERE VoucherNo IN
(
SELECT VoucherNo FROM accountingtransaction
WHERE LedgerCode = '3001'
)
AND LedgerCode = '3001'

this query fetch only 3001 datas. can you re try?

__TR__ 8-Dec-12 3:42am

Your query will not work in my system as i don't have the accountingtransaction table or any data other than what you have provided in your question.
Also your query has 4 columns while the result in your question only has 3 columns.
As i suggested earlier, update your question to provide more details on what is it you are trying to do.

Member 9504742 · Answer 2 · 2012-12-07T22:18:00

Solution 3

select LedgerName,DebitOrCredit,CreditAmount from table_name where DebitOrCredit='Cr'

Posted 7-Dec-12 22:18pm

Member 9504742

Comments

Rajaram24101984 8-Dec-12 4:24am

No, if i query for cash A/c, query will fetch the data that contains Cash A/c in each voucher that may either credit or debit of transaction. Vice versa, If I Query for Axis Bank, It will show only Cash A/c 3000.00