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

Divide column values Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 3:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 7:18 AM
Points: 55, Visits: 184
Hi,

I have these two columns

select
'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Total_Amount,2)as MONEY),1), '.00', '') as Total_Amount,
'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Monthly_Amount,2)as MONEY),1), '.00', '') as Monthly_Amount
from Finance

Now Monthly_Amount column should have calculated values like Total_Amount/12
Post #1568336
Posted Wednesday, May 7, 2014 3:31 AM
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 @ 9:15 AM
Points: 3,483, Visits: 9,325
vigneshkumart50 (5/7/2014)
Hi,

I have these two columns

select
'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Total_Amount,2)as MONEY),1), '.00', '') as Total_Amount,
'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Monthly_Amount,2)as MONEY),1), '.00', '') as Monthly_Amount
from Finance

Now Monthly_Amount column should have calculated values like Total_Amount/12


Is this what you are after?

select 
'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Total_Amount,2)as MONEY),1), '.00', '') as Total_Amount,
'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Total_Amount,2)as MONEY) / 12,1), '.00', '') as Monthly_Amount
from Finance

Post #1568338
Posted Wednesday, May 7, 2014 4:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 7:18 AM
Points: 55, Visits: 184
yes also for monthly_amount I need to have two decimal values

Like
$ 134,98.55
$ 41.34
Post #1568350
Posted Wednesday, May 7, 2014 4:04 AM
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 @ 9:15 AM
Points: 3,483, Visits: 9,325
Just move the division inside the round function.
Post #1568352
Posted Wednesday, May 7, 2014 4:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 7:18 AM
Points: 55, Visits: 184
like this

'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Total_Amount,2)/12 as MONEY),1), '.00', '') as Monthly_Amount,
Post #1568354
Posted Wednesday, May 7, 2014 4:13 AM This worked for the OP Answer marked as solution
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 @ 9:15 AM
Points: 3,483, Visits: 9,325
More like this
'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round((Total_Amount/12),2) as MONEY),1), '.00', '') as Monthly_Amount,

Post #1568356
Posted Wednesday, May 7, 2014 4:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:30 AM
Points: 1,978, Visits: 20,833
vigneshkumart50 (5/7/2014)
like this

'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Total_Amount,2)/12 as MONEY),1), '.00', '') as Monthly_Amount,


why CAST as MONEY and then REPLACE....couldn't you just cast as INT?


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1568361
Posted Wednesday, May 7, 2014 4:35 AM
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 @ 9:15 AM
Points: 3,483, Visits: 9,325
J Livingston SQL (5/7/2014)
vigneshkumart50 (5/7/2014)
like this

'$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Total_Amount,2)/12 as MONEY),1), '.00', '') as Monthly_Amount,


why CAST as MONEY and then REPLACE....couldn't you just cast as INT?


That would be implicit rounding
Post #1568364
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse