CODEWITHSUNDEEP
sqlsql-servercastingdecimal
Quacks101
Quacks101

Reputation: 293

convert integer into decimal

the two columns in a table are both INT, SA.SalesQuantity and SA.RefundQuantity

I want to work out a variance between 2107 and 2016 using case statements as in below:

(SUM(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = 22 
THEN SA.SalesQuantity-SA.RefundQuantity END ) 
/ SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = 22 
THEN SA.SalesQuantity-SA.RefundQuantity END ))-1 
AS 'SaleQTY_Var'

How would I get my result returned as a decimal and not an INT ?

Upvotes: 0

Views: 754

Answers (3)

Fabio
Fabio

Reputation: 32453

And another approach with CAST method for converting one of the operands to decimal type

CAST(
    (SUM(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = 22 
         THEN SA.SalesQuantity-SA.RefundQuantityEND) AS DECIMAL) 
/ 
SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = 22 
    THEN SA.SalesQuantity-SA.RefundQuantity END )) - 1 
AS 'SaleQTY_Var'

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

Int/Int = Int so change either numerator or denominator as Decimal value to get the result with decimal value. I have multiplied the numerator with 1.0 to convert it as decimal 

(SUM(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = 22 
THEN SA.SalesQuantity-SA.RefundQuantity * 1.0 END ) 
/ 
SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = 22 
THEN SA.SalesQuantity-SA.RefundQuantity END ))-1 
AS 'SaleQTY_Var'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Simply the convert the value after the calculation:

CONVERT(DECIMAL(18, 4)
        (SUM(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = 22 
THEN SA.SalesQuantity - SA.RefundQuantity END )*1.0 
/ SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = 22 
THEN SA.SalesQuantity - SA.RefundQuantity END )) - 1 
       ) AS SaleQTY_Var

Upvotes: 0

Related Questions