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 |
---|---|---|---|
SQL Query Syntax Join | 4 | 39 | |
ORA-00923: FROM keyword not found where expected | 3 | 66 | |
How can I update a 2nd table with what is inserted into the 1st? | 5 | 38 | |
sql server concatenate fields | 10 | 36 |
Join the community of 500,000 technology professionals and ask your questions.