June 4, 2013 at 5:08 pm
I have data that looks like this (ignore the dashes):
Invoice# --- InvoiceAmount
47730 --- 39,843.45
47730 --- 492.06
47730 --- -45,022.05
47730 --- 4,686.54
47730 --- 39,843.63
I 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# --- InvoiceAmount
47730 --- 39,843.63
June 4, 2013 at 5:39 pm
I made an edit...to my original request the positive amount does equal the negative amount and those records get removed.
June 4, 2013 at 5:46 pm
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?
June 4, 2013 at 5:50 pm
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.
June 4, 2013 at 6:57 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply