Can anyone explain this....

Question

Post reply

Can anyone explain this....

david.earl

Valued Member

Points: 64
More actions
August 24, 2016 at 1:19 am

#329671

I have a formula that calculates a Net Promoter Score.
The formula is simply (Promoters / Total Returns) - (Detractors / Total Returns)
I've found an anomaly when running this in SQL:
DECLARE @COUNT FLOAT, @PROMOTER FLOAT, @DETRACTOR FLOAT
SET @COUNT = 10
SET @PROMOTER = 7
SET @DETRACTOR = 2
-- Proves the values being used:
SELECT (@PROMOTER/@COUNT) as Promoters
SELECT (@DETRACTOR/@COUNT) as Detractors
-- Correctly gives the result as 0.5:
SELECT (@PROMOTER/@COUNT) - (@DETRACTOR/@COUNT)as NPS
-- Trying to find results above the target of 0.5, but this doesn't give the expected result!!!!!:
SELECT CASE WHEN (@PROMOTER/@COUNT) - (@DETRACTOR/@COUNT)>=0.5 THEN 1 ELSE 0 END as WTF
-- Just comparing the result against the target, WTF!!!! :
select ((@PROMOTER/@COUNT) - (@DETRACTOR/@COUNT)) - 0.5 as WTF2
Can someone please explain what is going on?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

durga.palepu Hall of Fame Points: 3530 More actions · Answer 1

I have verified this and it works fine for me, refer to the attachment.

david.earl Valued Member Points: 64 More actions · Answer 2

Someone local came back to me on this.

Its the difference between a float and a decimal.

I thought a float handled everything fine but apparently it doesn't really work for anything between 0 and 1.

Anything above 1 is fine.

durga.palepu Hall of Fame Points: 3530 More actions · Answer 3

Thanks for the update.

So this test case worked for you with float datatype or not?

david.earl Valued Member Points: 64 More actions · Answer 4

Hi,

The results of your test show exactly what I was getting at. The final result shows 0 while the logic suggests it should be 1.

essentially if its >= 0.5 then 1 else 0. As its equal to 0.5 it should show 1.

If I change the data type from float to decimal it works.

I've never came across this before and it had me scratching my head all day yesterday.

Just read an article about the difference between a float and a decimal and how the floating point works for numbers between 0 - 1 and now my head hurts.

Cheers for the reply.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

david.earl (8/24/2016)
Hi,
The results of your test show exactly what I was getting at. The final result shows 0 while the logic suggests it should be 1.
essentially if its >= 0.5 then 1 else 0. As its equal to 0.5 it should show 1.
If I change the data type from float to decimal it works.
I've never came across this before and it had me scratching my head all day yesterday.
Just read an article about the difference between a float and a decimal and how the floating point works for numbers between 0 - 1 and now my head hurts.
Cheers for the reply.

You might expect that subtracting 0.5 from your answer would yield 0, but it yields something like -0.00000000000000006. As a fraction of the circumference of our dear planet, it's about one 25 thousandth of the thickness of a human hair, so you could nod that fraction of the circumference without straining your neck. You wouldn't have to take a step. It's close, but not exact. Precise, but not accurate. If you don't require the precision offered by FLOAT datatypes but you do want the accuracy (and hence rather more predictable arithmetic) offered by NUMERIC, then the choice is easy.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

durga.palepu (8/24/2016)
I have verified this and it works fine for me, refer to the attachment.

Just post code in the future, please. No one can execute a JPG.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

The FLOAT datatype isn't based on the DECIMAL numbering system although the result is displayed as such. FLOAT is based on BINARY representations of DECIMAL numbers and, when you do things such as division, BINARY division can raise some hell with the "accuracy" that a lot of people expect from a 4 function calculator. Most 4 function calculators to the math behind the scenes using 15 places to the right of the decimal place and only display/use up to 13 places as a result.

FLOAT "only" has 15 places of precision including all places. If you have used 4 places to the left of the decimal point, only 11 places will be used to the right of it.

FLOAT can be useful but it takes a lot of folks by surprise because of things like BINARY division and because certain decimal expectations can't be accurately represented by BINARY fractions. It also doesn't help that MS tried to satisfy folks complaints about FLOAT by sometimes changing the display of the numbers rather than displaying the actual "imprecise" number.

When I need to be as accurate as possible for DIVISION and similar "decimal place intensive" calculations, I'll generally just use DECIMAL(38,xx) where 38-xx represents the largest number of digits to the left of the decimal point that I expect. For values within the range of the INTEGER datatype, I'll use DECIMAL(38,28), which leaves 10 digits to the left of the decimal point.

Also, since the DIVISOR in the given problem is the same for both sides of the subtraction, why not simplify the formula to (@PROMOTER-@DETRACTOR)/@COUNT?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)