Decimal to Fraction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Decimal to Fraction

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-13 : 11:20:35

Is there a way to convert a decimal into a fraction without using a function?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 11:35:17

quote:
Originally posted by sergeant_time

Is there a way to convert a decimal into a fraction without using a function?

How did you convert a decimal into a fraction using a function? You can take the same code and perhaps put it as in-line code?

I must admit, I didn't quite follow what you meant by "converting to fraction". Would it be something like this?

DECLARE @x DECIMAL(18,5); SET @x = 17.32322;
SELECT
	CAST(@x*Power(10,CAST(SQL_VARIANT_PROPERTY(@x,'Scale') AS INT)) AS INT) AS Numerator,
	POWER(10,CAST(SQL_VARIANT_PROPERTY(@x,'Scale') AS INT)) AS Denominator

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 11:49:00

thats the only way you can represent fraction in t-sql. otherwise it gets stored only in decimal format in any of available datatypes (decimal,numeric,real,float etc)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-13 : 12:40:26

@ sunitabeck

I have report that I would like to display ratios. The intent is to show my ratio in a fraction format. OR find the lowest common denominator (LCD). The main challenge I have is that dont have permissions to create functions and tables.

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-13 : 13:00:26

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 14:15:30

quote:
Originally posted by sergeant_time

@ sunitabeck

I have report that I would like to display ratios. The intent is to show my ratio in a fraction format. OR find the lowest common denominator (LCD). The main challenge I have is that dont have permissions to create functions and tables.

can you show how report output would be like?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-13 : 14:45:26

@ visaKh16

The report would have a line chart with percent of calls handled and the ratio of assist calls to regular calls. In addition to the line chart, the report would also say " For every 1,000 Regular Calls 500 were Assist Calls.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 14:46:42

for this why should you need fraction? why cant it be in decimals? you can always format the values onto %s

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-13 : 14:52:57

visakh16,

ex: 7000 assist calls; 115427 regualr calls = 7000/115427, how would I bring this to the lowest form?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 14:58:42

when you're making it decimal there's no need of further reducing to lowest form. just represent it as decimal and convert it to %

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-08-13 : 16:52:45

quote:
Originally posted by sergeant_time

visakh16,

ex: 7000 assist calls; 115427 regualr calls = 7000/115427, how would I bring this to the lowest form?

What exactly would be the point of that?

By the way, 7000/115427 is already in the lowest form, since they have no common factors, except for 1.

CODO ERGO SUM

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-14 : 08:50:08

@ Michael
The point of the report is to show the customer/stakeholder the call ratio of assist calls to regular calls. Assist Calls are exculated calls. 7000/115427 was just a example mumber. The ratio changes every month.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-14 : 10:01:46

quote:
The ratio changes every month

Expressing a ratio as a fraction could make changes difficult to detect:

7000/115427, 7000/151427, 7000/511427
vs.
0.0136871, 0.0606443, 0.0462268
vs.
1.36%, 6.06%, 4.62%

Just to make it interesting, I mixed up the order of the 2nd line, can you tell at a glance which fraction relates to which decimal or percentage?

And if you're measuring calls, it makes no sense to reduce the fraction, just show the actual numbers. 7000/115427 and 70000/1154271 will have the same ratio but represent a 10x difference in call volume.

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-14 : 13:19:04

I agree this would be a simple method. What about converting a decimal into a fraction. For example, I know .25 = 1/4. I was able to find a functions to convert decimals to fractions, but I dont have the adminstrative right to create functions. So I was hoping to find that I could use.

quote:
Originally posted by robvolk

quote:
The ratio changes every month
Expressing a ratio as a fraction could make changes difficult to detect:

7000/115427, 7000/151427, 7000/511427
vs.
0.0136871, 0.0606443, 0.0462268
vs.
1.36%, 6.06%, 4.62%

Just to make it interesting, I mixed up the order of the 2nd line, can you tell at a glance which fraction relates to which decimal or percentage?

And if you're measuring calls, it makes no sense to reduce the fraction, just show the actual numbers. 7000/115427 and 70000/1154271 will have the same ratio but represent a 10x difference in call volume.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-14 : 13:27:25

where is this report being done? Crystal Reports? Just pass the raw data to your reporting tool and convert it there.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-08-14 : 14:15:21

SSRS 2008

quote:
Originally posted by DonAtWork

where is this report being done? Crystal Reports? Just pass the raw data to your reporting tool and convert it there.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-15 : 08:57:56

doesn't reporting services have the ability to change the display value? should be much simpler to do it there.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources