Sum(30/365) Not Producing Expected Result

Question

Is there a better way to calculate my result:

All I want is the Result of 30/365 = 0.0821917808 so I can use this in a further calculating. When producing this in a script I get zero. The below is one method to produce the result but there must be a better way.

Any suggestions would be very welcome. Thanks all.

CREATE TABLE [dbo].[_DOS_SCORE] (
[SCORE]         [DBO].[EXPCURRENCY] DEFAULT ((0.00)),
[YEAR]          [DBO].[EXPCURRENCY] DEFAULT ((0.00)),
[RESULT]        [DBO].[EXPCURRENCY] DEFAULT ((0.00)),
[LAST_MODIFIED] DATETIME DEFAULT (GETDATE()) )


INSERT INTO [_DOS_SCORE] (SCORE, YEAR)
VALUES
(30,365),
(60,365)

UPDATE [_DOS_SCORE] SET RESULT = SCORE/YEAR

SELECT * FROM [_DOS_SCORE]

Please note creating types like EXPCURRENCY is counter productive in that it hides problems with datatypes and makes code harder to understand (and change). — Mitch Wheat, Oct 02 '18 at 00:38
+1 @MitchWheat Also they are really hard to change once they are being used. https://sqlblog.org/2009/10/14/bad-habits-to-kick-using-alias-types — Aaron Bertrand, Oct 02 '18 at 00:42

score 1 · Accepted Answer · answered Oct 02 '18 at 00:34

use Numeric data type

declare @DOS_SCORE as table (SCORE numeric(18,10) default 0, YEAR numeric(18,10) default 0, RESULT numeric(18,10) default 0,LAST_MODIFIED datetime default getdate())




INSERT INTO @DOS_SCORE (SCORE, YEAR)
VALUES
(30,365),
(60,365)

UPDATE @DOS_SCORE SET RESULT = SCORE/YEAR

SELECT * FROM @DOS_SCORE

result is

SCORE   YEAR    RESULT  LAST_MODIFIED
30.0000000000   365.0000000000  0.0821917808    2018-10-02 10:36:25.943
60.0000000000   365.0000000000  0.1643835616    2018-10-02 10:36:25.943

Thanks for taking the time to assist. This is exactly what I'm looking for. — Mandy, Oct 03 '18 at 03:38

ErikE · Answer 2 · 2018-10-02T00:41:49.567

Step 1: change the data type of your column RESULT from type EXPCURRENCY to an appropriate decimal(P, S) data type . You should carefully choose what P and S are after reviewing Microsoft SQL Server documentation on decimal precision, scale, and length

(Do not use float unless you understand the implications of using an inexact data type. Currency is not a good candidate for any float data type.)

Step 2: you may need to perform the calculation as SELECT 1.0 * SCORE / YEAR. This will convert any int value to decimal first (as the result of int / int is also int, truncated).

Step 3: review your data types. EXPCURRENCY seems an awfully weird data type for columns named SCORE and YEAR.

Agreed, EXPCURRENCY was very weird but I was getting a bit flustered with it. Thanks for your comments, great pointers. — Mandy, Oct 03 '18 at 03:42

score 1 · Answer 3 · answered Oct 02 '18 at 00:37

1

Per Microsoft Docs:

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

One of your arguments has to be a decimal. For example: SELECT 30.0 / 365 returns 0.082191.

If you need to store this value in a field/attribute, go with @ErikE and use the DECIMAL(A,B) data type.

answered Oct 02 '18 at 00:37

Zack

2,220
1
8
12

Thanks Zack - great tip as I did not know that. – Mandy Oct 03 '18 at 03:40

score 0 · Answer 4 · answered Oct 02 '18 at 00:29

0

SQL fiddle with Money instead of ExpCurrency:

http://sqlfiddle.com/#!18/3cf36c/1

 - SCORE    YEAR    RESULT  LAST_MODIFIED
 - 30       365     0.0821  2018-10-02T00:27:25.893Z
 - 60       365     0.1643  2018-10-02T00:27:25.893Z

Without much information about ExpCurrency, its hard to tell.

answered Oct 02 '18 at 00:29

Gauravsa

6,330
2
21
30

2

`money` is a terrible data type and should never be used. In many contexts it hides the fact that it has 4 decimal places after the decimal point... – ErikE Oct 02 '18 at 00:33
which datatype do you suggest for currency in sql server – Gauravsa Oct 02 '18 at 00:34
1

`decimal(x, 2)` or whatever precision and scale you want. Because even using 4 places of scale will work better as you will always be able to see there are 4 decimal places. – ErikE Oct 02 '18 at 00:36
as per https://www.w3schools.com/sql/sql_datatypes.asp, its saying storage of 5-17 bytes for decimal. and for money is a fixed 8 bytes – Gauravsa Oct 02 '18 at 00:38
money divided by scalar value (in this case 365) gives result to four precision. dont see anything wrong in this. – Gauravsa Oct 02 '18 at 00:41
1

Did I say anything about the problem lying in the resulting precision or scale? I said it's a terrible data type to use, and that is 100% true because many developers are not familiar with it. Depending on how you display it, it can truncate the last 2 decimal places at display time. A developer who codes with `money` without this understanding can introduce *serious* bugs in software that is actually working with money, because of the fractional cent values that *he cannot see*. – ErikE Oct 02 '18 at 00:43
1

Just to back up @ErikE comments. https://www.red-gate.com/hub/product-learning/sql-prompt/avoid-use-money-smallmoney-datatypes – Dave Brown Oct 02 '18 at 07:42

Sum(30/365) Not Producing Expected Result

4 Answers4