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.DataCust-no, From-Cust, Debit, Credit12345, 12345, 100,012345, 12345, 100,0 12345, 12345,0, 20054321, 12345, 100,054321, 54321, 200,054321, 54321, 200,054321, 54321,0, 40065432, 65432, 300,065432, 65432, 100,0What 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, Credit54321, 12345, 100,065432, 65432, 300,065432, 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.DataCust-no, From-Cust, Debit, Credit12345, 12345, 100,012345, 12345, 100,0 12345, 12345,0, 20054321, 12345, 100,054321, 54321, 200,054321, 54321, 200,054321, 54321,0, 40065432, 65432, 300,065432, 65432, 100,0What 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, Credit54321, 12345, 100,065432, 65432, 300,065432, 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] |
|
|
hbadministrator
Posting Yak Master
120 Posts |
Posted - 2013-05-03 : 12:36:03
|
Awesome! Thank you! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-03 : 13:34:28
|
You are very welcome - glad to help. |
|
|
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 @DataVALUES (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 fFROM ( SELECT SUM(Debit - Credit) OVER (PARTITION BY CustNo, FromCust) AS theSum FROM @Data ) AS fWHERE theSum = 0;SELECT *FROM @Data; N 56°04'39.26"E 12°55'05.63" |
|
|
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. |
|
|
|
|
|