How to calculation between 3 column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to calculation between 3 column?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-03-19 : 12:58:41

Hi
I am using below code in sql procedure and the data types are below.
b.price -nvarchar 255
b.quantity - nvarchar 255
a.NumPacks - numeric

cast(b.price as decimal(19,2)) / cast(a.NumPacks * b.quantity as decimal(19,2)) AS UNITPRICE

When I run the above code I should get the result as below
1400.99
Instead I am getting as below
1400.990000000000000000

Please help. Advance Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-19 : 13:27:02

Those values are the same. You can format any way you want at your client when outputting your results.

You also have a really bad design -- you should be using proper data types for your columns and your data, never use NVARCHAR when your data is numeric.

- Jeff
http://weblogs.sqlteam.com/JeffS

modi_sanjay
Starting Member

9 Posts

Posted - 2008-03-19 : 15:38:25

Hi
You have to put cast outside for trimming the getting result. But i recommended to you please keep column data type as appropriate

cast( put your above code here as decimal(19,2))

Like
cast( cast(b.price as decimal(19,2)) / cast(a.NumPacks * b.quantity as decimal(19,2)) as decimal(19,2)) AS UNITPRICE

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-19 : 19:06:42

...or you might want to try casting price as money rather than decimal.

e4 d5 xd5 Nf6

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-03-20 : 08:35:36

Hi

Thanks a lot. It works

quote:
Originally posted by modi_sanjay

Hi
You have to put cast outside for trimming the getting result. But i recommended to you please keep column data type as appropriate

cast( put your above code here as decimal(19,2))

Like
cast( cast(b.price as decimal(19,2)) / cast(a.NumPacks * b.quantity as decimal(19,2)) as decimal(19,2)) AS UNITPRICE

modi_sanjay
Starting Member

9 Posts

Posted - 2008-03-20 : 13:09:29

You Welcome

quote:
Originally posted by Vaishu

Hi

Thanks a lot. It works

quote:
Originally posted by modi_sanjay

Hi
You have to put cast outside for trimming the getting result. But i recommended to you please keep column data type as appropriate

cast( put your above code here as decimal(19,2))

Like
cast( cast(b.price as decimal(19,2)) / cast(a.NumPacks * b.quantity as decimal(19,2)) as decimal(19,2)) AS UNITPRICE

Thanks & Regards

Sanjay Modi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources