Format decimal value to fraction using a tally table.

Question

Post reply

Format decimal value to fraction using a tally table.

adrian.facio

SSCrazy

Points: 2406
More actions
April 8, 2014 at 9:19 pm

#285545

Comments posted to this topic are about the item Format decimal value to fraction using a tally table.

Viewing 8 posts - 1 through 7 (of 7 total)

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

david.kelly-684973 SSC Enthusiast Points: 148 More actions · Answer 1

What is the structure of the tally table "Numbers" listed in the example?

Nice work.

David

adrian.facio SSCrazy Points: 2406 More actions · Answer 2

Hi David,

This is the structure and the code to populate it:

CREATE TABLE [dbo].[Numbers](Number INT NOT NULL PRIMARY KEY)

INSERT INTO Numbers

SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY c.object_id) FROM sys.columns c CROSS JOIN sys.columns c2

I'm glad you liked my script.

Craig A. Silvis Ten Centuries Points: 1038 More actions · Answer 3

It worked for 10.375 but not for 10.377.

It is because this statement

DecimalPart % (1.0 / Number) = 0.0

is never true

I tried making the tally table 10,000 but it still doesn't work.

Brian J. Parker SSC Eights! Points: 879 More actions · Answer 4

I had the same problem. Besides a large enough tally table, I think you need to change "Number BETWEEN 2 AND 200" to "Number BETWEEN 2 AND 1000" for three-digit precision decimals.

For this code, I think you need to adjust the precision you are allowing in the @Value and the Number range used in the tally table to match; otherwise some of these more precise fractions are going to fall through the cracks.

Really great idea, though... a little more time in the oven, a little more testing with high-precision values, and it'll be very cool!

Dave Vroman SSC Eights! Points: 821 More actions · Answer 5

It appears that this calculation can't handle repeating decimals (standard rational numbers like .3333 (1/3)). If this is true it will have problems with the algebraic numbers like SQRT(11) et.al. and any of the other irrational numbers.

Nice idea.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

This is simply a brute force division, could be done this way

😎

DECLARE @DECIM DECIMAL(18,5) = 125521.12345;

/* Brute force division */

;WITH NX(N) AS (SELECT N FROM (VALUES

(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))

,NUMBERS(N) AS

(

SELECT CAST(ROW_NUMBER() OVER

(ORDER BY (SELECT NULL)) AS DECIMAL(18,5)) AS N

FROM NX N1,NX N2,NX N3,NX N4,NX N5,NX N6,NX N7

)

SELECT TOP 1

CAST(CAST((@DECIM * NM.N)

+ ((@DECIM % 1) / (1/NM.N)) AS BIGINT) AS VARCHAR(25))

+ '/' + CAST(CAST(NM.N AS INT) AS VARCHAR(25))

FROM NUMBERS NM

WHERE @DECIM % (CAST(1 AS DECIMAL(18,5))/NM.N) = 0;

Results

-----------------

2510424938/20000

adrian.facio SSCrazy Points: 2406 More actions · Answer 7

Hi guys,

Thanks for the feedback.

You are right, the precision depends on the size of the tally table and the filter "Number BETWEEN 2 AND X". Increasing X and the tally size will allow more precise values to be calculated.

As it was correctly pointed out, infinitely repeating decimals can't be handled with my function, it would be nice to have and idea how to accomplish that, at the moment i don't have any.

I liked the brute forced solution posted.

Thanks again for commenting