SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select union prob
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lemmor1120
Starting Member

5 Posts

Posted - 02/20/2014 :  17:24:56  Show Profile  Reply with Quote
have problem..please help

Lemmor1120
Starting Member

5 Posts

Posted - 02/20/2014 :  17:27:41  Show Profile  Reply with Quote
Hello Everyone,... I am having a problem using SELECT UNION
here is my code:

SELECT acc_code,sum(debit) as sdebit,SUM(credit) as scredit FROM OR_child WHERE [acc_code] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND doc_date <= '02/22/2014' AND Mem_Code = '13184' GROUP BY Acc_Code
Union
SELECT acc_code,sum(debit) as sdebit,SUM(credit) as scredit FROM JV_child WHERE [acc_code] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND doc_date <= '02/22/2014' AND Mem_Code = '13184' GROUP BY Acc_Code
Union
SELECT glaccount as acc_code,0 as sdebit,SUM(amount) as scredit FROM glxacts WHERE [glaccount] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND createdate <= '02/22/2014' AND cardid = '13184' GROUP BY GLAccount

and here is the result of my code:

acc_code sdebit scredit
40160 0.00 -505.00
40160 0.00 445.00
40402 0.00 50.00
40420 0.00 -10.00


some acc_code doesnt add up... whats wrong with my code.. please help...thanks in advance...
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4363 Posts

Posted - 02/20/2014 :  17:34:19  Show Profile  Reply with Quote
quote:
Originally posted by Lemmor1120

some acc_code doesnt add up... whats wrong with my code.. please help...thanks in advance...


http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

How is it not adding up? It looks like it is given the output you posted.

Edited by - Lamprey on 02/20/2014 17:35:27
Go to Top of Page

Lemmor1120
Starting Member

5 Posts

Posted - 02/20/2014 :  17:40:06  Show Profile  Reply with Quote
Hello,... Kindly check the 40160 acc_code. I wish to combine the results
Go to Top of Page

Lemmor1120
Starting Member

5 Posts

Posted - 02/20/2014 :  17:50:42  Show Profile  Reply with Quote
I really need help with this code... I'm stuck with it. It will be a big help. Thanks thanks..

oh, and Thanks for the link,.. sorry for the posting... this is my first time right a post in a forum... thanks again.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4363 Posts

Posted - 02/20/2014 :  18:11:05  Show Profile  Reply with Quote
I still don't know what you want as you haven't show us what output you expect. My GUESS is that you want to GROUP by the acc_code:
SELECT
	acc_code,
	SUM(sdebit) AS sdebit,
	SUM(scredit) AS scredit
FROM
(
	SELECT acc_code,sum(debit) as sdebit,SUM(credit) as scredit FROM OR_child WHERE [acc_code] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND doc_date <= '02/22/2014' AND Mem_Code = '13184' GROUP BY Acc_Code 
	Union 
	SELECT acc_code,sum(debit) as sdebit,SUM(credit) as scredit FROM JV_child WHERE [acc_code] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND doc_date <= '02/22/2014' AND Mem_Code = '13184' GROUP BY Acc_Code 
	Union 
	SELECT glaccount as acc_code,0 as sdebit,SUM(amount) as scredit FROM glxacts WHERE [glaccount] IN (SELECT CodeID FROM Codes WHERE CodeType = 'INQMISC') AND createdate <= '02/22/2014' AND cardid = '13184' GROUP BY GLAccount
) AS T
GROUP BY
	acc_code
Go to Top of Page

Lemmor1120
Starting Member

5 Posts

Posted - 02/20/2014 :  18:34:51  Show Profile  Reply with Quote
Thats exactly what I was trying to do... Your a genius Lamprey! thanks so much... this is a nice group. fast and effective response. Hope I can join this group. thanks again... cheers!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000