Subtract and change Negative sign to positive sign [Resolved]

Posted by Sriharim under Sql Server on 6/16/2015 | Points: 10 | Views : 331 | Status : [Member] | Replies : 4
for Below data:
ID  col1    col2    col3    total
1 26.67 -13.34 -13.33 40.00
2 40.00 -20.00 -20.00 60.00
3 -26.67 53.34 -26.67 80.00
4 26.67 -13.34 -13.33 40.00
5 8.50 -8.50 0.00 17.00
For rows, in 3 columns col1,col2, col3, one column value (record) will have +ve and remaining 2 can be -ve or 0. I want
1. +ve value sholud be subtracted from total value and
2. all -ve sholud removed.
Required Output is:
ID  col1    col2    col3    total
1 13.33 13.34 13.33 40.00
2 20.00 20.00 20.00 60.00
3 26.67 26.66 26.67 80.00
4 13.33 13.34 13.33 40.00
5 8.50 8.50 0.00 17.00


---
Srihari



Responses

Posted by: Bandi on: 6/16/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

declare @tab table(ID int,col1 dec(6,2),col2 dec(6,2),col3 dec(6,2),total dec(6,2))
insert @tab
SELECT 1 ,26.67, -13.34 , -13.33 , 40.00 union all
SELECT 2 ,40.00 , -20.00 , -20.00 , 60.00 union all
SELECT 3 ,-26.67 , 53.34 , -26.67 , 80.00 union all
SELECT 4 , 26.67 ,-13.34 , -13.33 , 40.00 union all
SELECT 5, 8.50 , -8.50 , 0.00 , 17.00

SELECT ID, case when SIGN(col1) >0 then total-col1 else ABS(col1) end col1
,case when SIGN(col2) >0 then total-col2 else ABS(col2) end col2
,case when SIGN(col3) >0 then total-col3 else ABS(col3) end col3
,total
FROM @tab


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 6/16/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

Can you provide table structure plz. becos decimal datatype canot allow -13.34 so i m asking ?

- change to + plus using abs function give me explain elaborate

Mark as Answer if its helpful to you

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 6/16/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
ram

check this query working for me.


create table a11
(
id int primary key identity(1,1),
col1 decimal(18,2)
)


Insert into a11 values(-12.25)
Insert into a11 values(-11.25)
Insert into a11 values(-16.25)
Insert into a11 values(-2.25)


Select id,abs(col1) from a11

Mark as Answer if its helpful to you

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 6/16/2015 [Member] Starter | Points: 25

Up
0
Down
for 3 columns col1,col2, col3 datatype is money.
thanks for information about abs() function...... how to subtract (answer for point 1 in my post)

---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response