Issue with adjusting CAST in a calculation (precision)-CodePudding

I believe it should be an easy fix for someone who is good at casting. I appreciate your help.

My calculation is :

CAST(SUM(p.QTY) / SUM (r.QTY) AS decimal (5,2)) AS 'Forecast Accuracy'

p.QTY - Forecast Demand

r.QTY - Last Week Actual

Example

p.QTY = 2596

r.QTY = 2504

Therefore my calculation is

2596/2504 = 1.04

But I have 1.00 as a result

There is an issue with precision I believe. Does someone know how I can adjust the so that the 4 is not dropped?

CodePudding user response：

I bet p.QTY and r.QTY are both integers.

The trick is an integer divided by another integer is always... still in an integer. You try to account for this when you cast to decimal, but by then it's already too late. At the time of the division operation we were still in integer land, where 2596 divided by 2504 is exactly 1. No more no less. Casting the integer 1 to numeric(5,2) just gets you 1.00.

You can fix it like this:

CAST(SUM(p.QTY) / (1.00 * SUM (r.QTY)) AS decimal (5,2)) AS 'Forecast Accuracy'

This adds an extra multiplication step which forces an implicit cast before the division. Now we have an integer divided by a numeric, and that can give us meaningful results.

See it work here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=dc021521b689bad13af0ca90f203c04b