none
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

Answers

  • 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.

    Thanks for your help

    Wednesday, July 10, 2013 2:29 PM