SQLServerCentral » Article Discussions » Article Discussions by Author » Discuss content posted by SRIKANTH & SUBIN » Finding Ratio between two numbersInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralMon, 22 Jan 2018 07:33:25 GMT20Finding Ratio between two numbershttps://www.sqlservercentral.com/Forums/FindPost931786.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/SQL/70395/">Finding Ratio between two numbers</A>[/B]Thu, 19 May 2016 06:55:09 GMTSite OwnersRE: Finding Ratio between two numbershttps://www.sqlservercentral.com/Forums/FindPost1787674.aspxThanks for the script.Thu, 19 May 2016 06:55:09 GMTIwas BornreadyRE: Finding Ratio between two numbershttps://www.sqlservercentral.com/Forums/FindPost941626.aspxHi,
You might like to consider using Euclid's Algorithm to find the Highest Common Factor (Greatest Common Divisor)
[code="sql"]
CREATE FUNCTION CalculateRatio(@numerator int, @denominator int)
RETURNS nvarchar(max)
AS
BEGIN
declare @ratio nvarchar(max)
declare @greatestcommondivisor int
declare @x int
declare @y int
set @x = @numerator
set @y = @denominator
declare @tmp int
if (@denominator > 0)
begin
while (@x > 0)
begin
if @x < @y begin
set @tmp = @x
set @x = @y
set @y = @tmp
end
set @x = @x % @y
end
end
set @greatestcommondivisor = @y
if (@numerator = 0 or @denominator = 0 or @greatestcommondivisor = 1)
begin
set @ratio = convert(nvarchar(max), @numerator) + ':' + convert(nvarchar(max), @denominator)
end
else
begin
set @ratio = convert(nvarchar(max), @numerator/@greatestcommondivisor) + ':' + convert(nvarchar(max), @denominator/@greatestcommondivisor)
end
return @ratio
END
[/code]
It returns a result faster than the original (even being lazy and using nvarchar(max)), much faster for larger numbers
cheers
Wed, 23 Jun 2010 04:06:56 GMTPete CoxRE: Finding Ratio between two numbershttps://www.sqlservercentral.com/Forums/FindPost931847.aspxThank you for your code. It was very helpful.
By the way... I`m looking for a code to view recent T-SQL that was executed. Just like SQL Profiler but I want to incorporate it in my application. Do you know how to do that or where can I find further help ?
Thanks !
Doron.
Wed, 02 Jun 2010 23:29:40 GMTdoronyaary