sql ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sql ?

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2012-09-07 : 14:14:56

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 15:05:20

[code]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
)[/code]

ntn104
Posting Yak Master

175 Posts

Posted - 2012-09-10 : 11:46:31

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
	)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources