calculated fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

calculated fields

Author

Topic

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-06 : 01:38:33

I have fields which data types are int.
How do I do a calculation & change it to decimal as im getting a 1.5 value. Here is my SQL
First I want to minus a.Field1 AND b.Field1 then multiply the answer by c.Field1 then Divide the end result by 100.
Have I added it in the correct order & change the end result to a decimal so it can show a 1.5

select (a.Field1 - b.Field1) * c.Field1 / 100 AS Field1Amount
FROM Table1 AS a
INNER JOIN Table2 AS b ON a.Field2 = b.Field2
INNER JOIN Table3 AS c ON a.Field2 = c.Field2
WHERE a.Field2 = @Field2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 01:50:19

Use 100.0 instead of 100

Madhivanan

Failing to plan is Planning to fail

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-06 : 02:21:53

It works but I get a number as 1.500000.
How do I get rid of the leading zeros

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 02:32:38

There are no leading zeros.
Do you mean trailing zeros?

E 12°55'05.25"
N 56°04'39.16"

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-06 : 02:34:29

Any other way to do this. The end result is currency.
a.Field1 = 5
b.Field1 = 2
c.Field1 = 50

5 - 2 = 3
3 * 50 = 150
150 / 100 = 1.5
Answer I want is 1.5
I change it to currency in C# R 1.50

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 02:54:11

If you use C# then you can format the result there

Madhivanan

Failing to plan is Planning to fail

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-06 : 03:05:05

Thanks so much. It is working....
This site is great help. this is my first source of help when stuck with a sql problem.
Thanks again guys. Much appreciated....... !!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 03:15:26

quote:
Originally posted by u2envy1

Thanks so much. It is working....
This site is great help. this is my first source of help when stuck with a sql problem.
Thanks again guys. Much appreciated....... !!

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources