September 18, 2009 at 5:23 am
I have a table with a "from date", a "to date", and a total interest amount. I need to work out, on a simple interest basis, the proportion of interest applicable to a subset of days within this range. Basically I'm using datediff to get the number of days between the two, then calculating the number of days from my own starting date to the to date and dividing this by the total to get the fraction to multiply by the interest applied.
For example,
From Date = 01 Oct 2008
To Date = 31 Oct 2008
Interest amount = £1000
Interest applied from 15 Oct 2008 to 31 Oct 2008 = ((days between 15 oct 2008 and 31 Oct 2008)/(days between 01 oct 2008 and 31 oct 2008)) * £1000.
My query is, which SQL data type is best to use for these calculations? I will then cast the final value as a decimal(18.2) or a money field.
September 20, 2009 at 6:52 am
Griff, since nobody stepped up, I will ask a couple of questions. I don't think you require floating point operations here, but how do you define best? Are you talking about the datatype to store the source or result data? For money amounts that is usually money, or numeric, to two decimals (obviously). You can cast or convert these on the fly if you want to calculate interest to 3 or more decimal places before you start rounding, but is that your intention?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 21, 2009 at 3:04 am
I would want the most accurate really. My problem is that datediff returns an integer and if I've got a datediff divided by a datediff with the numerator part of the fraction smaller than the denominator I can quite easily get a zero. I multiply this by the daily interest amount and I get zero which is obviously wrong. As a result I have to cast the datediff as a another data type, but when you're dealing with interest amounts these need to be quite precise as otherwise the end figures can vary quite a lot. I've gone with a real data type in my two casts for now. Would you agree with this? I then cast the result of that division being multiplied by the daily rate as a decimal with two decimal places.
September 21, 2009 at 7:57 am
You could try casting the datediff output from integer to a more precise decimal format. That way you don't lose precision early in the calculation and you can even capture fractional pence if necessary.
September 21, 2009 at 8:06 am
OK. I'll try decimal(18,6) I think. Thanks for your help.
September 21, 2009 at 12:45 pm
You want to use DECIMAL with sufficient significant digits (scale) based upon the values being calculated. You did not specify the number of digits of the interest rate percentage. E.g., is it 5%, 5.1%, 5.25%, 5.125%, 5.0625%, etc.
As one who has had to deal with "interesting" rounding problems in the financial services sector, I'd say that 6 decimal places may not be enough. I had a case once where we needed over 20 decimal places!
In this computation, I'd personally use something like DECIMAL(38,23) (will support up to 999 trillion) for any intermediate variable. Then you can round and/or truncate to the final target field.
September 21, 2009 at 3:50 pm
The bigger question is - what do you plan on doing with the results? If you plan on, say, storing the interest earned in a day, and then start adding up days to get to month/day/year totals, then you should NOT use a precise data type like decimal, since it will introduce a bigger error than would something like float.
In general, if you plan on doing ANY calculations based on the results you store, stick with float (and probably the "widest" float you can manage). Why presume you know how many "places" will be needed? Store as many as you are allowed to, cutting down to the appropriate size once you're ready to display the result.
----------------------------------------------------------------------------------
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?
September 22, 2009 at 2:54 am
Very useful responses. Many thanks.
May 28, 2014 at 10:35 am
Wow. Talk about a blast from the past..... 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 28, 2014 at 10:37 am
The Dixie Flatline (5/28/2014)
Wow. Talk about a blast from the past..... 🙂
The thread looks very interesting. Too bad it got hit with payday loan spam.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 28, 2014 at 1:15 pm
Looks like it's been cleaned up already.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply