Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
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 9-Apr-13 3:35am
Edited 9-Apr-13 3:39am
v3
Comments
ryanb31 at 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 at 9-Apr-13 11:32am
   
Please do yourself a great favor: stop posting non-answers as "solutions" and non-questions as "questions".
—SA
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
 
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.
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 240
1 Kamal Rocks 184
2 PIEBALDconsult 150
3 BillWoodruff 148
4 Jochen Arndt 135
0 OriginalGriff 5,695
1 DamithSL 4,506
2 Maciej Los 4,007
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 9 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100