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
Title | # Comments | Views | Activity |
---|---|---|---|
query question | 4 | 26 | |
Update foreign key reference after insert | 9 | 30 | |
SQL Select Query help | 3 | 29 | |
Help with stripping out character in SQL LEFT/RIGHT/REPLACE | 2 | 11 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
8 Experts available now in Live!