Calculate Incremental Number Series Based On A Variable

Question

• I need to compare a list of numbers selected from a table to a list of calculated numbers where each series is beteen a Start variable and an End variable.
The goal is to get a list of numbers that do not exist in the table - so we can use those numbers.
Is there a way to calculate such a list?

I'm imagining something like this

```declare @StartNmbr char (11)
declare @EndNmbr char (11)
set @StartNmbr = '12000'
set @EndNmbr = '13000'
select
<code to calculate and return all values between @StartNmbr and @EndNmbr> as AvailableNmbrs
where <code to calculate and return all values between @StartNmbr and @EndNmbr>
not in (select MachineID_I from MM010032 where MachineID_I between @StartNmbr and @EndNmbr)```

Tuesday, July 09, 2013 10:05 PM

• Is MachineID_1 a character value or an integer?  If it's an integer, it's easy using a CTE or a materialized numbers table:

```DECLARE
@StartNmbr int = 12000
,@EndNmbr int = 13000;
WITH
t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
SELECT num
FROM t16M
WHERE
num BETWEEN @StartNmbr AND @EndNmbr
AND num  NOT IN (
SELECT MachineID_I
FROM dbo.MM010032
WHERE MachineID_I BETWEEN @StartNmbr AND @EndNmbr
);

```

This will also work if MachineID_1 is char(11) but each MachineID_1 value will need to be converted to integer, which will fail if the column contains non-numeric data.  Also a table scan well be needed even if the column is indexed.

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Tuesday, July 09, 2013 10:31 PM

All replies

• Is MachineID_1 a character value or an integer?  If it's an integer, it's easy using a CTE or a materialized numbers table:

```DECLARE
@StartNmbr int = 12000
,@EndNmbr int = 13000;
WITH
t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
SELECT num
FROM t16M
WHERE
num BETWEEN @StartNmbr AND @EndNmbr
AND num  NOT IN (
SELECT MachineID_I
FROM dbo.MM010032
WHERE MachineID_I BETWEEN @StartNmbr AND @EndNmbr
);

```

This will also work if MachineID_1 is char(11) but each MachineID_1 value will need to be converted to integer, which will fail if the column contains non-numeric data.  Also a table scan well be needed even if the column is indexed.

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Tuesday, July 09, 2013 10:31 PM
• Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed). Temporal data should use ISO-8601 formats (you failed again). Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. Now we have to guess at everything for you and type it.

>> I need to compare a list of numbers selected from a table to a list of calculated numbers where each series is between a Start variable and an End variable. The goal is to get a list of numbers that do not exist in the table - so we can use those numbers. <<

You call them “numbers” then show them as string! Based on a guess  at the spec you failed to post, that this is a tag number (do you know what that is?), it is CHAR(11) and it is all digits.

DECLARE @start_nbr CHAR (11) = '00000012000';
DECLARE @end_nbr CHAR (11) = '00000013000';

WITH Digits(i)
AS
(SELECT i
FROM (VALUES ('0'), ('1'), ('2'), ('3'), ('4'),
('5'), ('6'), ('7'), ('8'), ('9')) AS X(i)
),

Tags(tag_nbr)
AS
(SELECT (D1.i + D2.i + ..+ D11.i) AS tag_nbr
FROM Digits AS D1, Digits AS D2, .., Digits AS D11
WHERE (D1.i + D2.i + ..+ D11.i)
BETWEEN @start_nbr AND @end_nbr)

SELECT X1.*
FROM (SELECT tag_nbr
FROM Tags
EXCEPT
SELECT tag_nbr
FROM Used_tags) AS X1(tag_nbr);

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Tuesday, July 09, 2013 11:51 PM
• Hi Dan

MachineID_I is Char (11) and the table does contain some non-numeric entries, so it looks like this won't work.  Not a big problem - this was a "nice to have" not a "must have".

I will save your code example - I can see other uses for it in situations where it will work.