Get all unused numbers in range

[Jason]

What I am trying to do is get all of the records in a table that are out of
sequence so I know which account numbers I can reuse. I have a range
of account numbers from 50100 to 70100. I need to know which account
numbers are not stored in the table (not currently used) so I can use.

For instance say I have the following data in table:

Account Name
--------- --------
50100 Test1
50105 Test2
50106 Test4
..
..
..
I should see the results:
50101
50102
50103
50104

because 50101-50104 are available account numbers since not currently in
table.

Thanks.

[John Gilson]

"Jason" <NO*********@ms n.com> wrote in message news:52******** **********@nwrd dc01.gnilink.ne t...

What I am trying to do is get all of the records in a table that are out of
sequence so I know which account numbers I can reuse. I have a range
of account numbers from 50100 to 70100. I need to know which account
numbers are not stored in the table (not currently used) so I can use.

For instance say I have the following data in table:

Account Name
--------- --------
50100 Test1
50105 Test2
50106 Test4
.
.
.
I should see the results:
50101
50102
50103
50104

because 50101-50104 are available account numbers since not currently in
table.

Thanks.

Here's a UDF that will fill in all missing integers between a
lower bound and an upper bound, both inclusive. By default,
the lower bound is 50100 and the upper bound is 70100.

CREATE TABLE Accounts
(
account_id INT NOT NULL PRIMARY KEY,
account_name VARCHAR(10) NOT NULL
)

CREATE TABLE Digits
(
d TINYINT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
)

INSERT INTO Digits (d)
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9

CREATE TABLE NonnegativeInte gers
(
n INT NOT NULL PRIMARY KEY CHECK (n >= 0)
)

INSERT INTO NonnegativeInte gers (n)
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d +
1000 * Thousands.d + 10000 * TenThousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
CROSS JOIN
Digits AS TenThousands

CREATE FUNCTION IntervalGaps
(@lower INT = 50100, @upper INT = 70100)
RETURNS @missing TABLE (n INT NOT NULL PRIMARY KEY)
AS
BEGIN
IF @lower > @upper
RETURN
INSERT INTO @missing (n)
SELECT @lower + I.n
FROM (SELECT MIN(account_id)
FROM Accounts) AS A(least_id)
INNER JOIN
NonnegativeInte gers AS I
ON I.n < A.least_id - @lower
INSERT INTO @missing (n)
SELECT A1.account_id + I.n + 1
FROM Accounts AS A1
INNER JOIN
Accounts AS A2
ON A2.account_id > A1.account_id + 1 AND
NOT EXISTS (SELECT *
FROM Accounts
WHERE account_id > A1.account_id AND
account_id < A2.account_id)
INNER JOIN
NonnegativeInte gers AS I
ON I.n < A2.account_id - A1.account_id - 1
INSERT INTO @missing (n)
SELECT A.greatest_id + I.n + 1
FROM (SELECT MAX(account_id)
FROM Accounts) AS A(greatest_id)
INNER JOIN
NonnegativeInte gers AS I
ON I.n < @upper - A.greatest_id
RETURN
END

-- Sample data
INSERT INTO Accounts (account_id, account_name)
SELECT 50102, 'test1'
UNION ALL
SELECT 50105, 'test2'
UNION ALL
SELECT 50106, 'test4'

SELECT n AS account_id
FROM IntervalGaps(DE FAULT, DEFAULT)

account_id
50100
50101
50103
50104
50107
50108
....
70100

Regards,
jag

[rtpth]

SET NOCOUNT ON

declare @lowwer as int
declare @upper as int

declare @cur as int

set @lowwer = 50100
set @upper = 50200

set @cur = @lowwer


IF EXISTS (SELECT * FROM tempdb..sysobje cts WHERE id = object_id(N'[tempdb]..[#missing]'))
Drop table #missing
--temp table to hold list of missing
create table #missing (
Missing int
--,NotMissing int
)

IF EXISTS (SELECT * FROM tempdb..sysobje cts WHERE id = object_id(N'[tempdb]..[#Account]'))
Drop table #Account
-- temp table to hold your range
create table #Account(
Account_number int
)

insert into #Account (Account_number )
select Account from [your table]
where inv_number like '[0-9][0-9][0-9][0-9][0-9][0-9]'
and inv_number between @lowwer and @upper
-- and any other filter you think would be handy

While( @cur between @lowwer and @upper)
Begin --while

if not exists (select Account_number from #Account
where inv_number = @cur)
Begin --if
insert into #missing (Missing) values (@cur)
End --if
-- else
-- Begin --else
-- insert into #missing (NotMissing) values (@cur)
-- End --else

set @cur = @cur + 1

End --while
-- return the results
select * from #missing
--clean up
IF EXISTS (SELECT * FROM tempdb..sysobje cts WHERE id = object_id(N'[tempdb]..[#missing]'))
Drop table #missing

IF EXISTS (SELECT * FROM tempdb..sysobje cts WHERE id = object_id(N'[tempdb]..[#Inv]'))
Drop table #Inv