Case statement with between not calculating correctly

Question

Case statement with between not calculating correctly

gissah

SSCommitted

Points: 1815
More actions
June 10, 2015 at 8:25 am

#297601

Can someone please look at this code and tell me what I am doing wrong. the middle 2 conditions are not calculating correctly. I am getting zero as the results.
Issue part:
when Ratio/100 BETWEEN 0.94 AND 0.90 THEN Cast(Amt_PastDue * 0.004 as numeric(18,2))
when Ratio/100 BETWEEN 0.89 AND 0.85 THEN Cast(Amt_PastDue * 0.005 as numeric(18,2))
Full code:
Case when Ratio/100 > 0.94 THEN Cast(Amt_PastDue * 0.003 as numeric(18,2))
--ELSE CASE
when Ratio/100 BETWEEN 0.94 AND 0.90 THEN Cast(Amt_PastDue * 0.004 as numeric(18,2))
when Ratio/100 BETWEEN 0.89 AND 0.85 THEN Cast(Amt_PastDue * 0.005 as numeric(18,2))
--ELSE CASE
when Ratio/100 < 0.85 and Ratio != 0 THEN Cast(Amt_PastDue * 0.006 as numeric(18,2))
ELSE
0.00
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

gissah (6/10/2015)
Can someone please look at this code and tell me what I am doing wrong. the middle 2 conditions are not calculating correctly. I am getting zero as the results.
Issue part:
when Ratio/100 BETWEEN 0.94 AND 0.90 THEN Cast(Amt_PastDue * 0.004 as numeric(18,2))
when Ratio/100 BETWEEN 0.89 AND 0.85 THEN Cast(Amt_PastDue * 0.005 as numeric(18,2))
Full code:
Case when Ratio/100 > 0.94 THEN Cast(Amt_PastDue * 0.003 as numeric(18,2))
--ELSE CASE
when Ratio/100 BETWEEN 0.94 AND 0.90 THEN Cast(Amt_PastDue * 0.004 as numeric(18,2))
when Ratio/100 BETWEEN 0.89 AND 0.85 THEN Cast(Amt_PastDue * 0.005 as numeric(18,2))
--ELSE CASE
when Ratio/100 < 0.85 and Ratio != 0 THEN Cast(Amt_PastDue * 0.006 as numeric(18,2))
ELSE
0.00
END

-- The values for BETWEEN were the wrong way around. Place smaller value value first

-- i.e. BETWEEN smaller value AND larger value

SELECT CASE

WHEN Ratio/100 > 0.94 THEN Cast(Amt_PastDue * 0.003 as numeric(18,2))

when Ratio/100 BETWEEN 0.90 AND 0.94 THEN Cast(Amt_PastDue * 0.004 as numeric(18,2)) --

when Ratio/100 BETWEEN 0.85 AND 0.89 THEN Cast(Amt_PastDue * 0.005 as numeric(18,2))

when Ratio/100 < 0.85 and Ratio != 0 THEN Cast(Amt_PastDue * 0.006 as numeric(18,2))

ELSE 0.00 END

FROM (SELECT Ratio = 86.00, Amt_PastDue = 10000) d

-- If the column [Ratio] is INT then the code will fail. Cast it to a .nn type.

SELECT CASE

WHEN Ratio/100 > 0.94 THEN Cast(Amt_PastDue * 0.003 as numeric(18,2))

when Ratio/100 BETWEEN 0.90 AND 0.94 THEN Cast(Amt_PastDue * 0.004 as numeric(18,2)) --

when Ratio/100 BETWEEN 0.85 AND 0.89 THEN Cast(Amt_PastDue * 0.005 as numeric(18,2))

when Ratio/100 < 0.85 and Ratio != 0 THEN Cast(Amt_PastDue * 0.006 as numeric(18,2))

ELSE 0.00 END

FROM (SELECT Ratio = 86, Amt_PastDue = 10000) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Andreas P. Williams SSCrazy Points: 2487 More actions · Answer 2

Hi!

What is the data type of Ratio? If it is "int" and between 0 and 100 the result of the division will always be 0. Try and replace "Ratio/100" by "Ratio/100.0" to force a conversion to "float" data type.