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."Jason" <NO-SPAM-xyz@.msn.com> wrote in message news:525Ka.43802$hI1.7823@.nwrddc01.gnilink.net...
> 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 NonnegativeIntegers
(
n INT NOT NULL PRIMARY KEY CHECK (n >= 0)
)
INSERT INTO NonnegativeIntegers (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
NonnegativeIntegers 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
NonnegativeIntegers 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
NonnegativeIntegers 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(DEFAULT, DEFAULT)
account_id
50100
50101
50103
50104
50107
50108
...
70100
Regards,
jag|||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..sysobjects 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..sysobjects 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..sysobjects WHERE id = object_id(N'[tempdb]..[#missing]'))
Drop table #missing
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Inv]'))
Drop table #Inv
