Testing for a range of numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Testing for a range of numbers

Author

Topic

nunov
Starting Member

17 Posts

Posted - 2010-05-10 : 14:31:14

I have a table, and each row has two values that define a interval of number.

Example:

create table interval(

max integer,
min integer)

What I wanted to test was: for an interval of integer numbers between [a,b], is there any number which doesn't belong to a interval inserted in the table?

For example:

I insert in the table the following intervals: [0,10];[11,19];[21,30]

Now I wanted to know if there was an integer between 0 and 30 that didn't belong to a inserted interval. (Answer: Yes, number 20)

How can I generate a random integer to test this?

NV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 14:34:25

which version are you using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-10 : 15:35:52

if you use 2005/2008


declare @interval table
(max integer,
min integer);
insert @interval(min, max)
select  0,10 union 
select 11,19 union 
select 21,30 

;with cte as 
(select 1 as i 
union all
select i+1 from cte where i<100),
numbs  as
(select row_number() over(order by (select null)) as i from cte c0 cross join cte)

select i from numbs 
where i between (select min(min) from @interval) and (select max(max) from @interval)
except
(
select i from numbs n
join @interval t 
on i between t.min and t.max
)

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-10 : 15:39:36

One way to solve this is by using Numbers table, and you can create one using this:


CREATE TABLE dbo.Numbers
(
   number INT NOT NULL

   CONSTRAINT Numbers_PK
      PRIMARY KEY(number)
);

WITH DigitsCTE AS
(
   SELECT digit
     FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS D(digit)
)
, NumbersCTE AS
(
   SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
     FROM DigitsCTE AS D0
          CROSS JOIN
          DigitsCTE AS D1
          CROSS JOIN
          DigitsCTE AS D2
          CROSS JOIN
          DigitsCTE AS D3
          CROSS JOIN
          DigitsCTE AS D4
          CROSS JOIN
          DigitsCTE AS D5
)
INSERT INTO dbo.Numbers(number)
   SELECT number
     FROM NumbersCTE;

ALTER INDEX Numbers_PK ON dbo.Numbers REBUILD;

Here I created Numbers table and populated it with numbers from 1 to 1000000.
The query then is simple like this:


SELECT N.number
  FROM Numbers AS N
 WHERE N.number < (SELECT MAX(max)
                     FROM interval)
   AND N.number > (SELECT MIN(min)
                     FROM interval)
   AND NOT EXISTS(SELECT *
                    FROM interval
                   WHERE min <= N.number
                     AND max >= N.number);

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-10 : 15:45:15

By the way, I suggest that you get the result in the same way you represent the data itself (intervals).

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-10 : 15:52:29

quote:

Originally posted by malpashaa

One way to solve this is by using Numbers table, and you can create one using this:


CREATE TABLE dbo.Numbers
(
   number INT NOT NULL

   CONSTRAINT Numbers_PK
      PRIMARY KEY(number)
);

WITH DigitsCTE AS
(
   SELECT digit
     FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS D(digit)
)
, NumbersCTE AS
(
   SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
     FROM DigitsCTE AS D0
          CROSS JOIN
          DigitsCTE AS D1
          CROSS JOIN
          DigitsCTE AS D2
          CROSS JOIN
          DigitsCTE AS D3
          CROSS JOIN
          DigitsCTE AS D4
          CROSS JOIN
          DigitsCTE AS D5
)
INSERT INTO dbo.Numbers(number)
   SELECT number
     FROM NumbersCTE;

ALTER INDEX Numbers_PK ON dbo.Numbers REBUILD;

Here I created Numbers table and populated it with numbers from 1 to 1000000.
The query then is simple like this:


SELECT N.number
  FROM Numbers AS N
 WHERE N.number < (SELECT MAX(max)
                     FROM interval)
   AND N.number > (SELECT MIN(min)
                     FROM interval)
   AND NOT EXISTS(SELECT *
                    FROM interval
                   WHERE min <= N.number
                     AND max >= N.number);

You exactly suggest my approch.

I use number table and instead not exist I use except

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-10 : 16:40:49

When I write my solution I did not know that you posted your solution(Hence I did not read it). But after posting my solution I see your solution and noticed the resemblance. But I decided to keep my solution because I think that your solution will not perform will on a large data set (in spit of that the optimizer may say the opposite).

nunov
Starting Member

17 Posts

Posted - 2010-05-10 : 19:17:12

Thank you! Actually I simplified the problem, the table I was talking about (interval) has other atributes besides the interval limits. But i think I can solve my problem with your help!

Thank you! ;)

NV

nunov
Starting Member

17 Posts

Posted - 2010-05-10 : 19:30:29

Wait, I realized I can have negative values to... How can I do it? (Sorry I am new with SQL, I'm still trying to figure out what you've done)

NV

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-11 : 00:26:11

The query still working, but you need to add more values to Numbers table, and here you should define the maximum range of numbers you are covering to fit your numbers in.
For example you can add 100000 negative number plus the the zero to my already created Numbers table using:


INSERT INTO Numbers(number)
   SELECT 0

    UNION ALL

   SELECT -number
     FROM Numbers AS N;

But here you will got Numbers to cover the range -1000000 - 1000000, and this is a big range so if you do not need that big range you can minimize it to fit your needs (and of course to get better performance).

nunov
Starting Member

17 Posts

Posted - 2010-05-11 : 08:07:56

Thank you I think I got it ;)

NV

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources