Case-Else for decimal values in SQL

Question

Hi, I have a table named 'Table1' from which i need to retrieve an 'Amount' as per one condition.If the 'Currency' is some 'Dollar',i want the amount to be displayed in 1 decimal place.if the currency is other than 'Dollar',the amount should in 2 decimal places.I have used the case-else condition for this.But it is not checking the condition,but only showing the bigger decimal place.My query is as follows.

Select InvNo,
Case Currency when 'Dollar' then
    Convert(decimal(18,1),Amount)
Else
    Convert(decimal(18,2),Amount)
End
'Amount'
From Table1 where ID=1

My Output is as follows:

InvNo Amount

236 5200.26

---

If i reverse the condition,i get the same output.

Select InvNo,
Case Currency when 'Dollar' then
    Convert(decimal(18,2),Amount)
Else
    Convert(decimal(18,1),Amount)
End
'Amount'
From Table1 where ID=1

My Output is as follows:

InvNo Amount

236 5200.26

---

That is, it is not checking the condition.But its considering only the biggest decimal place.Please help.

Answer 1

Try using ROUND instead

DECLARE @Table1 TABLE(
        ID INT,
        Currency VARCHAR(10),
        Amount FLOAT
)

INSERT INTO @Table1 SELECT 1, 'Dollar', 5200.26
INSERT INTO @Table1 SELECT 1, 'Pound', 5200.26

Select 
        Case Currency when 'Dollar' then
            CAST(Amount AS decimal(18,1))
        Else
            CAST(Amount AS decimal(18,2))
        End 'Amount',
        Case Currency when 'Dollar' then
            ROUND(Amount,1)
        Else
            ROUND(Amount,2)
        End 'Amount'
From    @Table1 
where   ID=1

Output

Amount                                  Amount
--------------------------------------- ----------------------
5200.30                                 5200.3
5200.26                                 5200.26