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)
 Sum fields then remove them if 0

Author  Topic 

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-05-03 : 11:50:39
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-03 : 12:07:28
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

120 Posts

Posted - 2013-05-03 : 12:36:03
Awesome! Thank you!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-03 : 13:34:28
You are very welcome - glad to help.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-05-03 : 13:48:51
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-03 : 14:34:40
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
   

- Advertisement -