CODEWITHSUNDEEP
functiont-sqlvariables
Robert Pulido
Robert Pulido

Reputation: 81

SQL Function - Variable setting itself to random value

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

Upvotes: 0

Views: 446

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24430

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

Upvotes: 1

Related Questions