Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Amounts cancel out Expand / Collapse
Author
Message
Posted Tuesday, June 4, 2013 5:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:43 AM
Points: 159, Visits: 422
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



Post #1459991
Posted Tuesday, June 4, 2013 5:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:43 AM
Points: 159, Visits: 422
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 4, 2013 5:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 3,783, Visits: 8,483
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1459995
Posted Tuesday, June 4, 2013 5:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:43 AM
Points: 159, Visits: 422
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 4, 2013 6:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:43 AM
Points: 159, Visits: 422
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse