May 5, 2009 at 10:01 am
Hi All,
I have one scenario.
create table #temp
(
week1 decimal(16,8),
week2 decimal(16,8),
week3 decimal(16,8),
gross decimal(16,8)
)
insert into #temp
select -9.12000000,-11.03000000,5.61690000,0
Update #temp
set gross = (((1.0+(week1/100.0))*
(1.0+(week2/100.0))*
(1.0+(week3/100.0)))-1)*100
select * from #temp
output:
week1 week2 week3 gross
-9.12000000-11.030000005.61690000-14.60250000
if i apply the above values directly, i.e
select (((1.0+(-9.12000000/100.0))*(1+(-11.03000000/100))*(1+(5.61690000/100)))-1)*100
i got
-14.602466930816000000000000
I need the above result.
Expected output:
-14.60246693
Inputs are highly appreciable!
karthik
May 5, 2009 at 10:01 pm
hi,
try this
create table #temp
(
week1 decimal(16,8),
week2 decimal(16,8),
week3 decimal(16,8),
gross decimal(38,30)
)
insert into #temp
select -9.12000000,-11.03000000,5.61690000,0
declare @a1 decimal(16,8),@a2 decimal(16,8),@a3 decimal(16,8)
select @a1 = (1.0+(week1/100.000)),
@a2 = (1.0+(week2/100.000)),
@a3 = (1.0+(week3/100.000)) from #temp
update #temp
set gross = ((@a1 * @a2 * @a3)-1)*100
select * from #temp
RESULT
week1week2week3gross
-9.12000000-11.030000005.61690000-14.602466930000000000000000000000
select (((1.0+(-9.12000000/100.0))*(1+(-11.03000000/100))*(1+(5.61690000/100)))-1)*100
RESULT
-14.602466930816000000000000
ARUN SAS
May 5, 2009 at 11:40 pm
Always use FLOAT data type for complex calculations.
DECLARE @Hundred float
SET @Hundred = 100
Update #temp
set gross = (((1.0+(week1/@Hundred))*
(1.0+(week2/@Hundred))*
(1.0+(week3/@Hundred)))-1)*@Hundred
_____________
Code for TallyGenerator
May 6, 2009 at 3:12 am
Sergiy,
FLOAT worked well. Can you tell me why my code failed to give the accurate result?
Also i have to convience my manager to alter the table to FLOAT data type.
So i need answer for the below questions.
1) How it is giving exact value?
2) How sqlserver handles DECIMAL & FLOAT datatype.
3) If i use DECIMAL datatype,why the UPDATE statement rounding the value? what is the reason?
4) Which will give good accuracy? i.e DECIMAL or FLOAT.
karthik
May 6, 2009 at 4:54 am
Read this article by Hugo Kornelis to get some answers: http://sqlblog.com/blogs/hugo_kornelis
May 6, 2009 at 11:01 am
I tried the below code
DECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(16,8);
SET @Fixed1 = 5444.32;
SET @Fixed2 = 12121.03;
SET @Fixed3 = @Fixed1 * @Fixed2;
SELECT @Fixed3
I got the truncation error.
To multiply two decimal(8,4) values, we need decimal(16,8) to sotre the resut. Right?
If yes, then why it is throwing the error message?
select 5444.32*12121.03
gave the correct result.
if i multiply two decimal(16,8) values, then how much space i need? decimal(32,16). Correct?
karthik
May 6, 2009 at 11:10 am
karthikeyan (5/6/2009)
I tried the below codeDECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(16,8);
SET @Fixed1 = 5444.32;
SET @Fixed2 = 12121.03; <-- Problem is here. decimal(8,4) means you only have room for 4 positions to the left of the . as well
SET @Fixed3 = @Fixed1 * @Fixed2;
SELECT @Fixed3
I got the truncation error.
To multiply two decimal(8,4) values, we need decimal(16,8) to sotre the resut. Right?
If yes, then why it is throwing the error message?
select 5444.32*12121.03
gave the correct result.
if i multiply two decimal(16,8) values, then how much space i need? decimal(32,16). Correct?
See the bold comment above.
May 6, 2009 at 11:22 am
For what it is worth, the following also returns the "correct" answer based on the original post:
create table #temp
(
week1 decimal(16,8),
week2 decimal(16,8),
week3 decimal(16,8),
gross decimal(16,8)
)
insert into #temp
select -9.12000000,-11.03000000,5.61690000,0
declare @a1 decimal(16,8),@a2 decimal(16,8),@a3 decimal(16,8);
update #temp set
@a1 = (1.0+(week1/100.000)),
@a2 = (1.0+(week2/100.000)),
@a3 = (1.0+(week3/100.000)),
gross = ((@a1 * @a2 * @a3)-1) * 100
select * from #temp
drop table #temp
May 6, 2009 at 11:27 am
I'd encourage you to read the BOL information about the FLOAT data type before you change the table. Pay careful attention to the fact that it is an approximate representation of the data and needs to be handled with special care.
May 6, 2009 at 11:37 am
Karthik:
There is a course in most graduate & undergraduate math & CompSci programs called "Numerical Analysis", have you taken it yet? I ask because it is really all about exactly this kind of thing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2009 at 12:58 pm
BS''D
Hi,
With respect to Numerical Analysis, there are things taught that I have not yet seen been discussed in this thread.
1. Extra zeros to the right of a decimal point are ignored so 100.0 and 10.000 are the same. If the variable is cast as a float or decimal, the 100 can be used instead of 100.0.
2. It is already better to multiply then divide. Hence, multiply by 0.01 instead of divide by 100.0.
Also, with respect to the definition of 4 decimals or 8 decimals, one topic you should investigate is machine error value. This has to do with the smallest value the computer can handle.
Bye,
Dr. Jerome Braunstein, Ph.D.
Atlas Development Corporation
26679 West Agoura Road, Suite 200
Calabasas, CA 91302
Phone: 1 (818) 340-7080
Direct: 1 (818) 224-6237
Toll Free: 1 (800) 333-0070
Fax: 1 (818) 340-7079
May 7, 2009 at 2:51 pm
jbraunstein (5/7/2009)
1. Extra zeros to the right of a decimal point are ignored so 100.0 and 10.000 are the same. If the variable is cast as a float or decimal, the 100 can be used instead of 100.0.
Not quite true. In this case notation format is used for implicit declaration of the constant.
"100.0" declares a constant of DECIMAL(4,1), "100.00" declares DECIMAL(5,2).
If you go through proper variable declaration step then yes, notation of the valuue assigned does not really matter. But it's not the case for OP's script.
2. It is already better to multiply then divide. Hence, multiply by 0.01 instead of divide by 100.0.
Multiply is not any better than divide.
Difference comes from difference in implicitly declared data types.
Declare them both as, say, MONEY and try to find any difference between dividing and multiplying.
_____________
Code for TallyGenerator
May 7, 2009 at 9:25 pm
Lynn,
Yes.It will work.. But I have more than one row in the table,so i can't use local variable here, becuase it lead us to use 'LOOP'.
karthik
May 7, 2009 at 9:27 pm
RBarryYoung (5/6/2009)
Karthik:There is a course in most graduate & undergraduate math & CompSci programs called "Numerical Analysis", have you taken it yet? I ask because it is really all about exactly this kind of thing.
Umm...Yes..RbarryYoung...but i studied it from exam perspective :)...so i don't remember the concepts...Anyway i am going to brush up the concepts.
karthik
May 7, 2009 at 9:32 pm
Lynn Pettis (5/6/2009)
karthikeyan (5/6/2009)
I tried the below codeDECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(16,8);
SET @Fixed1 = 5444.32;
SET @Fixed2 = 12121.03; <-- Problem is here. decimal(8,4) means you only have room for 4 positions to the left of the . as well
SET @Fixed3 = @Fixed1 * @Fixed2;
SELECT @Fixed3
I got the truncation error.
To multiply two decimal(8,4) values, we need decimal(16,8) to sotre the resut. Right?
If yes, then why it is throwing the error message?
select 5444.32*12121.03
gave the correct result.
if i multiply two decimal(16,8) values, then how much space i need? decimal(32,16). Correct?
See the bold comment above.
Sorry! I am not getting you...
select 5444.32*12121.03
output:
65990766.0496
then why i need to worry about the digits after the decimal point.
To multiply two decimal(8,4) values, we need decimal(16,8) to store the resut.
Is this right?
karthik
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply