Try adding ELSE '!!' after when '> 500000' then 'k' and see if you get any !! in your results. If so, then you are getting some values less than 101 OR you are getting NULL values as inputs.
ALTER FUNCTION [dbo].[fn_deductible]
(
-- Add the parameters for the function here
@DEDUCTIBLEAMT VARCHAR(7)
)
RETURNS varchar (1)
AS
BEGIN
-- Declare the return variable here
DECLARE @DEDUCTIBLE varchar(1)
DECLARE @DEDUCTIBLETEMP NUMERIC (7,0)
-- Add the T-SQL statements to compute the return value here
SET @DEDUCTIBLETEMP = CAST(@DEDUCTIBLEAMT AS NUMERIC (7,0))
set @DEDUCTIBLE = case
when @DEDUCTIBLETEMP > 100 and @DEDUCTIBLETEMP <= 250 then '4'
when @DEDUCTIBLETEMP > 500 and @DEDUCTIBLETEMP <= 750 then '6'
when @DEDUCTIBLETEMP > 750 and @DEDUCTIBLETEMP <= 1000 then '7'
when @DEDUCTIBLETEMP > 1000 and @DEDUCTIBLETEMP <= 2500 then '8'
when @DEDUCTIBLETEMP > 2500 and @DEDUCTIBLETEMP <= 5000 then '9'
when @DEDUCTIBLETEMP > 5000 and @DEDUCTIBLETEMP <= 10000 then 'A'
when @DEDUCTIBLETEMP > 10000 and @DEDUCTIBLETEMP <= 25000 then 'B'
when @DEDUCTIBLETEMP > 25000 and @DEDUCTIBLETEMP <= 50000 then 'C'
when @DEDUCTIBLETEMP > 50000 and @DEDUCTIBLETEMP <= 75000 then 'D'
when @DEDUCTIBLETEMP > 75000 and @DEDUCTIBLETEMP <= 100000 then 'E'
when @DEDUCTIBLETEMP > 100000 and @DEDUCTIBLETEMP <= 200000 then 'F'
when @DEDUCTIBLETEMP > 200000 and @DEDUCTIBLETEMP <= 250000 then 'G'
when @DEDUCTIBLETEMP > 250000 and @DEDUCTIBLETEMP <= 300000 then 'H'
when @DEDUCTIBLETEMP > 300000 and @DEDUCTIBLETEMP <= 400000 then 'I'
when @DEDUCTIBLETEMP > 400000 and @DEDUCTIBLETEMP <= 500000 then 'J'
when @DEDUCTIBLETEMP > 500000 then 'k'
end
RETURN @DEDUCTIBLE
END
ALTER FUNCTION [dbo].[fn_deductible2]
(
-- Add the parameters for the function here
@DEDUCTIBLEAMT VARCHAR(7)
)
RETURNS varchar (1)
AS
BEGIN
-- Declare the return variable here
DECLARE @DEDUCTIBLECODE varchar(1)
DECLARE @DeductibleAmtINT int
SET @DeductibleAmtINT = IsNull(Cast(@DeductibleAmt as Int), -1)
-- Add the T-SQL statements to compute the return value here
select @DeductibleCode = CASE
WHEN @DeductibleAmtInt < 0 THEN 'X'
WHEN @DeductibleAmtInt < 50 THEN '2'
WHEN @DeductibleAmtInt < 100 THEN '3'
WHEN @DeductibleAmtInt < 250 THEN '4'
WHEN @DeductibleAmtInt < 500 THEN '5'
WHEN @DeductibleAmtInt < 750 THEN '6'
WHEN @DeductibleAmtInt < 1000 THEN '7'
WHEN @DeductibleAmtInt < 2500 THEN '8'
WHEN @DeductibleAmtInt < 5000 THEN '9'
WHEN @DeductibleAmtInt < 10000 THEN 'A'
WHEN @DeductibleAmtInt < 25000 THEN 'B'
WHEN @DeductibleAmtInt < 50000 THEN 'C'
WHEN @DeductibleAmtInt < 75000 THEN 'D'
WHEN @DeductibleAmtInt < 100000 THEN 'E'
WHEN @DeductibleAmtInt < 200000 THEN 'F'
WHEN @DeductibleAmtInt < 250000 THEN 'G'
WHEN @DeductibleAmtInt < 300000 THEN 'H'
WHEN @DeductibleAmtInt < 400000 THEN 'I'
WHEN @DeductibleAmtInt < 500000 THEN 'J'
ELSE 'K'
END
-- Return the result of the function
RETURN @DEDUCTIBLECODE
END
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
how to fix this error | 14 | 57 | |
SQL Server 2008 R2 - Updating Table/Fields Documentation | 3 | 73 | |
Help with SQL field formatting | 3 | 20 | |
Help Extract Specific in SQL | 8 | 27 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!