SQL Function - Variable setting itself to random value

Question

I have created the below function, and it seems to at random to change the @FCST and @QTY values to random selections. Here is an example of the program in debug returning a bad value for @FCST. As @FCST was at 9.53, I expect it to return 9.53. enter image description here

CREATE FUNCTION dbo.LTBADJ
(@MAT VARCHAR(30),@LCUT DATE, @QS DATE,@STAT FLOAT, @MCA FLOAT)
RETURNS FLOAT
AS BEGIN
DECLARE @HD DATE
DECLARE @ED DATE
DECLARE @FCST FLOAT
DECLARE @QTY FLOAT 
DECLARE @YRS FLOAT

SET @ED = 
(SELECT TOP 1 [EO_END_DATE] FROM [dbo].[EO_LTB]
WHERE [W_PART_NUMBER] = @MAT
AND [APPROVED_DATE] <= @LCUT
ORDER BY [EO_END_DATE] DESC)
SET @HD =DATEADD(YEAR,2,@QS)
SET @FCST = (CASE WHEN @MCA <= @STAT THEN @MCA ELSE @STAT END)
SET @FCST = (CASE WHEN @FCST <.5 THEN .5 ELSE @FCST END)
SET @YRS = (DATEDIFF(DAY,@HD,@ED) + 730)/365
SET @QTY = @FCST * @YRS
SET @QTY = (CASE WHEN @ED<=@HD THEN 0 ELSE @QTY END)
RETURN @QTY
END ;

EDIT: Example of @QTY incorrectly calculated enter image description here

hmm... that's not 9.53.. that's 9.53 x 10^-2 = 9.53 x 0.01 .. which is 0.0953 — Brett Caswell
– Brett Caswell, Commented Nov 24, 2014 at 21:52
and the result is 5 x 10^-1 = 5 x 0.1 = 0.5 .. which is what you specified it to be when it's less that 0.5 — Brett Caswell
– Brett Caswell, Commented Nov 24, 2014 at 21:53
So yeah, I was reading the values of the floats incorrectly. Thank you. — Robert Pulido
– Robert Pulido, Commented Nov 24, 2014 at 22:35

JohnLBevan · Accepted Answer · 2014-11-24 21:59:04Z

1

All seems correct - you can see what's going on by running the below SQL. I suspect the confusion's the e-002 and e-001 on the end of the numbers; that's just SQL trying to display the values of floats; because of how floating point arithmetic works, some numbers are calculated slightly out from what you'd expect, then to try to make them readable SQL will display the numbers with an exponent.

declare @MAT VARCHAR(30) = '50309120000W'
,@LCUT DATE = '2014-10-26'
, @QS DATE =  '2014-10-27'
,@STAT FLOAT = 9.5399999999999999e-002
, @MCA FLOAT = 9.5399999999999999e-002


DECLARE @HD DATE
DECLARE @ED DATE
DECLARE @FCST FLOAT
DECLARE @QTY FLOAT 
DECLARE @YRS FLOAT

SET @ED = 
(
    SELECT TOP 1 [EO_END_DATE] 
    FROM (SELECT '2019-02-28' [EO_END_DATE], @MAT [W_PART_NUMBER], @LCUT [APPROVED_DATE] ) X
    WHERE [W_PART_NUMBER] = @MAT
    AND [APPROVED_DATE] <= @LCUT
    ORDER BY [EO_END_DATE] DESC
)
SET @HD =DATEADD(YEAR,2,@QS)


SET @FCST = (CASE WHEN @MCA <= @STAT THEN @MCA ELSE @STAT END)
SELECT @FCST

SET @FCST = (CASE WHEN @FCST <.5 THEN .5 ELSE @FCST END)
SELECT @FCST

SET @YRS = (DATEDIFF(DAY,@HD,@ED) + 730)/365
SET @QTY = @FCST * @YRS
SELECT @FCST, @YRS, @QTY

SET @QTY = (CASE WHEN @ED<=@HD THEN 0 ELSE @QTY END)
SELECT @QTY, @ED, @HD

answered Nov 24, 2014 at 21:59

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Pulido Over a year ago

So yeah, I was reading the values of the floats incorrectly. Thank you.

Collectives™ on Stack Overflow

SQL Function - Variable setting itself to random value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related