Same here, Magoo. Absolutely agree. The problem is that most people won't be aware and will write the code in the "extraordinary proof" manner.

I'll also state that they could have made our lives a bit easier here. Especially when it comes to division and multiplication.

--Jeff Moden

________

"Change is inevitable... change for the better is not".

"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

How to post code problems

How to Post Performance Problems

Create a Tally Function (fnTally)

I have this scenario. Start with an integer value e.g. 365000. Over a period of time (usually a year) I will add one record a day with a proportional amount (so in this very basic example I would add one record each day of 1000 for the next 365 days). The total of these records should add up to the initial integer value. So each day I calculate how much of the initial total remains, and how many days are left to allocate it, and add a new record with the correct proportional amount.

Now this example is a gross oversimplification as a number of variables can change (including the initial value and the period), but the golden rule still applies - the sum of the records at the end of the period should equal the initial total. It goes without saying, I rarely have a nice easy integer value like 365,000 to start with!

As you might imagine, there will be all sorts of precision issues here. I have chosen to store my calculated data as float, as I believe this gives me the best chance of all my records adding up to the correct amount (or as near as possible!). Storing with any other type (decimal, money etc.) will necessarily compromise on precision and therefore adding up the records will almost certainly mean I will get a larger difference than if I store my results as floats.

I have read advice earlier in this thread suggesting I not store my data as floats. Given this scenario does that advice still stand? I don't think so but would like to hear any thoughts people have.

Thanks,

Ash

p.s. this is financial data 😛

Hi Ash,

It all depends !

If each individual value is required to be used in a rounded (e.g. to 2 dp for currency) form at any time, then using float is going to be a potential problem.

The way to ensure the total of all individual values always adds up to the original amount is to subtract the total so far from the original amount for the last period, no matter what method you use for interim values.

The problem with float is this:

e.g. 1000 over 3 days = 333.33, 333.33 and 333.34 when stored as decimal to 2dp.

e.g. 1000 over 3 days = 333.3333333333..., 333.333333333..., 333.33333333333 when stored as float.

I know which I would rather see in financial reporting.

MM

`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`

A quotient is essentially a computed value. If the application is working with imprecise quotients, then perhaps it's best (from an accuracy, storage, and performance) to contain dividend and divisor in two simple integer columns.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply