# SQLSERVER BUG on CASE WHEN STATEMENT

• ### Question

• select left(v.kode_barang,6)as kode_barang,nama_barang,kode_gudang,kode_wilayah,
v.qty,u.qty_a,
case  when (v.qty >=0) then
case when (u.qty_a=1) then v.qty else
case   when  (v.qty>=u.qty_a) and (u.qty_a<>1) then
floor(v.qty/u.qty_a) else 0 end end
when v.qty<0 then
case when  v.qty*(-1) >=u.qty_a then floor(v.qty*(-1)/u.qty_a)*(-1) else 0 end
end as QTY_A,

when V.qty is 82.70 and u.qty_a is 1. why result is 83 ?????

Monday, March 26, 2018 3:43 PM

### All replies

• It works for me. You can combine the second and the third CASE statement together:

```DECLARE @d decimal(5, 2) = 82.70;
DECLARE @i int = 1;
SELECT @d, @i;
SELECT
CASE
WHEN @d >= 0 THEN
CASE
WHEN @i = 1 THEN @d
WHEN @d >= @i THEN FLOOR(@d/@i)
ELSE 0
END
WHEN @d < 0 THEN
CASE
WHEN @d * (-1) >= @i THEN FLOOR(@d * (-1) / @i) * (-1)
ELSE 0
END
END```

A Fan of SSIS, SSRS and SSAS

Monday, March 26, 2018 4:29 PM
• i guess thats because you're returning an integer in the other condition. So its getting rounded

try like

```select left(v.kode_barang,6)as kode_barang,nama_barang,kode_gudang,kode_wilayah,
v.qty,u.qty_a,
case  when (v.qty >=0) then
case when (u.qty_a=1) then v.qty else
case   when  (v.qty>=u.qty_a) and (u.qty_a<>1) then
floor(v.qty/u.qty_a) else 0.00 end
end
when v.qty<0 then
case when  v.qty*(-1) >=u.qty_a then floor(v.qty*(-1)/u.qty_a)*(-1) else 0.00 end
end as QTY_A,```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

• Proposed as answer by Monday, March 26, 2018 5:24 PM
Monday, March 26, 2018 4:33 PM
• What data type is u.qty_a? If it is float, it may only seem that is 1, but there may be hidden decimals, which you will see if you subtract 1 from it. In this case, it seems that you end up in floor(v.qty / u.qty_a) which would be 83.

When you work with float, you always need a fuzzy margin. I would write the condition as:

abs(u.qty_a - 1) < 1E-10

Same goes for the inequality later.

(The choise of -10 for the exponent is somewhat arbitrary.)

Monday, March 26, 2018 9:36 PM