December 7, 2010 at 1:35 pm
I'm trying to get a result of a product to return a decimal number (i.e. 1.05) but I keep getting 1
Say, for example the following values in the syntax below: 3 * .35 = 1.05
Cast(PTS_QA_CE * 0.35 as decimal (10,2))
Is there an add'l convert or cast step I need to do?
thx,
John
December 7, 2010 at 1:47 pm
First, I'm assuming that the text bit in there is a column name.
Assuming that's correct, it would be helpful to see the actual query, and to know what data type the column is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 7, 2010 at 2:05 pm
See the code below:
Selectb.FullName,
a.LANLogin,
b.HireDate,
[Status]= b.Active,
b.Supervisor,
[Month]= a.RecMonth,
Period= a.RecPeriod,
b.[Site],
b.Department,
QA_CE= Isnull(c.QA_CE,0),
PTS_QA_CE= Null,
NoOfScans= Isnull(c.NoOfScans,0),
Consistency= Isnull(c.Consistency,0),
PTS_Consistency = Null,
JobKnowledge = Isnull(d.Grade,0),
PTS_JobKnwldg = Null,
Total= Null
Into#AllTables
From#Period a
InnerJoin
#Employees b
Ona.LANLogin = b.LANLogin
LeftJoin
#CactiData c
Ona.LANLogin = c.CactiAgentId
Anda.RecPeriod = c.RecPeriod
LeftJoin
#Moodle1 d
Ona.LANLogin = d.UserId
Anda.RecPeriod = d.TestPeriod
Andb.Department = d.Department
Orderby b.FullName
----IMPLEMENT METRICS CALCULATIONS----
Update#AllTables
SetPTS_QA_CE= Case When QA_CE = 0.00 Then 0 When QA_CE Between 0.01 And 84.59 Then 1 When QA_CE Between 84.60 And 89.59 Then 2 When QA_CE Between 89.60 And 95.09 Then 3
When QA_CE Between 95.10 And 99.99 Then 4 When QA_CE = 100.00 Then 5 End,
PTS_Consistency= Case When Consistency = 0.00 Then 0 When Consistency Between 0.01 And 69.99 Then 1 When Consistency Between 70.00 And 74.99 Then 2 When Consistency Between
75.00 And 90.09 Then 3 When Consistency Between 90.10 And 99.99 Then 4 When Consistency = 100.00 Then 5 End,
PTS_JobKnwldg= Case When JobKnowledge = 0.00 Then 0 When JobKnowledge Between 0.01 And 75.09 Then 1 When JobKnowledge Between 75.10 And 84.99 Then 2 When JobKnowledge Between
85.00 And 94.99 Then 3 When JobKnowledge Between 95.00 And 99.99 Then 4 When JobKnowledge = 100.00 Then 5 End
Update#AllTables
SetTotal= Cast(PTS_QA_CE * 0.35 as decimal (10,2))
December 7, 2010 at 2:25 pm
Figured it out.
I have to set my field as decimal, then I have to cast the value/field I'm multiplying to float, then multiply by the decimal value and that does the trick.
regards,
John
December 7, 2010 at 3:40 pm
Be careful with float if you need exact answers. Float is an approximate data type and you need to look at BOL and understand what the implications for that are.
December 7, 2010 at 4:56 pm
I don't think you have to do all that. Just change the first SELECT ... INTO statement:
...
PTS_QA_CE = CAST(Null AS decimal(10, 2)),
...
Btw, don't use decimal(10, 2) unless you really do need that extra digit -- that extra digit costs you another four bytes vs decimal(9, 2).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply