I've read a few posts on this forum on how to force rounding to decimal places, but nothing is working. This is my latest attempt to try and have the results of a formula display decimals:
( CAST((CUPDAct / CityDelHrsAct) AS DECIMAL(6,2)) / CAST((CUPDSPLY / CityDelHrsSPLY) AS DECIMAL(6,2)) - CAST(1.00 AS DECIMAL(6,2))) * CAST(100.00 AS DECIMAL(6,2)) AS ActPctSPLY
The result is 3.0000
That makes no sense to me. The result I want is 2.73 using this data:
You can acheive this by increasing the decimal digits, and casting either numerator or denominator to decimal, instead of casting the fraction.
select ((CUPDAct/CAST(CityDelHrsAct AS DECIMAL(8,4)))/(CUPDSPLY / CAST(CityDelHrsSPLY AS DECIMAL(8,4))) - 1) * 100
What's the datatypes of the operands?
The easiest solution is to change the percentage calculation to multiply first and then divide:
(100*74.06 / 72.09 - 100)
Or cast the first operand to a FLOAT or NUMBER and finally cast back to a decimal.
Hey guys, thanks for the replies. Both of your suggestions worked, but Dieter's 1st solution was the easist to implement.
I forgot to mention that the attributes of the datatypes used in the calculation are DECIMAL(18,2) NOT NULL.