Select >0 Money Balances - 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 2008 Forums

Transact-SQL (2008)

Select >0 Money Balances

Author

Topic

proplink
Starting Member

4 Posts

Posted - 2011-04-22 : 14:49:58

I am trying to use a select statement in a stored procedure to calculate the money balance for each client but only return the balance if it is greater than 0. I have set up the select statement as follows:

SELECT ISNULL(SUM(dbo.Table1.cashCredit),0) - ISNULL(SUM(dbo.Table1.cashDebit),0)
AS cashBalance, clientName FROM Table1

GROUP BY clientName
ORDER BY cashBalance DESC

But now need to filter out the zero balances. Any suggestions much appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-22 : 14:55:25

SELECT cashBalanace, clientName
FROM (
SELECT ISNULL(SUM(dbo.Table1.cashCredit),0) - ISNULL(SUM(dbo.Table1.cashDebit),0) AS cashBalance, clientName
FROM Table1
GROUP BY clientName) t
WHERE cashBalance > 0
ORDER BY cashBalance DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 15:08:47

SELECT SUM(ISNULL(dbo.Table1.cashCredit,0)) - SUM(ISNULL(dbo.Table1.cashDebit ,0))
AS cashBalance, clientName
FROM Table1
GROUP BY clientName
HAVING SUM(ISNULL(dbo.Table1.cashCredit,0)) - SUM(ISNULL(dbo.Table1.cashDebit ,0)) > 0
ORDER BY cashBalance DESC

Jim

Everyday I learn something that somebody else already knew

proplink
Starting Member

4 Posts

Posted - 2011-04-22 : 15:19:13

Thanks for these solutions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources