sql server calculate in 'case when'

Question

I can't calculate in case when sql. it always return wrong value

actual_amount= 17.5
expexted_amount=0

select case when cast (cast ([actual_amount] as float) as money) - cast (cast ([expected_amouunt] as float) as money) < 0 then
cast (cast ([actual_amount] as float) as money) - cast (cast ([expected_amouunt] as float) as money)
else '' end as test from table_A;
17.5 -0 ,it is greater than zero, but my sql always return 0 as result

I try to amend sql to the following sql, it is correct, it return '' (blank) as expect, why I can't calculate in the true condition (after then ....)

select case when [actual] - [expected] < 0 then 'yes' else '' end as test from table_A;

how to calculate in case when caluse ?

Answer 1

Hi，@nononame2021
Welcome to Microsoft T-SQL Q&A Forum!
Please check this:

DECLARE @test_table TABLE  
(  
ID nvarchar not null,  
expected_amount nvarchar(25) NULL,  
actual_amount nvarchar(25) NULL  
)  
      
INSERT INTO @test_table VALUES  
 ( 1, 0, 17.5)  
  
select case when cast (cast ([actual_amount] as float) as money) - cast (cast ([expected_amount] as float) as money) < 0   
       then cast((cast (cast ([actual_amount] as float) as money) - cast (cast ([expected_amount] as float) as money))as nvarchar)  
       else ''  
       end as test from @test_table;  

Output：

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @nononame2021

I think there needs to be a varchar() after ‘then’