store procedure for fraction calculation

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 2005 Forums

Transact-SQL (2005)

store procedure for fraction calculation

Author

Topic

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-27 : 07:15:34

one of my coloumn containts data in fractions and i need to calculate total of two fractions from this column, i need an sp for this purpose but don't know how to do....

Sample Table:
---------------

Create Table #tmpFractions (FractionValue varchar(20))

Sample Data:
-----------
FractionValue
187/287775
3/1279

Result
187/287775 + 3/1279 = 862/287775

NOTE: Denominators may be same or different...

thanx in advance

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-27 : 07:43:15

can u explain ur requirement how will u get the op as 862 in numerator

heavymind
Posting Yak Master

115 Posts

Posted - 2009-01-27 : 07:52:11

You should decomposite your column first i.e.

Create Table #tmpFractions (FractionValue varchar(20))
insert into #tmpFractions values ('187/287775')
insert into #tmpFractions values ('3/1279')

select d = LEFT(FractionValue, CHARINDEX('/',FractionValue)-1), c = RIGHT(FractionValue, LEN(FractionValue) - CHARINDEX('/', FractionValue ))
from #tmpFractions

then you can calculate whatever you want

select summ = SUM(convert(real,d))/SUM(convert(real, c))
from (
select d = LEFT(FractionValue, CHARINDEX('/',FractionValue)-1), c = RIGHT(FractionValue, LEN(FractionValue) - CHARINDEX('/', FractionValue ))
from #tmpFractions
) i

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-01-27 : 08:04:39

try this, not sure abt the approach is correct or not

DECLARE @Val1 VARCHAR(100)
DECLARE @Val2 VARCHAR(100)

SELECT @Val1 = '1/4'
SELECT @Val2 = '3/4'

DECLARE @Numer1 INT,
		@DeNom1 INT,
		@Numer2 INT,
		@DeNom2 INT
		
DECLARE @DeNom INT
DECLARE @Numer INT

SELECT	@Numer1 = LEFT(@Val1, CHARINDEX('/', @Val1) - 1),
		@DeNom1 = RIGHT(@Val1, LEN(@Val1) - CHARINDEX('/', @Val1)),
		@Numer2 = LEFT(@Val2, CHARINDEX('/', @Val2) - 1),
		@DeNom2 = RIGHT(@Val2, LEN(@Val2) - CHARINDEX('/', @Val2))
	   
IF ((@DeNom1 % @DeNom2) = 0 OR (@DeNom2 % @DeNom1) = 0)
BEGIN
		SELECT @DeNom = CASE	WHEN @DeNom1 > @DeNom2 THEN @DeNom1
						WHEN @DeNom1 <= @DeNom2 THEN @DeNom2 
						END
END
ELSE
BEGIN
		SELECT @DeNom = @DeNom1 * @DeNom2
END

SELECT  @Numer = (@DeNom * @Numer1)/@DeNom1 + (@DeNom * @Numer2)/@DeNom2

SELECT CONVERT(VARCHAR(100), @Numer) + '/' + CONVERT(VARCHAR(100), @DeNom)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

heavymind
Posting Yak Master

115 Posts

Posted - 2009-01-27 : 08:06:43

Hey PeterNeo!
looks quite familiar :)

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-27 : 08:08:16

http://www.helpwithfractions.com/adding-fractions-different-denominators.html

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-01-27 : 08:10:35

thanx peter it worked