Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Amounts cancel out Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, June 04, 2013 5:08 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, December 03, 2013 11:33 AM Points: 111, Visits: 287
 I have data that looks like this (ignore the dashes):Invoice# --- InvoiceAmount47730 --- 39,843.4547730 --- 492.0647730 --- -45,022.0547730 --- 4,686.5447730 --- 39,843.63I want to keep the last line and remove line 1 thru 4. The logic is that the negative number must equal the sum of the positive numbers for that invoice number and what is left does not get removed. How can I do this?I would have a new table with :Invoice# --- InvoiceAmount47730 --- 39,843.63
Post #1459991
 Posted Tuesday, June 04, 2013 5:39 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, December 03, 2013 11:33 AM Points: 111, Visits: 287
 I made an edit...to my original request the positive amount does equal the negative amount and those records get removed.
Post #1459994
 Posted Tuesday, June 04, 2013 5:46 PM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 9:16 PM Points: 1,890, Visits: 4,175
 What happens when the positive amount won't equal the negative amount?Do you want a balance or just to eliminate certain amounts that won't change the calculation? Luis C.Please don't trust me, test the solutions I give you before using them.Forum Etiquette: How to post data/code on a forum to get the best help
Post #1459995
 Posted Tuesday, June 04, 2013 5:50 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, December 03, 2013 11:33 AM Points: 111, Visits: 287
 If the positive amounts don't equal the negative amount then nothing happens, those amounts get added to the new table. I want to create a new table with the amounts left over after the negative amount that equals the positive amounts are removed from the query.
Post #1459997
 Posted Tuesday, June 04, 2013 6:57 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, December 03, 2013 11:33 AM Points: 111, Visits: 287
 I figured out the query that works for me:Select S1.* Into AS_Workspace.dbo.StdPmt_InvAmt_SUM_Removed From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1 Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt Group By VndNbr, InvNbr) as S2 ON S1.InvNbr = S2.InvNbr AND S1.VndNbr = S2.VndNbr AND S1.InvAmt = S2.InvAmt_Sum
Post #1460003

 Permissions