# Format decimal value to fraction using a tally table.

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

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

Nice work.

David

• 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.

• 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.

• 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!

• 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.

• 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`

• 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

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