Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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
Edited 7-Dec-12 19:50pm
Mehdi Gholam231.6K
v2
Comments
Krunal Rohit at 8-Dec-12 1:52am
   
have you tried anything so far ???
Rajaram24101984 at 8-Dec-12 2:45am
   
yes i have tried... but can not get the exact result
Rajaram24101984 at 8-Dec-12 3:34am
   
Krunal did you tried on your side
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try
SELECT LedgerName, DebitOrCredit, CreditAmount FROM [YourTable]
WHERE VoucherNo IN
(
	SELECT VoucherNo FROM [YourTable]
	WHERE LedgerName = 'Cash A/c'
)
AND DebitOrCredit = 'Cr'
  Permalink  
v2
Comments
Rajaram24101984 at 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__ at 8-Dec-12 3:13am
   
Check the updated solution.
Rajaram24101984 at 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__ at 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 at 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__ at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

select LedgerName,DebitOrCredit,CreditAmount from table_name where DebitOrCredit='Cr'
  Permalink  
Comments
Rajaram24101984 at 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

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



Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 8 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid