SQL Server 2008 - Query to get result in fraction format

Question

I have a table which contains data like this:

MinFormat(int)  MaxFormat(int)  Precision(nvarchar)
   -2              3             1/2

The values in precision can be 1/2, 1/4, 1/8, 1/16, 1/32, 1/64 only.

Now I want result from query as -

 -2
 -3/2 
 -1 
 -1/2 
  0 
  1/2 
  1 
  3/2  
  2 
  5/2 
  3

Any query to get the result as follows?

Idea is to create result based onMinimum boundary (MinFomrat col value which is integer) to Maximum boundary (MaxFormat Col value which is integer) accordingly to the precision value.

Hence, in above example, value should start from -2 and generate the next values based on the precision value (1/2) till it comes to 3

Answer 1

Mine is a bit different others. I perform the addition on fraction and at the end, simplified the fraction.

-- This solution uses CTE
-- it breaks the @min, @max number into fraction
-- perform the addition in terms of fraction
-- at result, it attemp to convert the fraction to simpliest form

declare @min        int,
    @max        int,
    @step       varchar(10),
    @step_n     int,    -- precision step numerator portion
    @step_d     int -- precision step denominator portion

select  @min    = -2,
    @max    = 3,
    @step   = '1/16'

select  @step_n = left(@step, charindex('/', @step) - 1),
    @step_d = stuff(@step, 1, charindex('/', @step), '')

; with rcte as
(
    -- Anchor member
    select  n = @min,   -- numerator
        d = 1,      -- denominator
        v = convert(decimal(10,5), @min)

    union all

    -- Recursive member
    select  n = case when ( (r.n * @step_d) + (r.d * @step_n) ) % @step_d = 0 
             and  (r.d * @step_d) % @step_d = 0
             then ( (r.n * @step_d) + (r.d * @step_n) ) / @step_d
             else (r.n * @step_d) + (r.d * @step_n)
             end, 
        d = case when ( (r.n * @step_d) + (r.d * @step_n) ) % @step_d = 0
             and  (r.d * @step_d) % @step_d = 0
             then (r.d * @step_d) / @step_d
             else (r.d * @step_d)
             end,
        v =  convert(decimal(10,5), ((r.n * @step_d) + (r.d * @step_n)) / (r.d * @step_d * 1.0))
    from    rcte r
    where   r.v < @max
)
select  *,
    fraction    = case  when    n = 0
                then    '0'
                when    coalesce(d2, d) = 1
                then    convert(varchar(10), coalesce(n2, n))
                else    convert(varchar(10), coalesce(n2, n)) + '/' + convert(varchar(10), coalesce(d2, d))
                end
from    rcte r
    cross apply -- use to simplify the fraction result
    (
        select  n2 = case when n % 32 = 0 and d % 32 = 0 then n / 32
                  when n % 16 = 0 and d % 16 = 0 then n / 16
                  when n % 8 = 0 and d % 8 = 0 then n / 8
                  when n % 4 = 0 and d % 4 = 0 then n / 4
                  when n % 2 = 0 and d % 2 = 0 then n / 2
                  end,
            d2 = case when n % 32 = 0 and d % 32 = 0 then d / 32
                  when n % 16 = 0 and d % 16 = 0 then d / 16
                  when n % 8 = 0 and d % 8 = 0 then d / 8
                  when n % 4 = 0 and d % 4 = 0 then d / 4
                  when n % 2 = 0 and d % 2 = 0 then d / 2
                  end
    ) s
order by v
option (MAXRECURSION 0)

Answer 2

Sorry, now I'm late, but this was my approach:

I'd wrap this in a TVF actually and call it like

SELECT * FROM dbo.FractalStepper(-2,1,'1/4');

or join it with your actual table like

SELECT *
FROM SomeTable
CROSS APPLY dbo.MyFractalSteller(MinFormat,MaxFormat,[Precision]) AS Steps

But anyway, this was the code:

DECLARE @tbl TABLE (ID INT, MinFormat INT,MaxFormat INT,Precision NVARCHAR(100));

--Inserting two examples
INSERT INTO @tbl VALUES(1,-2,3,'1/2')
                      ,(2,-4,-1,'1/4');

--Test with example 1, just set it to 2 if you want to try the other example
DECLARE @ID INT=1;

--If you want to get your steps numbered, just de-comment the tree occurencies of "Step"
WITH RecursiveCTE as
(
    SELECT  CAST(tbl.MinFormat AS FLOAT) AS RunningValue
           ,CAST(tbl.MaxFormat AS FLOAT) AS MaxF
           ,1/CAST(SUBSTRING(LTRIM(RTRIM(tbl.Precision)),3,10) AS FLOAT) AS Prec
           --,1 AS Step
    FROM @tbl AS tbl
    WHERE tbl.ID=@ID

    UNION ALL

    SELECT RunningValue + Prec
          ,MaxF 
          ,Prec
          --,Step + 1
    FROM RecursiveCTE
    WHERE RunningValue + Prec <= MaxF
)
SELECT RunningValue --,Step 
      ,CASE WHEN CAST(RunningValue AS INT)<>RunningValue 
            THEN CAST(RunningValue / Prec AS VARCHAR(10)) + '/' + CAST(CAST(1/Prec AS INT) AS VARCHAR(MAX))
            ELSE CAST(RunningValue AS VARCHAR(10))
       END AS RunningValueFractal
FROM RecursiveCTE;

The result

Value   ValueFractal
-2      -2
-1,5    -3/2
-1      -1
-0,5    -1/2
0        0
0,5      1/2
1        1
1,5      3/2
2        2
2,5      5/2
3        3

Answer 3

Note this will only work for Precision 1/1, 1/2, 1/4, 1/8, 1/16, 1/32 and 1/64

DECLARE @t table(MinFormat int, MaxFormat int, Precision varchar(4))
INSERT @t values(-2, 3, '1/2')

DECLARE @numerator INT, @denominator DECIMAL(9,7)
DECLARE @MinFormat INT, @MaxFormat INT

-- put a where clause on this to get the needed row

SELECT @numerator   = 1,
       @denominator = STUFF(Precision, 1, charindex('/', Precision), ''),
       @MinFormat = MinFormat,
       @MaxFormat = MaxFormat
FROM @t


;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
SELECT top(cast((@MaxFormat- @MinFormat)  / (@numerator/@denominator) as int) + 1) 
CASE WHEN val % 1 = 0 THEN cast(cast(val as int) as varchar(10))
     WHEN val*2 % 1 = 0 THEN cast(cast(val*2 as int) as varchar(10)) + '/2'
     WHEN val*4 % 1 = 0 THEN cast(cast(val*4 as int) as varchar(10)) + '/4'
     WHEN val*8 % 1 = 0 THEN  cast(cast(val*8 as int) as varchar(10)) + '/8'
     WHEN val*16 % 1 = 0 THEN cast(cast(val*16 as int) as varchar(10)) + '/16'
     WHEN val*32 % 1 = 0 THEN cast(cast(val*32 as int) as varchar(10)) + '/32'
     WHEN val*64 % 1 = 0 THEN cast(cast(val*64 as int) as varchar(10)) + '/64'
END
FROM tally
CROSS APPLY
(SELECT @MinFormat +(N-1) *(@numerator/@denominator) val) x