Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding Ratio between two numbers Expand / Collapse
Author
Message
Posted Wednesday, June 2, 2010 8:11 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item Finding Ratio between two numbers
Post #931786
Posted Wednesday, June 2, 2010 11:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 6, 2012 2:23 AM
Points: 6, Visits: 14
Thank 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.
Post #931847
Posted Wednesday, June 23, 2010 4:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 1:05 AM
Points: 96, Visits: 277
Hi,
You might like to consider using Euclid's Algorithm to find the Highest Common Factor (Greatest Common Divisor)

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

It returns a result faster than the original (even being lazy and using nvarchar(max)), much faster for larger numbers

cheers


Post #941626
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse