calculation sql

Question

my table stored with

TABLE [dbo].[table_A](
ID nvarchar not null,
[expected_amouunt] nvarchar NULL,
[actual_amount] nvarchar NULL,

) ON [PRIMARY]
GO

ID expected_amouunt actual_amount

---

1 0 17.5

i would like to extract from table_A with the following output

ID DR CR

---

1 (display blank) 17.5

DR column:(when actual_amount - expected_amouunt)<0 then display (when actual_amount - expected_amouunt) else ''
CR column : (when actual_amount - expected_amouunt)>0 then display (when actual_amount - expected_amouunt) else ''

select ID,
case when
cast (cast (([actual_amount],'0') 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 [DR],
case when
cast (cast (([actual_amount],'0') 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 [CR]
from table_A;

i don't know why it is always return 0.00 for "DR" even the (when actual_amount - expected_amouunt) > 0, if it greater then zero, it should fall into "Else" , so it should display blank ''.

how to fix it?

Answer 1

Just to be clear.

The CASE statement MUST return the same data type for all values. Your CASE statement currently returns THEN MONEY ELSE VARCHAR, that is not possible.

If you want blank as a possible value, you must return VARCHAR and VARCHAR in all results of the CASE statement.

Answer 2

If you are interested in some reasonable simplifications, then check this query too:

select ID, 
    case when d < 0 then s else '' end as DR,
    case when d > 0 then s else '' end as CR
from table_A
cross apply (values (cast(isnull(actual_amount, 0) as money) - isnull(expected_amount, 0))) t1(d)
cross apply (values (cast(d as varchar(max)))) t2(s)

Answer 3

Hi，@nononame2021
Welcome to Microsoft T-SQL Q&A Forum!
You need to change the data type of the ‘then’ clause from ‘money’ to ‘nvarchar’, otherwise ‘ ’ will always be converted to ‘money’ type.
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 ID,  
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 [DR],  
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 [CR]  
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 4

Hi @nononame2021

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