In my day job I work for a manufacturer of custom kitchen cabinets.  The entire process is steeped in automation, from complex order entry to robots that deliver material when and where it is needed.  The machines that cut and form the materials operate as accurately as mere thousanths of an inch.   Final dimensions and reports meant for human consumption usually require information in fractions of an inch.  These dimensions are usually in 64ths, 32nds, 16ths, 8ths, 4ths, and halves of an inch.  Dimensions are stored in the sql server database as decimal numbers that can be used for calculations.  I felt that a SQL user defined function (udf) would be helpful in getting the information required for the reports.  I started with Both Google and Bing searches.  Surely someone had done this before.  I did not quickly find any.  I set out to create my own. 

I first looked at what it would take to calculate the values.  I realized that sometimes I wanted the number in 1/8’s and sometimes 1/64’s and so forth.  To help me get my head around the problem I decided to create an excel spreadsheet that listed all decimal values from 0 to 1” in 64th of an inch increments.  At least these would be useful in testing my UDF.  I then started to see the pattern in the data and added columns for 32nds and 16ths.  The solution to some people may seem obvious, but I find it helpful to look at the patterns in the data for some insight into approaches I had not first considered.  I thought that with only 64 rows in the dataset it should be pretty straightforward to just build a lookup table. And that it would play into SQL’s strengths in data selection. So now I needed the columns for numerator and denominator since 4/64ths should show 1/16 not 4/64ths.   Also I sometimes what to round numbers off to ½” for some rough cuts and 1/64th for fine ones.  I created a column for each rounding factor from 1/64 to full integer inches.  I moved this data into SQL by doing a little concatenation in excel of an insert statement for each row and then copied that to query analyzer to input the data.  The table I created was like this

CREATE TABLE [dbo].[FractionLookup](
      [DecValue] [float] NOT NULL,
      [Numerator] [varchar](2) NOT NULL,
      [Denominator] [varchar](2) NOT NULL,
      [Max64] [float] NULL,
      [Max32] [float] NULL,
      [Max16] [float] NULL,
      [Max8] [float] NULL,
      [Max4] [float] NULL,
      [Max2] [float] NULL,
      [Max1] [float] NULL

And the data looked like this (only showing the first few columns and half the rows)

decvalue    numerator denominator Max64       Max32       Max16       Max8
----------  --------- ----------- ---------- ------------ ----------- -----------
0           0         0           0           0           0           0
0.015625    1         64          0.0078125   NULL        NULL        NULL
0.03125     1         32          0.0234375   0.015625    NULL        NULL
0.046875    3         64          0.0390625   NULL        NULL        NULL
0.0625      1         16          0.0546875   0.046875    0.03125     NULL
0.078125    5         64          0.0703125   NULL        NULL        NULL
0.09375     3         32          0.0859375   0.078125    NULL        NULL
0.109375    7         64          0.1015625   NULL        NULL        NULL
0.125       1         8           0.1171875   0.109375    0.09375     0.0625
0.140625    9         64          0.1328125   NULL        NULL        NULL
0.15625     5         32          0.1484375   0.140625    NULL        NULL
0.171875    11        64          0.1640625   NULL        NULL        NULL
0.1875      3         16          0.1796875   0.171875    0.15625     NULL
0.203125    13        64          0.1953125   NULL        NULL        NULL
0.21875     7         32          0.2109375   0.203125    NULL        NULL
0.234375    15        64          0.2265625   NULL        NULL        NULL
0.25        1         4           0.2421875   0.234375    0.21875     0.1875
0.265625    17        64          0.2578125   NULL        NULL        NULL
0.28125     9         32          0.2734375   0.265625    NULL        NULL
0.296875    19        64          0.2890625   NULL        NULL        NULL
0.3125      5         16          0.3046875   0.296875    0.28125     NULL
0.328125    21        64          0.3203125   NULL        NULL        NULL
0.34375     11        32          0.3359375   0.328125    NULL        NULL
0.359375    23        64          0.3515625   NULL        NULL        NULL
0.375       3         8           0.3671875   0.359375    0.34375     0.3125
0.390625    25        64          0.3828125   NULL        NULL        NULL
0.40625     13        32          0.3984375   0.390625    NULL        NULL
0.421875    27        64          0.4140625   NULL        NULL        NULL
0.4375      7         16          0.4296875   0.421875    0.40625     NULL
0.453125    29        64          0.4453125   NULL        NULL        NULL
0.46875     15        32          0.4609375   0.453125    NULL        NULL
0.484375    31        64          0.4765625   NULL        NULL        NULL
0.5         1         2           0.4921875   0.484375    0.46875     0.4375

…

Each of the max columns have values that are the max decimal value allowed to return that particular numerator and denominator pair.  This would achieve my rounding and the null rows will be ignored since there are no decimal values that will return those numerator and denominator pairs for that factor.

Then the UDF was fairly easy.  Basically it is a case statement that chooses which column to retrieve values from.  I had originally thought I could use the function to also accept a unit parameter that would append a unit mark.  Since I got it going it became obvious that was just plain silly since when would I ever want 64ths of a foot.  I left that in the code here to show off my Billnance.

I had to handle the edge condition where the fraction is rounded to 0 or 1” and added that at the bottom.

Now without further blabbering, here is the function:

CREATE FUNCTION [dbo].[ToFraction]
(
      -- parameters
      @Decval float,
      @Units      Varchar(10),
      @denominator tinyint
)
RETURNS varchar(15)
AS
BEGIN
      -- Declare the variables
      DECLARE @FractionString varchar(15)
      DECLARE @IntPart smallint
      Declare @DecPart float
      -- split the dec val into int and remainder
      select @FractionString = ''
      select @IntPart = convert(int,@Decval)
      select @DecPart = @Decval-@IntPart
      SELECT @FractionString =
    Case @denominator -- compute the return value
      when 64 then
                   (select top 1 (numerator + '/' + denominator) as fract
                  from FractionLookup
                  where max64 is not null and  Max64<=@DecPart
                  order by decvalue desc)
      when 32 then
                  (select top 1 (numerator + '/' + denominator) as fract
                  from FractionLookup
                  where max32 is not null and  Max32<=@DecPart
                  order by decvalue desc)
      when 16 then
               (select top 1 (numerator + '/' + denominator) as fract
                  from FractionLookup
                  where max16 is not null and  Max16<=@DecPart
                  order by decvalue desc)
      when 8 then
            (select top 1 (numerator + '/' + denominator) as fract
                  from FractionLookup
                  where max8 is not null and  Max8<=@DecPart
                  order by decvalue desc)
      when 4 then
           (select top 1 (numerator + '/' + denominator) as fract
                  from FractionLookup
                  where max4 is not null and  Max4<=@DecPart
                  order by decvalue desc)
      when 2 then
           (select top 1 (numerator + '/' + denominator) as fract
                  from FractionLookup
                  where max2 is not null and  Max2<=@DecPart
                  order by decvalue desc)
      when 1 then
           (select top 1 (numerator + '/' + denominator) as fract
                  from FractionLookup
                  where max1 is not null and  Max1<=@DecPart
                  order by decvalue desc)
    else
                  cast(@denominator as varchar(15)) + ' - Invalid valueToFraction'
    END
      -- Check to see if we rounded up to a whole unit and add it to the number
      SELECT @FractionString =
      Case @FractionString
            WHEN '1/1' THEN convert(varchar(15),@IntPart+1)
            WHEN '0/0' THEN convert(varchar(15),@IntPart)
            ELSE convert(varchar(15),@IntPart) + '-' + @FractionString
      End
      -- Return the result of the function
      RETURN @FractionString +
            Case @Units
                  WHEN 'inch' THEN '"'
                  else ''
            End
END