Summing - Adding a negative and a positive

Question

Summing - Adding a negative and a positive

malcolm.garbett

Ten Centuries

Points: 1160
More actions
March 20, 2014 at 4:07 am

#305443

I have a query that brings in a result from a table that I have to display as a negative, so I use ABS (tbname.fieldname) *-1
This works fine. (Learnt it here from a previous post.)
I have another query that brings in a result from the same table but as a postive. So that works fine too.
Now, I need to sum them together in a new query. If the total value is a positive, fine. If the total value is a negative, it displays as a positive.
My question. Is it actually possible to achieve what I'm trying to do?
Output example below - narrow result for testing.
idpt2 Value2SumQty
N1141307-80970 3.80 2
N1141S2G00009D070 26.16 2
The 3.80 is correct, it is a positive.
The 26.16 is incorrect as it is the output from sum 235.44 -261.6 so I need it to display as -26.6

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

How about doing a CASE statement against the result? Then you can adjust what you subtract from what to get the answer you need. The one thing you haven't mentioned is if the numbers are equal. Don't forget to take that into account so you know how to handle it. If this is something that should never happen and you don't handle it, it will happen. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

malcolm.garbett Ten Centuries Points: 1160 More actions · Answer 2

Ed Wagner (3/20/2014)
How about doing a CASE statement against the result?

Hi Ed,

I was trying that:

SUM(CASE WHEN neg.Valueneg > invoiceitems.homenettvalue THEN ABS (invoiceitems.homenettvalue + neg.Valueneg) *-1

ELSE ABS (invoiceitems.homenettvalue + neg.Valueneg)END) As TestValue,

But I have all the outputs as "positive" or, if I change this: CASE WHEN neg.Valueneg < invoiceitems.homenettvalue , then all "negative" with the "-" symbol.

I could be missing something obvious, but I want to at least confirm that what I'm after is in fact possible?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You will get better responses to your questions if you provide more information. Personally, from your original post, there really isn't enough to really help you. What would help is if you could post the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data for the table(s) (in the form of INSERT INTO statements (some people still use SQL Server 2005)), and the expected results based on the sample data.

For help with this, please read the article I reference below in the first link in my signature block.

malcolm.garbett Ten Centuries Points: 1160 More actions · Answer 4

Thanks Lynn,

Lynn Pettis (3/20/2014)
...from your original post, there really isn't enough to really help you...

Value 1 = 10

Value 2 = -12

Can they be summed to produce result = -2 ?

If "yes" how?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

malcolm.garbett (3/20/2014)
Thanks Lynn,
Lynn Pettis (3/20/2014)
...from your original post, there really isn't enough to really help you...
Value 1 = 10
Value 2 = -12
Can they be summed to produce result = -2 ?
If "yes" how?

select Value1 + Value2

Now, please read that article I recommended as I am sure that the answer I just provided does you no good.

Dohsan SSCrazy Points: 2972 More actions · Answer 6

This work?

DECLARE @a INT = 10,

@b-2 INT = -12;

SELECT@a,

@b-2,

CASE

WHEN @b-2 < 0 THEN @a + @b-2

ELSE @a - @b-2

END;

malcolm.garbett Ten Centuries Points: 1160 More actions · Answer 7

Lynn Pettis (3/20/2014)
malcolm.garbett (3/20/2014)
Thanks Lynn,
Lynn Pettis (3/20/2014)
...from your original post, there really isn't enough to really help you...
Value 1 = 10
Value 2 = -12
Can they be summed to produce result = -2 ?
If "yes" how?
select Value1 + Value2
Now, please read that article I recommended as I am sure that the answer I just provided does you no good.

Thanks Lynn, but that doesn't work, as explained in my original post. It outputs "2" instead of "-2"

malcolm.garbett Ten Centuries Points: 1160 More actions · Answer 8

Dohsan (3/20/2014)
This work?
DECLARE @a INT = 10,
@b-2 INT = -12;
SELECT@a,
@b-2,
CASE
WHEN @b-2 < 0 THEN @a + @b-2
ELSE @a - @b-2
END;

Thanks Dohsan, yes, that works. 🙂 Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Glad you got an answer because I still haven't figured out what it is you are actually trying to accomplish.

malcolm.garbett Ten Centuries Points: 1160 More actions · Answer 10

Lynn Pettis (3/20/2014)
Glad you got an answer because I still haven't figured out what it is you are actually trying to accomplish.

Apologies Lynn.

I've read the article and here is my attempt...

--create test table.
CREATE TABLE #tmp
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
customer NVARCHAR (20),
part NVARCHAR (30),
invtype NVARCHAR (4),
qty FLOAT,
homevalue MONEY
)
--insert test data.
INSERT #tmp
(customer, part, invtype, qty, homevalue)
SELECT 'N1141', '307-80970', 'SCRN', '1', '34.20'
SELECT 'N1141', 'S2G00009D070', 'SCRN', '1', '261.60'
SELECT 'N1141', '307-80970', 'SINV', '1', '38.00'
SELECT 'N1141', 'S2G00009D070', 'SINV', '1', '235.44'
--create query
--cte
With neg As
(
select
#tmp.id,
#tmp.customer,
#tmp.part,
#tmp.invtype,
#tmp.qty,
SUM(-ABS(#tmp.homevalue)) As Valueng
from #tmp
WHERE #tmp.invtype = 'SCRN'
GROUP BY
#tmp.ID,
#tmp.customer,
#tmp.part,
#tmp.invtype,
#tmp.qty
)
--final query, joining cte with #tmp
--summing negative and positive values.
select
#tmp.id,
#tmp.customer,
#tmp.part,
SUM(#tmp.qty + neg.qty) as SumQty,
SUM (#tmp.homevalue + neg.Valueng) as totvalue
from #tmp
LEFT OUTER JOIN neg ON
#tmp.customer = neg.customer AND
#tmp.part = neg.part
WHERE #tmp.invtype = 'SINV'
GROUP BY
#tmp.ID,
#tmp.customer,
#tmp.part,
#tmp.invtype,
#tmp.qty

A very much cut-down query in order to make it clear what I'm after. And this WORKS

idcustomerpartSumQtytotvalue
2N1141S2G00009D0702-26.16
3N1141307-8097023.80

Think I've figured it out. Something wrong with my "production" script.

Last day with this job, so my successor will have to continue...

Thanks for your help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Here is my shot at what you were trying to accomplish. The only difference between my final output and yours is that I left off the ID as it doesn't mean much when grouping on customer and part. Also not sure what value summing the qty actually provides since I also don't know what the invtypes SCRN and SINV actually mean.

CREATE TABLE dbo.TestData

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

customer NVARCHAR (20),

part NVARCHAR (30),

invtype NVARCHAR (4),

qty FLOAT,

homevalue MONEY

);

--insert test data.

INSERT dbo.TestData(customer, part, invtype, qty, homevalue)

SELECT 'N1141', '307-80970', 'SCRN', '1', '34.20' union all

SELECT 'N1141', 'S2G00009D070', 'SCRN', '1', '261.60' union all

SELECT 'N1141', '307-80970', 'SINV', '1', '38.00' union all

SELECT 'N1141', 'S2G00009D070', 'SINV', '1', '235.44';

select * from dbo.TestData;

select

customer,

part,

sum(qty) as SumQty, -- does this really make sense?

sum(case when invtype = N'SINV' then homevalue when invtype = N'SCRN' then -1 * homevalue end) as TotalValue

from

dbo.TestData

group by

customer,

part;

drop table dbo.TestData;