Search code examples
sqlsql-servercurrency

Managing percentages of money columns

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

CREATE TABLE _ROUNDERROR 
(
    Premium Money,
    Commission Money
)

INSERT INTO _ROUNDERROR (Premium, Commission) 
VALUES (1632.33, 408.08)

INSERT INTO _ROUNDERROR (Premium, Commission) 
VALUES (408.08, 163.23)

When I execute the following

SELECT
    *, 
    (Commission / Premium) AS RAWDIV, 
    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.

Solution

  • 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
    *, 
    (Commission / Premium) AS RAWDIV, 
    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)