The Number that shouldn't be a number

Question

The Number that shouldn't be a number

Viewing 9 posts - 106 through 113 (of 113 total)

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

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 1

RonKyle - Thursday, December 13, 2018 12:41 PM
Eric M Russell - Thursday, December 13, 2018 12:19 PM
t.franz - Thursday, December 13, 2018 8:46 AM
Worst data types: I've seen money values, that are usually < 100 EUR be stored in DECIMAL(38,17) columns (and many percentage columns with the same declaration too).
Perhaps the data modeler is hedging against monetary hyper inflation.
Why all the decimals then?

Probably to replace "FLOAT" when it comes to calculations such as mortgage interest calculations. One of the keys to such calculations is to never store the results formatted for human consumption. Whoever did this is likely unaware of the truncation to just 6 decimal places under certain conditions, especially the conditions created by multiplication.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 2

Jeff Moden - Thursday, December 13, 2018 1:39 PM
RonKyle - Thursday, December 13, 2018 12:41 PM
Eric M Russell - Thursday, December 13, 2018 12:19 PM
t.franz - Thursday, December 13, 2018 8:46 AM
Worst data types: I've seen money values, that are usually < 100 EUR be stored in DECIMAL(38,17) columns (and many percentage columns with the same declaration too).
Perhaps the data modeler is hedging against monetary hyper inflation.
Why all the decimals then?
Probably to replace "FLOAT" when it comes to calculations such as mortgage interest calculations. One of the keys to such calculations is to never store the results formatted for human consumption. Whoever did this is likely unaware of the truncation to just 6 decimal places under certain conditions, especially the conditions created by multiplication.

I'm not sure I understand the answer. Are you saying that decimals to this place are necessary for interest calculations? For money I've used decimal(19,4) thinking four decimals was more than enough. Is that sound?

skeleton567 SSCertifiable Points: 5671 More actions · Answer 3

RonKyle - Thursday, December 13, 2018 5:31 PM
Jeff Moden - Thursday, December 13, 2018 1:39 PM
RonKyle - Thursday, December 13, 2018 12:41 PM
Eric M Russell - Thursday, December 13, 2018 12:19 PM
t.franz - Thursday, December 13, 2018 8:46 AM
Worst data types: I've seen money values, that are usually < 100 EUR be stored in DECIMAL(38,17) columns (and many percentage columns with the same declaration too).
Perhaps the data modeler is hedging against monetary hyper inflation.
Why all the decimals then?
Probably to replace "FLOAT" when it comes to calculations such as mortgage interest calculations. One of the keys to such calculations is to never store the results formatted for human consumption. Whoever did this is likely unaware of the truncation to just 6 decimal places under certain conditions, especially the conditions created by multiplication.
I'm not sure I understand the answer. Are you saying that decimals to this place are necessary for interest calculations? For money I've used decimal(19,4) thinking four decimals was more than enough. Is that sound?

Agreed. I may not have a real handle on this, but it seems to me as if rounding to cents anyway, even interest calculations don't need a whole lot of decimal places. If I'm not mistaken, once you get to the second place beyond the one cent point, can anything else change the rounding? The only place i seem to use more decimals is when I'm trying to match to an existing number, such as in reconciling my investment transactions where I need to reconcile to the transaction total the broker presents by entering more decimals until the totals match. Probably the only need for more accuracy would be if you are matching to an existing number that already has an accuracy point beyond cents. General rule might be two decimals beyond that point.

Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

skeleton567 - Friday, December 14, 2018 7:23 AM
RonKyle - Thursday, December 13, 2018 5:31 PM
Jeff Moden - Thursday, December 13, 2018 1:39 PM
RonKyle - Thursday, December 13, 2018 12:41 PM
Eric M Russell - Thursday, December 13, 2018 12:19 PM
t.franz - Thursday, December 13, 2018 8:46 AM
Worst data types: I've seen money values, that are usually < 100 EUR be stored in DECIMAL(38,17) columns (and many percentage columns with the same declaration too).
Perhaps the data modeler is hedging against monetary hyper inflation.
Why all the decimals then?
Probably to replace "FLOAT" when it comes to calculations such as mortgage interest calculations. One of the keys to such calculations is to never store the results formatted for human consumption. Whoever did this is likely unaware of the truncation to just 6 decimal places under certain conditions, especially the conditions created by multiplication.
I'm not sure I understand the answer. Are you saying that decimals to this place are necessary for interest calculations? For money I've used decimal(19,4) thinking four decimals was more than enough. Is that sound?
Agreed. I may not have a real handle on this, but it seems to me as if rounding to cents anyway, even interest calculations don't need a whole lot of decimal places. If I'm not mistaken, once you get to the second place beyond the one cent point, can anything else change the rounding? The only place i seem to use more decimals is when I'm trying to match to an existing number, such as in reconciling my investment transactions where I need to reconcile to the transaction total the broker presents by entering more decimals until the totals match. Probably the only need for more accuracy would be if you are matching to an existing number that already has an accuracy point beyond cents. General rule might be two decimals beyond that point.

It usually has to be looked at based on your circumstances. We have had need for more than expected precision in a number of places, more than just for interest calculations.

For example, in insurance you tend to price things "per year": however things get interesting after that since not all polices are annual, change to a policy might happen part of the way through the year, or your accounting only gets to recognize premium over time. So you end up having to "prorate" (i.e. convert the price from "per year" to "per day"), and you still need the numbers to tie in no matter which of the two you use.

Extend that even a little bit further, and you might need to in some cases turn that "per building per year" price into a "per square foot per day" price, on a warehouse size building. You'd better be able to carry a LOT of extra precision or your "rounding errors" quickly climb into 100's of thousands or higher.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Thomas Franz Hall of Fame Points: 3874 More actions · Answer 5

even in this case (breaking down to daily or even hourly costs) you can not invoice more than rounded cents. So more precisition will only prevent rounding errors, if you have to add many mini prices and wants to round only the totoal sum.

On the other hand I worked for a telephony company before, where the telephone calls will be priced at minute level, but even they did not calculate with more than 4 decimal places.

PS: 6-fold quotes when you refer to the answere just above are very good to read and increases the clarity a lot!

God is real, unless declared integer.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Fully agreed on everything (including the ability to only quote the latest post 😛). In this case it was more of a need to identify which values are "final" and which ones might need to be interim and carry more significant digits to support the final answer.

It kind of brings us back to Gail's original point: understand what the data is supposed to be used for and don't just put your blinders on when deciding what data types to use.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 7

....and Slashdot's quote for the day is:

God is real, unless declared integer.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 8

PHYData DBA - Thursday, December 13, 2018 9:07 AM
Eric M Russell - Thursday, December 13, 2018 7:47 AM
The ISO standard for formatting date strings is YYYYMMDD
What ISO standard is that?
ISO 8601 standard states:
"ISO 8601 tackles this uncertainty by setting out an internationally agreed way to represent dates:
YYYY-MM-DD
For example, September 27, 2012 is represented as 2012-09-27."
https://www.iso.org/iso-8601-date-and-time-format.html

No sir. Don't mistake an ad for information as the gospel. That's only a part of what the standard actually says.

Section 4.1.2.2 of the standard clearly states that the YYYY-MM-DD format is an allowable "extended" format and that the "basic" format is actually YYYYMMDD.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John_Hill Ten Centuries Points: 1173 More actions · Answer 9

I'm really surprised that no one mentioned Microsoft doing this (date and/or time as INT) in the msdb database.

SELECT * FROM dbo.sysjobhistory

Complete pain to convert that to a time format to do any sort of reporting on when jobs ran.