October 17, 2015 at 10:15 am
My code is rounding my values incorrectly and I'm not sure why. In this example, the numerator is 48 and the denominator is 49 which is .9795 but my SQL is producing 97.0. I would like to result to be 97.9
CONVERT(decimal(4,1), (SUM(CASE Score_CorrectID WHEN 1 THEN 1 ELSE 0 END +
CASE Score_MiniMiranda WHEN 1 THEN 1 ELSE 0 END +
CASE Score_RepAssistance WHEN 1 THEN 1 ELSE 0 END+
CASE Score_Tone WHEN 1 THEN 1 ELSE 0 END +
CASE Score_Consol_Default WHEN 'OK' THEN 1 ELSE 0 END) * 100)
/
SUM(CASE WHEN Score_CorrectID IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_MiniMiranda IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_RepAssistance IS NULL THEN 0 ELSE 1 END+
CASE WHEN Score_Tone IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_Consol_Default IS NULL THEN 0 ELSE 1 END)) AS Avg_Percent_Actions
October 19, 2015 at 1:33 am
Please post the full query along with table definitions and sample data with an expected output.
Seems like some conversion of datatypes, but need the above to clarify.
October 19, 2015 at 7:58 am
ericvanburen (10/17/2015)
My code is rounding my values incorrectly and I'm not sure why. In this example, the numerator is 48 and the denominator is 49 which is .9795 but my SQL is producing 97.0. I would like to result to be 97.9CONVERT(decimal(4,1), (SUM(CASE Score_CorrectID WHEN 1 THEN 1 ELSE 0 END +
CASE Score_MiniMiranda WHEN 1 THEN 1 ELSE 0 END +
CASE Score_RepAssistance WHEN 1 THEN 1 ELSE 0 END+
CASE Score_Tone WHEN 1 THEN 1 ELSE 0 END +
CASE Score_Consol_Default WHEN 'OK' THEN 1 ELSE 0 END) * 100)
/
SUM(CASE WHEN Score_CorrectID IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_MiniMiranda IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_RepAssistance IS NULL THEN 0 ELSE 1 END+
CASE WHEN Score_Tone IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_Consol_Default IS NULL THEN 0 ELSE 1 END)) AS Avg_Percent_Actions
Try changing the *100 to *100.0
October 19, 2015 at 8:19 am
djj (10/19/2015)
ericvanburen (10/17/2015)
My code is rounding my values incorrectly and I'm not sure why. In this example, the numerator is 48 and the denominator is 49 which is .9795 but my SQL is producing 97.0. I would like to result to be 97.9CONVERT(decimal(4,1), (SUM(CASE Score_CorrectID WHEN 1 THEN 1 ELSE 0 END +
CASE Score_MiniMiranda WHEN 1 THEN 1 ELSE 0 END +
CASE Score_RepAssistance WHEN 1 THEN 1 ELSE 0 END+
CASE Score_Tone WHEN 1 THEN 1 ELSE 0 END +
CASE Score_Consol_Default WHEN 'OK' THEN 1 ELSE 0 END) * 100)
/
SUM(CASE WHEN Score_CorrectID IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_MiniMiranda IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_RepAssistance IS NULL THEN 0 ELSE 1 END+
CASE WHEN Score_Tone IS NULL THEN 0 ELSE 1 END +
CASE WHEN Score_Consol_Default IS NULL THEN 0 ELSE 1 END)) AS Avg_Percent_Actions
Try changing the *100 to *100.0
That seems spot on. The problem is integer division which occurs before the conversion to decimal. Integer division won't add any decimals and will discard the remainder (e.g. 999999/1000000=0). Changing any integer value to a decimal or float will implicitly convert all the other values to the same data type and remove the problem.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply