Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Format decimal value to fraction using a tally table.


Format decimal value to fraction using a tally table.

Author
Message
adrian.facio
adrian.facio
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 386
Comments posted to this topic are about the item Format decimal value to fraction using a tally table.
david.kelly-684973
david.kelly-684973
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 330
What is the structure of the tally table "Numbers" listed in the example?

Nice work.

David
adrian.facio
adrian.facio
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 386
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
Craig A. Silvis
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 170
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
Brian J. Parker
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 294
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
Dave Vroman
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 721
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
Eirikur Eiriksson
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8062 Visits: 18163
This is simply a brute force division, could be done this way
Cool
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
adrian.facio
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 386
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search