Sql Calculation differs with windows calculation

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

Sql Calculation differs with windows calculation

Author

Topic

jagBuddy
Starting Member

3 Posts

Posted - 2013-12-20 : 02:38:12

Hi all,
I m new to this forum.Having one problem with my query.

SELECT Convert(varchar,Column1) As DataColumn1,

Convert(varchar,Convert(Decimal(18,4),Column2)) As DataColumn2,
CASE WHEN(
Convert(varchar, Column1)=(select top 1 DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,Column4)+1,0))) as Day from table1 where table1.ID=1217)
)
THEN Column2
WHEN(
Convert(varchar,Column1)<>(select top 1 DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,Column4)+1,0))) as Day from table1 where table1.ID=1217)
)
THEN ((Column2*12/365)*Convert(varchar, convert(decimal(18,4), Column1)))
ELSE column5 END AS DataColumn3,
Convert(varchar,Convert(Decimal(18,4),column5)) As DataColumn4
FROM Table2
WHERE Table2.ID=1217

i m getting result of this as "3.1217" but by windows calculator m getting "3.1236".
Just few points difference is there.but i want exact count as there in windows calculator.what can i do????

Thanks in advance....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-20 : 03:07:08

can you post your table DDL and some sample data ?

KH
[spoiler]Time is always against us[/spoiler]

jagBuddy
Starting Member

3 Posts

Posted - 2013-12-20 : 03:52:19

Thannks 4 reply khtan,

m not allow to give any table details outside industry...but i can tell u that floating number multiplication in sql and in windows calculator is different,..in above query

THEN ((Column2*12/365)*Convert(varchar, convert(decimal(18,4), Column1)))
ELSE column5 END AS DataColumn3,

Column2=Rate which is 5 rupees.
Column1=days which is 19.

should show 3.123287(by calculator)
but it is showing 3.1217(sql calculation)

this condition giving result which is different.

Kristen
Test

22859 Posts

Posted - 2013-12-20 : 04:22:33

quote:
Originally posted by jagBuddy

m not allow to give any table details outside industry

Then you need to construct an example that you can post, and which demonstrates the problem.

Or get a consultant in.

You are multiplying Column2 by 12/365. No parenthesis, so you are using default precedence rules. This is integer maths. Or maybe part of it is floating point, depending on the datatype of Column2

Then you are taking a decimal(18,4) and converting it to a string and then multiplying!! that by the result of the first calculation - which is either INT or some floating point numeric type, depending on the data type of column2.

I'm not surprised the results are cockeyed.

Provide some data, and expected result, and a (stand alone) query that makes the calculation, and I am sure that folk here will be able to advise

jagBuddy
Starting Member

3 Posts

Posted - 2013-12-20 : 05:17:49

i got the reason why i was getting the wrong value.Because of round function.Round function was incrementing the value of column in some points and after multiplication it was showing me the quite large difference in calculation...
Can u please tell me now one thing.
If i m having value as 5 as my rate.and i want to multiply 5 with 12/365(eg:5*12/365).What would be the round value of this.I want dis should be 0.1644.but m getting 0.16438356..How can i round dis to get 0.1644 result???
?
?
?

thank you...

Kristen
Test

22859 Posts

Posted - 2013-12-20 : 06:27:49

"i want to multiply 5 with 12/365(eg:5*12/365) ... I'm getting 0.16438356"

No, you will get zero.


SELECT 5*12/365

so yet again you are only telling us half the story, and not providing a worked example. People don't have time to spend guessing what you mean, or might be doing. It would be SO easy to help if you provided an example.


SELECT 5.0*12/365

Nope, that isn't it either. That gives 0.164383 rather than the figure of 0.16438356 that you quote ... although that would round to 0.1644, but it isn't the intermediate result that you quoted, so you must be using a different figure, or a different datatype, which might influence the outcome of any advice we give.

Anyways, you can round it to 4 decimal places by using the ROUND() function.


SELECT ROUND(5.0*12/365, 4)

gives result 0.164400

Of course if you want that truncated to "0.1644" at your application instead, in order to remove the trailing zeros, then that's a different question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources