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 2008 Forums
 Transact-SQL (2008)
 Sum fields then remove them if 0
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

118 Posts

Posted - 05/03/2013 :  11:50:39  Show Profile  Reply with Quote
I thought I posted this but maybe not. I have a table that houses transactions. It has Cust-no (master Account) From-cust (Site Account) Debit and Credits. I need to sum up the debits minus the credits to find the accounts that have a 0 balance then delete those transactions. Data below and what I would like to see.

Data
Cust-no, From-Cust, Debit, Credit
12345, 12345, 100,0
12345, 12345, 100,0
12345, 12345,0, 200
54321, 12345, 100,0
54321, 54321, 200,0
54321, 54321, 200,0
54321, 54321,0, 400
65432, 65432, 300,0
65432, 65432, 100,0

What I want to do is group by From-cust then cust-no, Sum((Debit)+Sum(Credit)) AS Balance, if Balance is = 0 then remove transactions.

End results from data above would be.

Cust-no, From-Cust, Debit, Credit
54321, 12345, 100,0
65432, 65432, 300,0
65432, 65432,0, 100

Edited by - hbadministrator on 05/03/2013 11:55:12

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/03/2013 :  12:07:28  Show Profile  Reply with Quote
quote:
Originally posted by hbadministrator

I thought I posted this but maybe not. I have a table that houses transactions. It has Cust-no (master Account) From-cust (Site Account) Debit and Credits. I need to sum up the debits minus the credits to find the accounts that have a 0 balance then delete those transactions. Data below and what I would like to see.

Data
Cust-no, From-Cust, Debit, Credit
12345, 12345, 100,0
12345, 12345, 100,0
12345, 12345,0, 200
54321, 12345, 100,0
54321, 54321, 200,0
54321, 54321, 200,0
54321, 54321,0, 400
65432, 65432, 300,0
65432, 65432, 100,0

What I want to do is group by From-cust then cust-no, Sum((Debit)+Sum(Credit)) AS Balance, if Balance is = 0 then remove transactions.

End results from data above would be.

Cust-no, From-Cust, Debit, Credit
54321, 12345, 100,0
65432, 65432, 300,0
65432, 65432,0, 100

Shouldn't 65432 really be two debit lines? Also, I assume you want to check for Sum(Debit)-Sum(Credit) rather than Sum((Debit)+Sum(Credit)) which is something completely different. Assuming all that
;WITH    cte
          AS ( SELECT   [Cust-no] ,
                        [From-Cust]
               FROM     YourTable
               GROUP BY [Cust-no] ,
                        [From-Cust]
               HAVING   COALESCE(SUM(Debit), 0) - COALESCE(SUM(Credit), 0) = 0
             )
    DELETE  y
    FROM    YourTable y
            INNER JOIN cte c ON c.[Cust-no] = y.[Cust-no]
                                AND c.[From-Cust] = y.[From-Cust]
Go to Top of Page

hbadministrator
Posting Yak Master

118 Posts

Posted - 05/03/2013 :  12:36:03  Show Profile  Reply with Quote
Awesome! Thank you!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/03/2013 :  13:34:28  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 05/03/2013 :  13:48:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Any reason a windowed function wouldn't work?
DECLARE	@Data TABLE (CustNo INT, FromCust INT, Debit INT, Credit INT);

INSERT	@Data
VALUES	(12345, 12345, 100,   0),
	(12345, 12345, 100,   0), 
	(12345, 12345,   0, 200),
	(54321, 12345, 100,   0),
	(54321, 54321, 200,   0),
	(54321, 54321, 200,   0),
	(54321, 54321,   0, 400),
	(65432, 65432, 300,   0),
	(65432, 65432, 100,   0);

DELETE	f
FROM	(
		SELECT	SUM(Debit - Credit) OVER (PARTITION BY CustNo, FromCust) AS theSum
		FROM	@Data
	) AS f
WHERE	theSum = 0;

SELECT	*
FROM	@Data;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/03/2013 :  14:34:40  Show Profile  Reply with Quote
quote:
Any reason a windowed function wouldn't work?
None that I can think of; windowing would be/should be faster as well.
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.06 seconds. Powered By: Snitz Forums 2000