>>>>What are the types of these columns?
>>>
>>>Thanks I solved it. The types were float and money. Casting to numeric fixes it.
>>
>>Exactly. That was my first thought - that both or one of them is float.
>
>This rounding has been working for a couple of months now, is it that float only causes problems in certain situations?

Every data type in SQL Server has certain problems. But float and money are the types that lead to the most number of rounding errors.

Search MSDN forum for Money vs. Decimal discussions, say,
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f24acf6c-a518-4f0a-a13e-0172e700564c