# how can I achieve this dataset? (Numbers table?) • ### 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

• 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 Sunday, October 27, 2013 7:41 AM
• Marked as answer by 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 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 Sunday, October 27, 2013 7:41 AM
• Marked as answer by 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
• 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
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