March 25, 2009 at 11:18 am
usarian (3/25/2009)
Point taken on the float bit. In your example one does get the correct result, HOWEVER:DECLARE @a AS FLOAT
SET @a = 1
SELECT @a
--VS
DECLARE @a AS DECIMAL
SET @a = 1
SELECT @a
For me, that's an obvious deal breaker.
my rule of thumb:
NEVER use float, unless your smart enought to know when it's going to work right, because most of the time it wont.
OK. Well MY rule of thumbs are:
1) Don't believe everything that you hear, especially on the internet.
2) Don't accept uninformed opinion at face value
3) Base conclusions on reason and evidence, not claims and emotions
4) Test everything
So let's test this, shall we?:
DECLARE @a AS FLOAT
SET @a = 1
SELECT @a
go
--VS
DECLARE @a AS DECIMAL
SET @a = 1
SELECT @a
go
/* Results:
----------------------
1
---------------------------------------
1
*/
Hmmm, apparently your deals break for no obvious reason.
[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]
March 25, 2009 at 11:52 am
Scott T (1/16/2009)
--With simple calculation we don't care about data-type (float or decimal)--====================================
--When we start calculation like the following one, we should use decimal
-- Let's see example
--====================================
DECLARE @APR ??????? (18,3)
--------------------------------------
SET @APR=.074;
DECLARE @Months INT --how to break up the annual percentage
SET @Months=12;
DECLARE @Period INT -- (i.e. at what point in the future do you want to know the value)
SET @Period=36;
DECLARE @PMT DECIMAL(18,2) --monthly payment
SET @PMT=1889;
DECLARE @PV DECIMAL(18,2) --present value or starting point
SET @PV=132930;
DECLARE @EndPeriod INT --whether compounding occurs at the beginning or the end of the period
SET @EndPeriod=0;
DECLARE @FV DECIMAL(36,6)
SELECT @FV=(@PV*POWER(1+@APR/@Months,(@Period-@EndPeriod))
-@PMT*(POWER(1+@APR/@Months,@Period) -1)/(@APR/@Months))*POWER(1+@APR/@Months,@EndPeriod)
SELECT @FV
--=====================
--In Case When we declare with FLOAT
---------------------------------------
DECLARE @APR FLOAT
---------------------------------------
-- RESULT
---------------------------------------
89976.668128
--=====================
--=====================
--=====================
--In Case When we declare with DECIMAL
---------------------------------------
DECLARE @APR DECIMAL(18,3)
---------------------------------------
-- RESULT
---------------------------------------
89976.668104
--=====================
As you can see the results it's not matching. 😉 Well it's up to you know to make a decision.
-------------------------------------------------------------------
Only from my personal experience, if there is possibility for mistake it will happens, eventually.
-------------------------------------------------------------------
GOOD LUCK 🙂
Actually, if you apply the rule regarding significant digits you probably should round the results of both calculations to the nearest hundreths (or perhaps thousandths), and if you do that, both values are the same.
April 3, 2009 at 8:26 am
RBarryYoung (3/25/2009)
usarian (3/25/2009)
Point taken on the float bit. In your example one does get the correct result, HOWEVER:DECLARE @a AS FLOAT
SET @a = 1
SELECT @a
--VS
DECLARE @a AS DECIMAL
SET @a = 1
SELECT @a
For me, that's an obvious deal breaker.
my rule of thumb:
NEVER use float, unless your smart enought to know when it's going to work right, because most of the time it wont.
OK. Well MY rule of thumbs are:
1) Don't believe everything that you hear, especially on the internet.
2) Don't accept uninformed opinion at face value
3) Base conclusions on reason and evidence, not claims and emotions
4) Test everything
So let's test this, shall we?:
DECLARE @a AS FLOAT
SET @a = 1
SELECT @a
go
--VS
DECLARE @a AS DECIMAL
SET @a = 1
SELECT @a
go
/* Results:
----------------------
1
---------------------------------------
1
*/
Hmmm, apparently your deals break for no obvious reason.
Doggone it, I had deleted that post!
Anyway, I posted it in the first place becuase my result came back .999999999 for float the first time I ran it, but not since.. I think it might be a difference between SQL Server 2000 and 2005, but I don't know.
BUT since there's people responding on this topic still, I tried the NPER function somebody posted above, and it's not working for me. I'm trying to duplicate Excel's NPER(rate,payment,presentvalue,futurevalue) using negative numbers for payments and futurevalue = 0. Any ideas or pointers?
April 3, 2009 at 9:08 am
usarian (4/3/2009)
...BUT since there's people responding on this topic still, I tried the NPER function somebody posted above, and it's not working for me. I'm trying to duplicate Excel's NPER(rate,payment,presentvalue,futurevalue) using negative numbers for payments and futurevalue = 0. Any ideas or pointers?
There were multiple versions posted I think. Please post the version that you are using and let us know what specifically is wrong with the results.
[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]
October 14, 2020 at 3:28 pm
Bingo, finally found accurate sql code for a beginning period payment. Thanks ambgoval.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply