I have been trying to build below logic
Declare @OT float Declare @Remark varchar(100) set @OT = 9.52 set @Remark = '1-25 hours' select case when @Remark='1-25 hours' then (FLOOR((@OT * 100)/100)*150 + (((@OT * 100) % 100.00) * (150/60))) --@OT*150 when @Remark='26-35 hours' then (25*150) + ((FLOOR((@OT-25.00) * 100)/100 )*200 + (((@OT-25.00) * 100)%100) * (200/60)) --((@OT-25.00)* 200) when @Remark='36-45 hours' then (25*150) + (10*200) + ( FLOOR(((@OT-35.00)*100)/100)*250 + (((@OT-35.00) * 100)%100) * (250/60)) --((@OT-35.00) * 250) when @Remark='46-55 hours' then (25*150) + (10*200) + (10* 250) + (FLOOR(((@OT-45.00) *100)/100)*350 + (((@OT-45.00) * 100)%100) * (350/60)) --((@OT-45.00) * 350) when @Remark='56-65 hours' then (25*150) + (10*200) + (10* 250) + (10 * 350) + (FLOOR(((@OT-55.00)*100)/100)*550 + (((@OT-55.00) * 100)%100) * (550/60)) --((@OT-55.00) * 550) when @Remark='aboce 65 hours' then (25*150) + (10*200) + (10* 250) + (10 * 350) + (10 * 550) + (FLOOR(((@OT-65.00)*100)/100)*650 + (((@OT-65.00) * 100)%100) * (650/60)) --((@OT-65.00)* 650) end
But i am getting below error
Msg 402, Level 16, State 1, Line 19 The data types float and numeric are incompatible in the modulo operator.
Advertisement
Answer
Just cast to INT
before performing the modulo
:
Declare @OT float Declare @Remark varchar(100) set @OT = 9.52 set @Remark = '1-25 hours' SELECT FLOOR((@OT * 100)/100)*150 + ((CAST(@OT * 100 AS INT) % 100.00) * (150/60))
Here is the complete code:
select case when @Remark='1-25 hours' then (FLOOR((@OT * 100)/100)*150 + ((CAST(@OT * 100 AS INT) % 100.00) * (150/60))) --@OT*150 when @Remark='26-35 hours' then (25*150) + ((FLOOR((@OT-25.00) * 100)/100 )*200 + (CAST((@OT-25.00) * 100 AS INT)%100) * (200/60)) --((@OT-25.00)* 200) when @Remark='36-45 hours' then (25*150) + (10*200) + ( FLOOR(((@OT-35.00)*100)/100)*250 + (CAST((@OT-35.00) * 100 AS INT)%100) * (250/60)) --((@OT-35.00) * 250) when @Remark='46-55 hours' then (25*150) + (10*200) + (10* 250) + (FLOOR(((@OT-45.00) *100)/100)*350 + (CAST((@OT-45.00) * 100 AS INT)%100) * (350/60)) --((@OT-45.00) * 350) when @Remark='56-65 hours' then (25*150) + (10*200) + (10* 250) + (10 * 350) + (FLOOR(((@OT-55.00)*100)/100)*550 + (CAST((@OT-55.00) * 100 AS INT)%100) * (550/60)) --((@OT-55.00) * 550) when @Remark='aboce 65 hours' then (25*150) + (10*200) + (10* 250) + (10 * 350) + (10 * 550) + (FLOOR(((@OT-65.00)*100)/100)*650 + (CAST((@OT-65.00) * 100 AS INT)%100) * (650/60)) --((@OT-65.00)* 650) end
The issue is you are not allowed to perform modulo
, because:
dividend must be a valid expression of any one of the data types in the integer and monetary data type categories, or the numeric data type.
and even you multiply with 100
, you are not changing the type.