# Managing Percentages Of Money Columns

## 10 January 2022 - 1 answer

I have a question about managing percentages of money columns in SQL Server. I have a simplified case like this:

``````CREATE TABLE _ROUNDERROR
(
Commission Money
)

VALUES (1632.33, 408.08)

VALUES (408.08, 163.23)
``````

When I execute the following

``````SELECT
*,
CAST(Commission AS decimal) / CAST(Premium AS decimal) AS DECDIV,
ROUND(CAST(Commission AS decimal) /CAST(Premium AS decimal), 4) AS DECDIV4,
CAST(Commission AS float) / CAST(Premium AS float) AS FLODIV,
ROUND(CAST(Commission AS float) / CAST(Premium AS float), 4) AS FLODIV4
FROM
_ROUNDERROR
``````

These are the results of this query:

``````PREMIUM               COMMISSION            RAWDIV                DECDIV                                  DECDIV4                                 FLODIV                 FLODIV4
--------------------- --------------------- --------------------- --------------------------------------- --------------------------------------- ---------------------- ----------------------
1632,33               408,08                0,2499                0.2500000000000000000                   0.2500000000000000000                   0,249998468446944      0,25
408,08                163,23                0,3999                0.3995098039215686274                   0.3995000000000000000                   0,399995099000196      0,4
``````

As seen above, if I divide the money columns SQL Server is ceiling the result. So, after reading some post about, I've tried to cast as decimal both columns before dividing them, and it works fine for the first pair of values (first row). But decimal is losing precision too (in second row), and I've decided to cast as float (see second row).

OK, it's working now, but it's the best solution? Is there any side-effect? I can't change the type of the fields because is a legacy database.

The reason why `decimal` isn't working for you is because you don't specify any scale and precision and so it defaults to `decimal(18,0)` and instantly loses any fractional part via rounding.

`CAST(1632.63 as decimal)` gives 1633 for example.

You should specify precision and scale suitable for the range that `money` supports.

`decimal(19,4)` would represent `922,337,203,685,477.5807` exactly though you may want to reduce the precision if your money values are never anywhere near those limits.

``````SELECT
*,
CAST(Commission AS decimal(19,4)) / CAST(Premium AS decimal(19,4)) AS DECDIV,
CAST(Commission AS float) / CAST(Premium AS float) AS FLODIV
FROM
_ROUNDERROR
``````

Returns

``````+---------+------------+--------+-----------------------+-------------------+
| Premium | Commission | RAWDIV |        DECDIV         |      FLODIV       |
+---------+------------+--------+-----------------------+-------------------+
| 1632.33 |     408.08 | 0.2499 | 0.2499984684469439390 | 0.249998468446944 |
|  408.08 |     163.23 | 0.3999 | 0.3999950990001960399 | 0.399995099000196 |
+---------+------------+--------+-----------------------+-------------------+
``````

(certainly if you are paying commission of `922,337,203,685,477` I'd likely want a job there dependent on the currency)