Prev: Tracking of changes
Next: Compare Data
From: Phil on 23 Jun 2010 04:37
Hi,

Just wondering why I am having such problems rounding figures, when I round
the same figure on an Excel Sheet, I end up with a different end result to
what appears in sql.

The Excel Looks Like This.

Net Vat Total Allocated Result
(=(NET+VAT)/Allocated*Total)
900.00 157.50 1721.38 1721.38 1057.50
150.00 26.25 1721.38 1721.38 176.25

The Result field is a calculated field and is rounded to 2 decimal places,
the calculation is in brackets next to the title name.

The Table in SQL has the same structure but the net, vat, total, allocated
fields are all money fields, the calculation being done in sql is the same,

(NET+VAT)/Allocated*Total

Net Vat Total Allocated Result
900.00 157.50 1721.38 1721.38 1057.44
150.00 26.25 1721.38 1721.38 176.10

I need the results to be the same as the Excel results, is this something to
do with the field type that the initial sql values are coming from?

Any help would be great.

Thanks
From: Iain Sharp on 23 Jun 2010 09:28
On Wed, 23 Jun 2010 01:37:57 -0700, Phil
<Phil(a)discussions.microsoft.com> wrote:

>Hi,
>
>Just wondering why I am having such problems rounding figures, when I round
>the same figure on an Excel Sheet, I end up with a different end result to
>what appears in sql.
>
>The Excel Looks Like This.
>
>Net Vat Total Allocated Result
>(=(NET+VAT)/Allocated*Total)
>900.00 157.50 1721.38 1721.38 1057.50
>150.00 26.25 1721.38 1721.38 176.25
>
>The Result field is a calculated field and is rounded to 2 decimal places,
>the calculation is in brackets next to the title name.
>
>The Table in SQL has the same structure but the net, vat, total, allocated
>fields are all money fields, the calculation being done in sql is the same,
>
>(NET+VAT)/Allocated*Total
>
>Net Vat Total Allocated Result
>900.00 157.50 1721.38 1721.38 1057.44
>150.00 26.25 1721.38 1721.38 176.10
>
>I need the results to be the same as the Excel results, is this something to
>do with the field type that the initial sql values are coming from?
>
>Any help would be great.
>
>Thanks

This is because every stage of calculations involving money will be
rounded to 4dp.

1. Cast each bit as decimal 32,10 (cast(net+vat as decimal(32,10))/
cast(Allocated as decimal(32,10))*cast(total as decimal(32,10))

or

2. Try ((NET+VAT)*Total)/Allocated

Iain
From: --CELKO-- on 23 Jun 2010 10:43
In additionto being proprietary and violating the rule about
formatting in the database, MONEY has all kinds of problems with
multipication and division. Never use it. DECIMAL (s,p) is the better
choice.
From: --CELKO-- on 23 Jun 2010 10:47
On Jun 23, 9:43 am, --CELKO-- <jcelko...(a)earthlink.net> wrote:
> In additionto being proprietary and violating the rule about
> formatting in the database, MONEY has all kinds of problems with
> multipication and division. Never use it. DECIMAL (s,p) is the better
> choice.

Almost forgot! Since you have VAT in your example, have you chekced
out the rules for triangularization, rounded and all that stuff for EU
computations? I tyhink it is still 5 decimal places ..
From: Phil on 24 Jun 2010 04:13
Hi Both,

Thanks for the replies, I managed to fix the problem and convert the values
to Decimal, did spend quite some time trying to get back on the site to
update my question but it seemed to be having a few issues.

Though all the replies were very much appreciated, the joys of working with
other people's table structures.

Thanks Phil
"--CELKO--" wrote:

> In additionto being proprietary and violating the rule about
> formatting in the database, MONEY has all kinds of problems with
> multipication and division. Never use it. DECIMAL (s,p) is the better
> choice.
> .
>
 | 
Pages: 1
Prev: Tracking of changes
Next: Compare Data