Click here to Skip to main content
15,845,785 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
SQL
SELECT COA.acc_code  AS  'Account Code', COA.acc_name  AS  'Account Name', CAST(SUM(debit-credit) AS varchar) AS [$ Debit]
FROM COA  JOIN AccountReceivable
ON COA.acc_name = AccountReceivable.acc_name
WHERE COA.acc_name IN ('Account Receivable')  
GROUP BY COA .acc_code, COA.acc_name

union all

SELECT COA.acc_code  AS  'Account Code', COA.acc_name  AS  'Account Name', CAST(SUM(debit-credit) AS varchar) AS [$ Credit]
FROM COA  JOIN AccountPayable
ON COA.acc_name = AccountPayable.acc_name
WHERE COA.acc_name IN ('Account Payable')  
GROUP BY COA .acc_code, COA.acc_name


I want my result output be like the second table where debit value is under debit credit value is under credit.

Account Code	    Account Name	                Debit
101	            Account Receivable	                200	
200	            Account Payable		       -250



Account Code	    Account Name	                Debit	                Credit
101	            Account Receivable	                               200	
200	            Account Payable		                                           -250


[edit]Code block added - OriginalGriff[/edit]
Posted
Updated 9-Apr-13 3:39am
v3
Comments
ZurdoDev 9-Apr-13 8:49am    
I'm really confused. Are you saying that you want the order of your fields to be different? If so, then change them. What's the issue?
Sergey Alexandrovich Kryukov 9-Apr-13 11:32am    
Please do yourself a great favor: stop posting non-answers as "solutions" and non-questions as "questions".
—SA

You cannot, in an UNION statement, give two different names to a column (here I'm talking about the [$ Debit]/[$ Credit] columns). Moreover, you only have to specify column names in the first query (all subsequent queries will inherit the naming schema).

You have to select 4 columns ; in the first query give the [$ Credit] a null value ; in the second one, give the [$ Debit] a null value.

Thus:

SQL
SELECT
   COA.acc_code AS [Account Code]
  ,COA.acc_name AS [Account Name]
  ,CAST(SUM(debit-credit) AS varchar) AS [$ Debit]
  ,NULL AS [$ Credit]
FROM
  COA JOIN AccountReceivable ON COA.acc_name = AccountReceivable.acc_name
WHERE
  COA.acc_name IN ('Account Receivable')
GROUP BY
  COA.acc_code, COA.acc_name
 
UNION ALL
 
SELECT
   COA.acc_code
  ,COA.acc_name
  ,NULL
  ,CAST(SUM(debit-credit) AS varchar)
FROM
  COA JOIN AccountPayable ON COA.acc_name = AccountPayable.acc_name
WHERE
  COA.acc_name IN ('Account Payable')
GROUP BY
  COA.acc_code, COA.acc_name


Hope this helps.
 
Share this answer
 
v3
Try this

SELECT COA.acc_code AS 'Account Code', COA.acc_name AS 'Account Name', CAST(SUM(debit-credit) AS varchar) AS [$ Debit],CAST(SUM(debit-credit) AS varchar) AS [$ Credit]
FROM COA left outer JOIN AccountReceivable
ON COA.acc_name = AccountReceivable.acc_name
left outer join AccountPayable
on COA.acc_name = AccountPayable.acc_name
WHERE COA.acc_name IN ('Account Receivable', 'Account Payable')
GROUP BY COA .acc_code, COA.acc_name
 
Share this answer
 

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