locked
how can I achieve this dataset? (Numbers table?) RRS feed

  • Question

  • Say I have a table with 3 columns, Min, Increment, Max. And I have one row in this table with these values.

    Min = 1, Increment = 0.5, Max = 10

    What I would like to do is produce a single column dataset whose row values go from 1 (Min) to 10 (Max) in 0.5 (Increment)increments. So I would have a total of 20 rows in this dataset.

    How can I do this? If my Increment value was 1, then I would create a Numbers table that start at 1 and go up to some high number and join to this Numbers table where Number between Min and Max. But this is complicated by Increments that aren't always 1.

    Maybe the solution isn't with a Numbers table.


    Sunday, October 27, 2013 12:30 AM

Answers

  • You can also do this with a Numbers table, for example

    Declare @Test Table(MinValue int, Increment decimal(1,1), MaxValue int);
    
    -- test with MinValue = 1, Increment = 0.5, MaxValue = 10
    Insert @Test(MinValue, Increment, MaxValue) Values(1, 0.5, 10);
    Select MinValue + (Number - 1) * Increment
    From @Test
    Inner Join dbo.Numbers On MinValue + (Number - 1) * Increment Between MinValue And MaxValue;
    
    -- test with MinValue = 6, Increment = 0.3, MaxValue = 13
    Delete From @Test;
    Insert @Test(MinValue, Increment, MaxValue) Values(6, 0.3, 13);
    Select MinValue + (Number - 1) * Increment
    From @Test
    Inner Join dbo.Numbers On MinValue + (Number - 1) * Increment Between MinValue And MaxValue;

    Tom

    • Proposed as answer by Kalman Toth Sunday, October 27, 2013 7:41 AM
    • Marked as answer by Allen Li - MSFT Sunday, November 3, 2013 2:52 PM
    Sunday, October 27, 2013 4:07 AM

All replies

  • create table test (minCol int, incCol decimal(1,1),maxCol int)
    
    insert into test values (1,0.5,10)
    ;with mycte as(
    select v from (values  (1),(2),(3),(4),(5)) d(v)
    )
    
    select incCol*rn as NewCol from test
    cross apply (select row_number() Over(Order By m1.v) rn from mycte m1,mycte m2) d(rn)
    Where incCol*rn>=minCol AND incCol*rn<=maxCol
    
    
    drop table test

    • Edited by Jingyang Li Sunday, October 27, 2013 4:21 AM
    Sunday, October 27, 2013 2:37 AM
  • You can also do this with a Numbers table, for example

    Declare @Test Table(MinValue int, Increment decimal(1,1), MaxValue int);
    
    -- test with MinValue = 1, Increment = 0.5, MaxValue = 10
    Insert @Test(MinValue, Increment, MaxValue) Values(1, 0.5, 10);
    Select MinValue + (Number - 1) * Increment
    From @Test
    Inner Join dbo.Numbers On MinValue + (Number - 1) * Increment Between MinValue And MaxValue;
    
    -- test with MinValue = 6, Increment = 0.3, MaxValue = 13
    Delete From @Test;
    Insert @Test(MinValue, Increment, MaxValue) Values(6, 0.3, 13);
    Select MinValue + (Number - 1) * Increment
    From @Test
    Inner Join dbo.Numbers On MinValue + (Number - 1) * Increment Between MinValue And MaxValue;

    Tom

    • Proposed as answer by Kalman Toth Sunday, October 27, 2013 7:41 AM
    • Marked as answer by Allen Li - MSFT Sunday, November 3, 2013 2:52 PM
    Sunday, October 27, 2013 4:07 AM
  • Looks good, using the Numbers table. Thx.
    Thursday, November 7, 2013 1:43 PM
  • Follow up question....

    Say my increments table can have several ranges. Meaning... (following Tom's example above with some enhacements)

    IF OBJECT_ID('tempdb..#Test') IS NOT NULL
    DROP TABLE #Test
    CREATE TABLE #Test ([Range] int, MinValue int, Increment decimal(3,1), MaxValue int);
    
    Insert #Test([Range], MinValue, Increment, MaxValue) Values(1, 1, 0.5, 10);
    Insert #Test([Range], MinValue, Increment, MaxValue) Values(2, 10, 5, 30);
    Insert #Test([Range], MinValue, Increment, MaxValue) Values(3, 30, 70, 100);
    
    SELECT * FROM #Test
    
    SELECT
    rng.MinValue + ((num.Number -1) * rng.Increment) AS lowerbound,
    rng.MinValue + ((num.Number -1) * rng.Increment) + rng.Increment AS upperbound
    FROM #Test rng
    INNER JOIN Numbers num
    ON rng.MinValue + ((num.Number -1) * rng.Increment) BETWEEN rng.MinValue AND rng.MaxValue
    WHERE rng.MinValue + ((num.Number -1) * rng.Increment) < rng.MaxValue
    So, as you can see the final select gives each increment step for the 3 ranges in my test table. Now, I want to see if I can build a rule on the test table so that my ranges are continous. In other word, if my range 1 ended in 10, then I want the start of range 2 to start at 10, and so on. Can you build some kind of a constraint to enforce this continuity?

    Friday, November 8, 2013 5:09 AM
  • You can create a user defind function to check the range's min and max values and use that UDF in a CHECK constraint. Here is an example:

    CREATE TABLE RangeTest ([Range] int, MinValue int, Increment decimal(3,1), MaxValue int);
    GO
    
    CREATE FUNCTION dbo.fn_CheckMinValue (@MinValue int)
    RETURNS BIT
    AS
    BEGIN
    
    	DECLARE @cnt AS INT;
    	DECLARE @maxval AS INT;
    
    	SELECT @cnt = COUNT(*) FROM RangeTest;
    
    	IF @cnt = 1
    		RETURN 1;
    
    	SELECT @maxval = MAX(MaxValue) FROM RangeTest
    	WHERE MaxValue <= @MinValue;
    
    	IF @maxval = @MinValue
    		RETURN 1;
    
        RETURN 0;
    END
    GO
    
    ALTER TABLE RangeTest
      ADD CONSTRAINT chk_MinValueForRange
        CHECK(dbo.fn_CheckMinValue(MinValue) = 1)
    GO
    
    INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (1, 1, 0.5, 10); -- Success
    -- INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (1, 9, 0.5, 10); -- Failure : MinValue below prevoius max value
    -- INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (1, 11, 0.5, 10); -- Failure : MinValue higher than prevoius max value
    INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (2, 10, 5, 30); -- Success - MinValue equals previous range's max value
    INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (3, 30, 70, 100); -- Success - MinValue equals previous range's max value
    GO
    
    SELECT * FROM RangeTest;
    GO
    
    DROP TABLE RangeTest;
    GO
    
    DROP FUNCTION fn_CheckMinValue;
    GO
    Hope this will help.


    Krishnakumar S

    Friday, November 8, 2013 7:33 AM
  • A disadvantage of using a function and a check constraint to enforce no gaps is that the check constraint does not work if rows are deleted (because check constraints are only checked on inserts and updates and are not checked for deletes).  You can enforce this with a trigger and constraints.

    First you want a unique constraint on MinValue since (I presume) you do not want multiple rows with the same MinValue.  Also you want a check to ensure that MinValue < MaxValue.  And, of course, MinValue, Increment, and MaxValue should not be allowed to be NULL.  Then you need a trigger that checks to ensure that at most one row exists where MinValue does not equal the MaxValue in another row. So, something like

    CREATE TABLE RangeTest ([Range] int Primary Key, MinValue int Unique Not Null, Increment decimal(3,1) Not Null, MaxValue int Not Null);
    GO
    
    ALTER TABLE RangeTest
      ADD CONSTRAINT ckMinAndMax CHECK (MinValue < MaxValue);
    GO
    
    CREATE TRIGGER trRangeTest ON RangeTest FOR insert, update, delete AS
    BEGIN
      DECLARE @NbrMismatchedRows int;
      SET @NbrMismatchedRows = (SELECT COUNT(*) FROM RangeTest r WHERE NOT EXISTS (SELECT * FROM RangeTest r2 WHERE r.MinValue = r2.MaxValue));
      IF @NbrMismatchedRows > 1
      BEGIN
        ROLLBACK;
        RAISERROR ('Gaps are not allowed', 16,1);
      END;
    END
    GO
    INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (1, 1, 5, 10); -- Success - MinValue equals previous range's max value
    GO
    INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (2, 10, 5, 30); -- Success - MinValue equals previous range's max value
    GO
    INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (3, 30, 70, 100); -- Success - MinValue equals previous range's max value
    GO
    INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (4, 30, 70, 100); -- fails - duplicate MinValue
    GO
    INSERT INTO RangeTest ([Range], MinValue, Increment, MaxValue) VALUES (5, 125, 5, 200); -- fails - leaves gap
    GO
    DELETE FROM RangeTest WHERE Range = 2; -- Fails because it leaves gap
    GO
    
    SELECT * FROM RangeTest;
    GO
    
    DROP TABLE RangeTest;
    GO
    

    Tom

    Friday, November 8, 2013 3:30 PM