How to store 1/3rd in the database

Question

How to store 1/3rd in the database

alex.kemp

SSC Rookie

Points: 44
More actions
November 4, 2010 at 12:24 pm

#231214

Can anyone tell me how to store 1/3rd in a table?
I'll give an example.
Say we have a sale of 1 unit. 1/3 of the unit was paid for using credit card, 1/3 was paid using gift card, and the last 1/3 was paid in cash. In my sales table I want to store three records for this transaction, each having .3 repeating in the units column, so that when I aggregate the transaction it is 1 unit, not .999999999 or anything like that.
I know I could use rounding for presentation, but was wondering and would prefer if there is a means of storing infinite decimals such as .3 repeating.
Thanks for any insight.
Rock on.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i would consider leaving the transaction table alone, with one row per transaction, and add a payments table with a FK to the transaction.

that way you can have one to many payments to each transaction, as you might have payments that have other breakdowns in the future.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

You could store the Dividend and Divisor as individual columns. Probably be a bit painful to work with.

Example:

Update MyTable

set

Dividend = 1,

Divisor = 3

where

<condition>

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Use a higher precision numeric/decimal then you're going to display as. For an example:

DECLARE @n1a NUMERIC (25, 8),

@n1b NUMERIC (25, 8),

@n1c NUMERIC (25, 8),

@n2 NUMERIC (20, 4),

@n3 MONEY,

@n4 NUMERIC (18,2)

SELECT @n1a = 0.3333333333333,

@n1b = 0.3333333333333,

@n1c = 0.3333333333333

PRINT @n1a

PRINT @n1b

PRINT @n1c

SET @n2 = @n1a + @n1b + @n1c

SET @n3 = @n1a + @n1b + @n1c

SET @n4 = @n1a + @n1b + @n1c

PRINT '--- Different results'

PRINT @n2

PRINT @n3

PRINT @n4

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

alex.kemp SSC Rookie Points: 44 More actions · Answer 4

alex.kemp

SSC Rookie

Points: 44

November 4, 2010 at 1:58 pm

#1246836

Thank you Mr. Farrell. Its genius.

GSquared SSC Guru Points: 260824 More actions · Answer 5

Don't do anything that involves splitting the unit up. You're going to end up with headaches later from rounding issues. And any sort of inventory control/tracking becomes problematic. Also, it'll throw off BI queries like "how many orders were placed for Widget #3", since those are unlikely to bother checking for fractional orders.

Normalize it. Orders/Purchases table, and Transactions table. Record the actual amount of the transaction in there, plus the source and all that.

You'll regret it later if you do it the other way. Or, if you don't, the person who replaces you will.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon