July 23, 2014 at 4:14 pm
I want to identify rows that go negative but only for 2 cents or more as well as identify rows that 2 or more
I have this expression that does not work how I want it to work:
CASE
WHEN (SUM(FavUnfavCostChange) < (2/100) THEN 'Less'
WHEN SUM(FavUnfavCostChange) > (2/100) THEN 'More'
ELSE NULL
END AS 'Flag'
But I get:
0.00000815000000000000More -- this is not more than 2 cents, is just a positive number
-0.00094700000000000000Less -- this is not less than 2 cents, is just negative number
-0.00222000000000000000Less -- this is not less than 2 cents, is just negative number
-0.00012250000000000000Less -- this is not less than 2 cents, is just negative number
0.00000000000000000000NULL -- this is zero so null is fine
0.01188576000000000000More -- this is not more than 2 cents, is just a positive number[/p]
Can someone please help me out figure out the right CASE expression?
Thank you very much.
July 23, 2014 at 4:41 pm
I have no idea what FavUnfavCostChange is, but would it help to cast it as a specific data type?
CASE
WHEN CAST(SUM(FavUnfavCostChange) AS DECIMAL(10,2)) < 0.02 THEN 'Less'
WHEN CAST(SUM(FavUnfavCostChange) AS DECIMAL(10,2)) > 0.02 THEN 'More'
ELSE NULL
END AS 'Flag'
Also, you seem to have an unclosed parentheses in your first statement.
July 23, 2014 at 4:47 pm
The reason of your problem is that you're dividing integers and that will return an integer when you want a float or decimal value.
Check the following examples:
SELECT 2/100 intDivision,
2/100.0 floatDivision,
2/CAST( 100 AS decimal(10,2)) decimalDivision,
0.02 DirectExpression
July 24, 2014 at 10:03 am
Thank you guys. Both solutions gave me clarity to where I was making a mistake.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply