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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 sql ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ntn104
Posting Yak Master

172 Posts

Posted - 09/07/2012 :  14:14:56  Show Profile  Reply with Quote
Hello,

I want to get a list of those accounts that has zero balance as a total, but I used below query, it still showing accounts that has balance for other year/month, it only have one year has balance =0...the rest still has balance. How do we fix the query to make it shows only the one that has zero balance for all year/month. Thanks,

SELECT ACTNO, YEAR, MONTH, SUM(BAL) AS BALANCE
FROM TABLE1
GROUP BY ACTNO, YEAR, MONTH
HAVING SUM(CASE WHEN BAL IS NOT NULL THEN BAL ELSE 0 END)=0

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/07/2012 :  15:05:20  Show Profile  Reply with Quote
SELECT
	ACCTNO
FROM
	TABLE1 t1
WHERE
	NOT EXISTS 
	(
		SELECT t2.ACTNO
		FROM   TABLE1 t2
		WHERE t1.ACCTNO = t2.ACCTNO
		GROUP BY
			   t2.ACTNO,
			   t2.YEAR,
			   t2.MONTH
		HAVING SUM(CASE WHEN BAL IS NOT NULL THEN BAL ELSE 0 END) <> 0
	)
Go to Top of Page

ntn104
Posting Yak Master

172 Posts

Posted - 09/10/2012 :  11:46:31  Show Profile  Reply with Quote
It works ...thanks much!

quote:
Originally posted by sunitabeck

SELECT
	ACCTNO
FROM
	TABLE1 t1
WHERE
	NOT EXISTS 
	(
		SELECT t2.ACTNO
		FROM   TABLE1 t2
		WHERE t1.ACCTNO = t2.ACCTNO
		GROUP BY
			   t2.ACTNO,
			   t2.YEAR,
			   t2.MONTH
		HAVING SUM(CASE WHEN BAL IS NOT NULL THEN BAL ELSE 0 END) <> 0
	)


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.05 seconds. Powered By: Snitz Forums 2000