July 12, 2012 at 1:04 pm
I am trying to display decimal places in a select statement and am not having much luck. Here is a simplified example of what i am trying to do:
IF OBJECT_ID('tempdb..#Example') IS NOT NULL
DROP TABLE #Example
CREATE TABLE #Example
(
Dept char(10),
Numerator int,
Denominator int
)
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
INSERT INTO #Example values('Dept1', 330, 449)
INSERT INTO #Example values('Dept2', 47, 63)
Select e.Dept, e.Numerator, e.Denominator
,(CASE WHEN e.Denominator=0 THEN 0
ELSE e.Numerator/e.Denominator END) as Rate
,(CASE WHEN e.Denominator=0 THEN 0
ELSE 100*e.Numerator/e.Denominator END) as Rate2
,cast((e.numerator)/e.Denominator as decimal (9,4)) as N3
into #Temp
from #Example E
select * from #Temp
When I run it I get the following futile attempts:
Dept Numerator Denominator Rate Rate2 N3
Dept1 330 449 0 73 0.0000
Dept2 47 63 0 74 0.0000
What I would like to get to is this:
Dept Numerator Denominator Rate
Dept1 330 449 0.7349
Dept2 47 63 0.7460
Any help would be appreciated.
Jon
July 12, 2012 at 1:13 pm
Try this:
Select e.Dept, e.Numerator, e.Denominator
,(CASE WHEN e.Denominator=0 THEN 0
ELSE e.Numerator/(e.Denominator * 1.00)
END) as Rate
,(CASE WHEN e.Denominator=0 THEN 0
ELSE 100*e.Numerator/(e.Denominator * 1.00)
END) as Rate2
,cast((e.numerator)/(e.Denominator * 1.00) as decimal (9,4)) as N3
from #Example E
July 12, 2012 at 3:11 pm
Thanks for your response. I tried your suggestion on my example and that works but for some reason it is not working on the data I am pulling from the table.
July 12, 2012 at 3:20 pm
Nevermind, I moved the calculation to the front (i.e. 1.00*...) and that worked. Thanks!
July 12, 2012 at 3:21 pm
Are u using the same idea that im used on your example? Just multiply denominator with 1.00 (this triggers implicit conversion) and there by u ll get decimal places..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply