SQL Clone
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 388
Comments posted to this topic are about the item Format decimal value to fraction using a tally table.
david.kelly-684973
david.kelly-684973
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

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

Nice work.

David
adrian.facio
adrian.facio
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 388
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-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 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 Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 310
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-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 727
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
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71378 Visits: 20009
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 388
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