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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 6:52 AM
Points: 49, Visits: 155
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 1,951, Visits: 5,074
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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 6:52 AM
Points: 49, Visits: 155
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 1,951, Visits: 5,074
Just move the division inside the round function.
Post #1568352
Posted Wednesday, May 7, 2014 4:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 6:52 AM
Points: 49, Visits: 155
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 1,951, Visits: 5,074
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 @ 1:37 AM
Points: 1,919, Visits: 19,321
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 1,951, Visits: 5,074
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