SQL Server Calculation Issue-CodePudding

Please I was trying to get this correct. Am I doing wrong here. I get a percentage based on amount. But when I try to get the amount from percentage it is not correct.

SELECT (1000 / 1271.11) * 100

I get 78.6713900

But when I multiply I get

SELECT 1271.11 * 0.786713900

I get 999.99990542900 it should be 1000

Any clue on this. Thanks

CodePudding user response：

The problem is that dividing by anything.11 requires too many decimal places to be reversed easily (you can see that SELECT 1000.00000000 / 1271.1100000000; gets you a number that's closer, but still no cigar). Much simpler to use CEILING (or ROUND):

SELECT CEILING(1271.11 * 0.786713900);

Result:

CodePudding user response：

If you want to get back 1000 you can use the function ROUND(). The difference is at the 10th place after the point so you'd have to be in a very specialised application for it to make any difference.

<!-- -->
>     declare @var float(38);
>     set @var =(1000.000 / 1271.11);
>     select 1271.11*@var, @var;
>     select round(1271.11*@var,9);
>     select round(1271.11*@var,10);

>     GO
> 
> <pre>
> (No column name) | (No column name)
> ---------------: | ---------------:
> 999.999999999584 |     0.7867139744
> 
> | (No column name) |
> | ---------------: |
> |             1000 |
> 
> | (No column name) |
> | ---------------: |
> |   999.9999999996 |
> </pre>

<!-- -->
>     declare @var decimal(20,10);
>     set @var =(1000.000 / 1271.11);
>     select 1271.11*@var, @var;
>     select round(1271.11*@var,9);
>     select round(1271.11*@var,10);

>     GO
> 
> <pre>
> (No column name) | (No column name)
> ---------------: | ---------------:
> 999.999999999584 |     0.7867139744
> 
> |  (No column name) |
> | ----------------: |
> | 1000.000000000000 |
> 
> | (No column name) |
> | ---------------: |
> | 999.999999999600 |
> </pre>

*db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6e1baa4da39f97b8acbf028cdf452039)*