Click here to Skip to main content
15,867,779 members
Please Sign up or sign in to vote.
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
Updated 7-Dec-12 19:50pm
v2
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

select LedgerName,DebitOrCredit,CreditAmount from table_name where DebitOrCredit='Cr'
 
Share this answer
 
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
Try
SQL
SELECT LedgerName, DebitOrCredit, CreditAmount FROM [YourTable]
WHERE VoucherNo IN
(
	SELECT VoucherNo FROM [YourTable]
	WHERE LedgerName = 'Cash A/c'
)
AND DebitOrCredit = 'Cr'
 
Share this answer
 
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?

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