Subtract and change Negative sign to positive sign [Resolved]

Posted by Sriharim under Sql Server on 6/16/2015 | Points: 10 | Views : 1365 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Mark as Answer if its helpful to you
---
Srihari

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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


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

0	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 Kumaraspcode2009@gmail.com Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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 Kumaraspcode2009@gmail.com Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

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

Kumaraspcode2009@gmail.com

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

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

0	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) Mark as Answer if its helpful to you --- Srihari Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts