Integer math
BTW don't use money use decimal/numeric
take a look at this

--use decimal
DECLARE @annualisedPremium decimal(15,4)
DECLARE @totalPolicyPremium decimal(15,4)

SELECT @totalPolicyPremium = 2500.0000
SELECT @annualisedPremium = (( @totalPolicyPremium / 365 ) * 365 )


SELECT @totalPolicyPremium, @annualisedPremium
GO

--Or get around integer math by using 365.0 instead of 365
DECLARE @annualisedPremium money
DECLARE @totalPolicyPremium money


SELECT @totalPolicyPremium = 2500.0000
SELECT @annualisedPremium = (( 2500 / 365.0 ) * 365.0 )
SELECT @totalPolicyPremium, @annualisedPremium
GO

Denis the SQL Menace
http://sqlservercode.blogspot.com/

*sigh*

Thats a *normal* behaviour, watch this here:


SELECT @annualisedPremium = (( @totalPolicyPremium / 365 ) * 365.0 )

--------------------- ---------------------
2500.0000 2499.9945


SELECT @annualisedPremium = (( @totalPolicyPremium / 365.0 ) * 365 )


--------------------- ---------------------
2500.0000 2500.0000


HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Could someone explain to me what's going on here? I'm obviously missing
something

DECLARE @annualisedPremium money
DECLARE @totalPolicyPremium money

SELECT @totalPolicyPremium = 2500.0000
SELECT @annualisedPremium = (( @totalPolicyPremium / 365 ) * 365 )

SELECT @totalPolicyPremium, @annualisedPremium
GO

totalPolicyPremium annualisedPremium
--------------------- ---------------------
2500.0000 2499.9945

Shouldn't they both be 2500.0000? Now, compare with this:

DECLARE @annualisedPremium money
DECLARE @totalPolicyPremium money

SELECT @totalPolicyPremium = 2500.0000
SELECT @annualisedPremium = (( 2500 / 365 ) * 365 )

SELECT @totalPolicyPremium as totalPolicyPremium, @annualisedPremium as
annualisedPremium
GO

totalPolicyPremium annualisedPremium
--------------------- ---------------------
2500.0000 2190.0000

2190? Why is it so far off? Is SQLServer viewing the 2500 value as an
integer and something is lost in the type juggling? Now, compare with this:

DECLARE @annualisedPremium money
DECLARE @totalPolicyPremium money

SELECT @totalPolicyPremium = 2500.0000
SELECT @annualisedPremium = (( 2500.0000 / 365 ) * 365 )

SELECT @totalPolicyPremium as totalPolicyPremium, @annualisedPremium as
annualisedPremium
GO

totalPolicyPremium annualisedPremium
--------------------- ---------------------
2500.0000 2500.0000

Now this is just odd. So using the @totalPolicyPremium variable in the
first example throws it off by a few thousands of a penny but using the
literal value in the 3rd example yeilds the correct value? Huh? And what's
up with the calculation being so off in the second example?

I know I must be missing something...

Any help/pointers would be greatly appreciated!

thnx,
Christoph

Money behavior must conform to "Generally Accepted Accounting Standards"
which require 4 decimals.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.

[quoted text, click to view]

[quoted text, click to view]

Hi Christoph,

No problem. Just start by going to Books Online and finding the subject
"Data Type Precedence".

[quoted text, click to view]

For the parenthesed expression ( @totalPolicyPremium / 365 ),
@totalPolicyPremium is money and 365 is int. According to data type
precedence, 365 is cast to money and the result is a money value as
well. The result of the division is truncated to four decimals: 6.8493.

For the multiplication, we (again) have money and int. The int constant
365 is converted to money and the multiplication is done. The result is
6.8493 * 365.0000 = 2499.9945.

[quoted text, click to view]

Here, 2500 and 365 are both integer constants. The result of 2500/365 is
therefor integer as well - 2500/365, truncated to integer, is 6.
Multiply that with 365 to get the result of 2190. An assignment to
@annualisedPremium, this gets cast to money: 2190.0000

[quoted text, click to view]

In this case, SQL Server treats the constant 2500.0000 as numeric(8,4).
According to data type precedence, the integer 365 is cast to numeric as
well (probably numeric(3,0), but I didn't check), and the result of the
division is also numeric - but in this case, it is numeric(12,8). (The
rules for determining length and mantissa of a numeric division result
are also documented in BOL, but I'm too lazy to look them up right now,
so I just did a test to find out).

The result of the division, after truncation to numeric(12,8) is equal
to 6.84931506. Multiplying this with 365 (cast to numeric, of course)
yields 2499.99999690. The assignment to @annualisedPremium forces
conversion to money - the result is rounded to 2500.0000

[quoted text, click to view]

Never use the money datatype in calculations. Best not use the money
datatype at all, but if you must, use CAST to force a more sensible
datatype in each calculation.

Take care to avoid using only integer datatype in division, except if
you really WANT to lose the fractional part.

--

[quoted text, click to view]

Thank you for your (and everyone else who who replied) help. It went
a long way to explain what was going. I appreciate it!

thnx,
Christoph